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…
Solution – Challenge 34 – Increment All Digits by 7 and Perform Numerological Sum of the Digits
Below is a possible solution to the challenge – Challenge 34 – Increment All Digits by 7 and Perform Numerological Sum of the Digits. =SUMPRODUCT((MOD((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)+ 7*ROW(INDIRECT("1:"&LEN(A1)))^0)-1,9)+1)*10^(LEN(A1) -ROW(INDIRECT("1:"&LEN(A1))))) The workbook containing the above solution can be downloaded from Solution – Challenge 34 – Increment All Digits by 7 and Perform Numerological Sum of the Digits.
Article 36 – VBA – User Names in Excel
Many times, we are required to fetch User Names in Excel. Unfortunately, getting User Names in Excel is possible only through VBA. These are very small pieces of VBA codes and even a person not knowing VBA can make use of them by following the instructions here. I am going to discuss the various type…
Challenge 36 – Generate Triangular Numbers
If interested in details about Triangular Numbers, you can refer to following link (though it is not needed and one look at the sequence, you will understand what it is) https://en.wikipedia.org/wiki/Triangular_number It is, basically, the following sequence – 1, 3, 6, 10, 15, 21, 28, 36, 45, 55, 66, 78, 91, 105, 120, 136, 153,…
Tips & Tricks 129 – Generate Non Repeating Random Numbers through Formula
Suppose, you want to generate non-repeating random numbers between 1 to 30, you can use following formula in A2 and drag down =IFERROR(AGGREGATE(14,6,ROW($1:$30)*NOT(COUNTIF($A$1:$A1, ROW($1:$30))), RANDBETWEEN(1,30-ROWS($1:1)+1)),"") Note: $A$1:$A1 is with reference to A2 as you put formula in A2 and dragged down. Suppose, you had put the formula in G4, this should be replaced with $G$3:$G3….