Sat 30 Jan 2016

Article 35 - VBA - Timer Function and Overcoming Midnight Limitation of Timer Function

By |Saturday, January 30th, 2016|Categories: Articles, VBA|Tags: , , , , , , |0 Comments

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 Seconds.

How Accurate is Timer Function

(more…)

Sat 30 Jan 2016

Challenge 35 - Validate 16 Digits Credit Card Numbers through Mod 10 Algorithm

By |Saturday, January 30th, 2016|Categories: Challenges|Tags: , , , , , , , , |1 Comment

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 more than 15 significant digits in numeric format.

Below is the way Credit Card Numbers can be verified.

1. Start from the rightmost digit and multiply every odd position digit by 1 starting from rightmost. Hence, 1st, 3rd, 5th....15th digits from rightmost should be multiplied by 1.
2. Start from second rightmost digits and multiply this 2. Multiple every even position digit by 2 starting from 2nd rightmost digit. Hence, 2nd, 4th, 6th....16th digits from rightmost should be multiplied by 2.
3. Perform numerological sum on digits which we got in step 2 if result is > 9.
4. Add the results of step 1 and step 3.
5. This result should be a multiple of 10 if the credit card number is valid.

Look at the below example (Row 1 has credit card number. The numbers are in different cell just to make our understanding correct. The formula / VBA function which you will make will be for all 16 digits in one cell only) -

1

Hence, if cell A1 contains following credit card numbers, your result should be as follows for following sample numbers -

5026209217581350 - TRUE
7006688888881300 - FALSE
4406212008581350 - TRUE
6809008888881300 - TRUE
6839808008881306 - FALSE

The result would be published after one month i.e. on 1-Mar-16.

Sun 24 Jan 2016

Excel Quiz 30

By |Sunday, January 24th, 2016|Categories: Quizzes|Tags: , , , , |0 Comments

This is a quiz having only TRUE/FALSE answers.

Excel Quiz 30

A general quiz on Excel.

Sat 23 Jan 2016

Tips & Tricks 128 - Used F9 to See Values in the Formula but Values Stick / Formula doesn't gets Restored

By |Saturday, January 23rd, 2016|Categories: Tips and Tricks|Tags: , , , , , |0 Comments

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.

1

Now, if I press enter that part of the formula gets converted to values and formula doesn't get restored. (see the below snip)

1

How to Prevent this

1. If you have not pressed Enter, press ESC after using F9 to see its values and formula will get restored.

2. If you have pressed Enter, press CTRL+Z (Undo) and formula will get restored. (You need to press CTRL+Z after pressing enter. If there have been many intermediate steps, you need to press CTRL+Z repeatedly to undo this step. Of course, CTRL+Z has its own limitations in terms of retracing steps)

Sat 23 Jan 2016

Tips & Tricks 127 - Change Default File Extension for Saving

By |Saturday, January 23rd, 2016|Categories: Tips and Tricks|Tags: , , , , , , , , |0 Comments

We know that when we save an Excel file, it gets saved as .xlsx file.

1

But sometimes, few users may need to change the default file extension to something else. You can change it in

File > Options > Save

1

Mon 18 Jan 2016

Solution - Challenge 32 – Convert Matrix into Linear Column – I

By |Monday, January 18th, 2016|Categories: Solutions|Tags: , , , , , |3 Comments

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))))

Sat 16 Jan 2016

Article 34 - Show Date for a Coming or Previous Day (Say Coming Monday, Previous Friday)

By |Saturday, January 16th, 2016|Categories: Articles|Tags: , , , , , , , , , , , , , , , , , |2 Comments

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 formulas. So, I will be talking about a total of 3 formulas.

2. In Article 3, I talked only about Coming Day not the Previous Day. In this article, I will be talking about formulas for Previous Day also.

(more…)

Sat 16 Jan 2016

Challenge 34 - Increment All Digits by 7 and Perform Numerological Sum of the Digits

By |Saturday, January 16th, 2016|Categories: Challenges|Tags: , , , , , , |2 Comments

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 aka Sum the Digits till the result is a single digit. In this, I had explored how to add a number and arrive at a single digit. For example, if you have to add 8 + 7 the answer would be 15. You need to further add up 1 and 5 of 15 and final answer would be 6. And this is numerological sum.

Now, the challenge before you is to combine both the approaches and come up with the answer. Hence, if you have been given a number and you have to add those many 7's and you have to perform Numerological Sum on individual digits.

Example

1. Given number is 356 in cell A1, hence you need to add 777 to it. 356+777 = (3+7) (5+7) (6+7) = (10) (12) (13) = (1+0) (1+2) (1+3) = 134

2. Given number is 4072 in cell A1, hence you need to add 7777 to it. 4072 + 7777 = (4+7) (0+7) (7+7) (2+7) = (11) (7) (14) (9) = (1+1) (7) (1+4) (9) = 2759

Come up with a formula to do this (you can also do it in VBA, if you wish though formula is preferable). It should be able to do it for any number of digits. Excel can store numbers with 15 significant digits.

The solution to this problem will be published after a month i.e. on 16-Feb-16.

Sun 10 Jan 2016

Tips & Tricks 126 - Press CTRL+A Three Times to Select Entire Worksheet not Two Times

By |Sunday, January 10th, 2016|Categories: Tips and Tricks|Tags: , , , , , , , |0 Comments

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 comes into picture when your worksheet contains tables also. Suppose, you have a worksheet like below which has at least one table.

1

1. Select a cell outside any data range, press CTRL+A and it will select entire sheet.

2. Select a cell inside A1:D2, press CTRL+A once and it will select A1:D2 range only. Pressing CTRL+A again will select entire sheet.

3. Select a cell inside header row in the table , press CTRL+A once and it will select entire table. Pressing CTRL+A again will select entire sheet.

4. Select a cell inside the table data range i.e. A6:D7, press CTRL+A once and it will select table data range i.e. A6:D7 only. Press CTRL+A again, now it will select entire table i.e. A5:D7 only not entire worksheet.. Now, press CTRL+A again i.e. 3rd time and now, it will select entire  worksheet.

Sun 10 Jan 2016

Tips & Tricks 125 - Sometimes Excel sorts Lowercase first and Sometimes Uppercase in the same range

By |Sunday, January 10th, 2016|Categories: Tips and Tricks|Tags: , |0 Comments

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, a not as a, A.

1

The reason is that Excel's default sorting is case-insensitive.

To perform the sorting which you need, you will need to make it case-sensitive.

1. Select the range of data which you want to sort.
2. Data tab > Sort

1

3. Options > Check Case Sensitive to give you the result which you want.

1