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…
Author: Vijay A. Verma
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
Solution – Challenge 32 – Convert Matrix into Linear Column – I
Below is a possible solution to Challenge 32 – Convert Matrix into Linear Column – I Enter following formula and drag down – =IF(OFFSET($A$1,ROUNDUP(ROWS($1:1)/COLUMNS($A$1:$D$100),0)-1, MOD(ROWS($1:1)-1,COLUMNS($A$1:$D$100)))=0,"",OFFSET($A$1,ROUNDUP(ROWS($1:1) /COLUMNS($A$1:$D$100),0)-1,MOD(ROWS($1:1)-1,COLUMNS($A$1:$D$100))))
Article 34 – Show Date for a Coming or Previous Day (Say Coming Monday, Previous Friday)
I had already written about one formula where I dealt with coming day formula – Article 3 – Show Date for a Coming Day (e.g. Coming Saturday) I wanted to revisit this with following things in mind – 1. I wanted to explore various formulas on the above topic. Here, I am giving 2 additional…
Challenge 34 – Increment All Digits by 7 and Perform Numerological Sum of the Digits
I had posted a relatively simpler problem Challenge 31 – Increment All Digits by 1 which talked about adding 1 to all digits i.e. if your number is 7409, you needed to add 7409+1111 and come up with the answer of 8520. I had also posted Tips & Tricks 119 – Numerology Sum of the Digits…
Tips & Tricks 126 – Press CTRL+A Three Times to Select Entire Worksheet not Two Times
The safest bet to select entire worksheet is through pressing CTRL+A three times not two times or one time, if you are using shortcut (The safest bet is to press the triangle between 1 and A as marked in Red in the given picture. The tip is for CTRL+A shortcut.) This peculiarity of CTRL+A shortcut…
Tips & Tricks 125 – Sometimes Excel sorts Lowercase first and Sometimes Uppercase in the same range
You have been given following data and you sort this data in Excel and you were expecting that lowercase will be sorted before uppercase. But Excel is sorting on the basis of FIFO i.e. First In First Out for the same alphabet. Hence if A is coming before a, it will get sorted as A,…