A good number of countries don't follow calendar year as the financial year. For example, India's financial year start is 1-Apr and finishes on 31-Mar. Hence, currently (20-Feb-16), the financial year is 2015-16 (It is also written as FY16). On 1-Apr-16, it will become 2016-17 (It is also written as FY17). Now if a date…
Solution – Challenge 36 – Generate Triangular Numbers
Below is a possible solution to Challenge 36 – Generate Triangular Numbers. Enter below formula anywhere and drag down – =IF(ROWS($1:1)=1,1,INDIRECT(ADDRESS(ROW()-1,COLUMN()))+ROWS(($1:1))) A workbook containing the above solution can be downloaded from Solution – Challenge 36 – Generate Triangular Numbers. Edit – 23-Aug-16 – A better solution is to use below formula and drag down =ROWS($1:1)*(ROWS($1:1)+1)/2
Article 37 – VBA – Generating Unique (Non-repeating) Random Numbers Efficiently
This article is about how to generate unique or non-repeating random numbers given two limits. Hence, if lower limit is 10 and maximum limit is 100, hence, it should generate random numbers between 10 and 100. The algorithm to ensure uniqueness is following – 1. Given lower limit and upper limit, generate all numbers and…
Challenge 38 – Formula for Top 5
The challenge, this time, is to write a formula to come up with names and marks for Top 5 in descending order. Below is the original table and answer expected. The workbook related to this challenge can be downloaded from Challenge 38 – Find Top 5 The solution to this challenge will be published after…
Excel Quiz 31
Tips & Tricks 131 – Repeat a Number and Increment and Repeat….
Suppose, you have been given the task of repeating a number and increment that number and repeat it. For example – 1,1,1,1,2,2,2,2,3,3,3,3…..(Here, we are repeating it 4 times and incrementing and repeating 4 times again and so on) Then you can use following formula =ROUNDUP(ROWS($1:1)/4,0) Suppose, you want to start the number with 5 not…
Solution – Challenge 35 – Validate 16 Digits Credit Card Numbers through Mod 10 Algorithm
Below is a possible solution to the challenge – Challenge 35 – Validate 16 Digits Credit Card Numbers through Mod 10 Algorithm. Enter the below formula as Array formula =MOD(SUM(IF((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))*MOD(ROW(INDIRECT("1:"&LEN(A1))),2) =0,0,MOD((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))*MOD(ROW(INDIRECT("1:"&LEN(A1))),2)*2-1,9) +1)+(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))*MOD(ROW(INDIRECT("1:"&LEN(A1)))-1,2)),10)=0 Note – Array Formula is not entered by pressing ENTER after entering your formula but by pressing CTRL+SHIFT+ENTER. If you are copying and…
Challenge 37 – Find nth Alphabet from the Bottom
Suppose, you have been given a layout as below. The challenge before you is to write a formula to find nth alphabet from bottom. If no criterion is met, blanks should be returned. C1 hold the value of that nth. For C1 = 5, answer would be k. for C1 = 10, answer would be…
Tips & Tricks 130 – Generate Sequential Numbers and Repeat them
Suppose, you have been given the task to generate a sequence of numbers and repeat them. For example – 1,2,3,4,1,2,3,4,1,2,3,4 You can use the below formula and drag down – =MOD(ROWS($1:1)-1,4)+1 Replace 4 with with any other number to generate any other sequence. Hence, if you want to generate 1,2,3,4,5,6,7,8,9,10,1,2,3,4,5,6,7,8,9,10,1,2,3,4,5,6,7,8,9,10 then formula becomes – =MOD(ROWS($1:1)-1,10)+1…
Downloads 01 – Template 01 – A Different Kind of Pareto Chart Template
Download this Pareto Chart Template from Template 01 – Pareto Chart Template This Pareto Chart Template is different from commonly found Pareto Chart Templates. It not only gives a slider bar to adjust your percentage which is typically 80% to any other percentage but it also gives a vertical bar. The combination of vertical bars…