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…
Author: Vijay A. Verma
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….
Solution – Challenge 33 – Convert Matrix into Linear Column – II
Below is a possible solution to the challenge – Challenge 33 – Convert Matrix into Linear Column – II. Enter below formula and drag down – =IF(OFFSET($A$1,MOD(ROWS($1:1)-1,COUNTA($A:$A)),ROUNDUP(ROWS($1:1) /COUNTA($A:$A),0)-1)=0,"",OFFSET($A$1,MOD(ROWS($1:1)-1,COUNTA($A:$A)), ROUNDUP(ROWS($1:1)/COUNTA($A:$A),0)-1)) The workbook containing the above solution can be downloaded from Solution – Challenge 33 – Convert Matrix into Linear Column – II.
Article 35 – VBA – Timer Function and Overcoming Midnight Limitation of Timer Function
The basic and classic and most popular mode to time your code is achieved through Timer function which is natively supplied by VBA. It returns a Single representing the number of seconds elapsed since midnight. Hence, your code will look like following when you want to measure the time and it gives the result in…