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…
Challenge 35 – Validate 16 Digits Credit Card Numbers through Mod 10 Algorithm
This time challenge is to come up with a formula (or a VBA function though formula is preferred) to give the result of TRUE or FALSE if a 16 digit credit card number can be validated or not using Mod 10 algorithm. The credit card numbers will be stored as Text as Excel can't contain…
Excel Quiz 30
Tips & Tricks 128 – Used F9 to See Values in the Formula but Values Stick / Formula doesn't gets Restored
We know that great trick that you can select part of the formula and see it values by pressing F9. See the below snip where I have selected part of the formula (see shaded area) and pressed F9 to see its values. Now, if I press enter that part of the formula gets converted to…
Tips & Tricks 127 – Change Default File Extension for Saving
We know that when we save an Excel file, it gets saved as .xlsx file. But sometimes, few users may need to change the default file extension to something else. You can change it in File > Options > Save