Tue 22 Nov 2016

Solution - Challenge 54 - Make a Sequence like A_B__C___D____E_____F......

By |Tuesday, November 22nd, 2016|Categories: Solutions|Tags: , , , , , , |0 Comments

Below is a possible solution to the challenge Challenge 54 - Make a Sequence like A B C D E F......

Put below formula as array formula in a cell and drag down

=IFERROR(CHAR(MATCH(ROWS($1:1),(ROW($1:$26)*(ROW($1:$26)+1))/2,0)+64),"")

(more…)

Mon 24 Oct 2016

Solution - Challenge 52 - Generate the Sequence 1,2,2,3,3,3,4,4,4,4,5,5,5,5,5...

By |Monday, October 24th, 2016|Categories: Solutions|Tags: , , , , |0 Comments

Below is a possible solution to the challenge Challenge 52 - Generate the Sequence 1,2,2,3,3,3,4,4,4,4,5,5,5,5,5...

Put following formula and drag down

=FLOOR((1+((1+8*ROWS($1:1)-1)^0.5))/2,1)

Sat 22 Oct 2016

Challenge 54 - Make a Sequence like A_B__C___D____E_____F......

By |Saturday, October 22nd, 2016|Categories: Challenges|Tags: , , , , , , |1 Comment

This time challenge before you is to write a formula which can be dragged down and leaves so many blank cells - 1 before that alphabet's position in English Language. Hence, the sequence will start with A, will leave 1 blank cell, then B, then 2 blank cells, then C, then 3 blank cells, then D, then 4 blank cells, then E, then 5 blank cells, then F....................................................................then 24 blank cells, then Y and finally 25 blank cells and then Z.

(more…)

Sat 24 Sep 2016

Challenge 52 - Generate the Sequence 1,2,2,3,3,3,4,4,4,4,5,5,5,5,5...

By |Saturday, September 24th, 2016|Categories: Challenges|Tags: , , , , |1 Comment

This time challenge before you is to write a formula which generates a sequence where every digit appears that many times as that digit. Hence, 3 will repeat 3 times, 8 will repeat 8 times and so on..Hence, the exact sequence would be 1,2,2,3,3,3,4,4,4,4,5,5,5,5,5,6,6,6,6,6,6,7,7,7,7,7,7,7.....

The solution to above challenge will be published after a month i.e. on 24-Oct-16.

Tue 01 Mar 2016

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

By |Tuesday, March 01st, 2016|Categories: Solutions|Tags: , , , , , , , , |1 Comment

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 pasting this formula, take F2 after pasting and CTRL+SHIFT+ENTER. This will put { } brackets around the formula which you can see in Formula Bar. If you edit again, you will have to do CTRL+SHIFT+ENTER again. Don't put { } manually.

The workbook containing the above solution can be downloaded from Solution - Challenge 35 – Validate 16 Digits Credit Card Numbers through Mod 10 Algorithm.

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.

Mon 13 Jul 2015

Solution - Challenge 11 – Generate a Repeating Number Sequence – II

By |Monday, July 13th, 2015|Categories: Solutions|Tags: , , , , |0 Comments

Below is a proposed solution for challenge - Challenge 11 – Generate a Repeating Number Sequence – II

Put following formula and drag down -

=ROUNDUP(ROWS($1:1)/3,0)

This will repeat the sequence 111222333444555...........

Mon 13 Jul 2015

Solution - Challenge 10 – Generate a Repeating Number Sequence – I

By |Monday, July 13th, 2015|Categories: Solutions|Tags: , , , , |1 Comment

Below is the proposed solution for challenge - Challenge 10 – Generate a Repeating Number Sequence – I

Put following in a cell and drag down -

=MOD(ROWS($1:1)-1,5)+1

This will generate a sequence 1, 2, 3, 4, 5 and will repeat this.