Tue 16 Aug 2016

Solution - Challenge 47 - Generate Pentagonal Series

By |Tuesday, August 16th, 2016|Categories: Solutions|Tags: , , , , , , , |0 Comments

Below is  a possible solution to the problem - Challenge 47 - Generate Pentagonal Series

Put following formula and drag down -

=ROWS($1:1)*(3*ROWS($1:1)-1)/2

Sat 16 Jul 2016

Challenge 47 - Generate Pentagonal Series

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

Pentagonal number series is following - 1, 5, 12, 22, 35, 51, 70, 92, 117, 145, 176....

You need to write an Excel formula which can be dragged down and generates the above sequence.

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

Mon 13 Jul 2015

Solution - Challenge 7 – Formula for Sum of Square of the Digits in an Alphanumeric String

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

Below is a possible solution to the challenge - Challenge 7 – Formula for Sum of Square of the Digits in an Alphanumeric String

Enter the below formula as Array Formula i.e. not 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.

=SUM(IFERROR(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)^2,0))

Mon 13 Jul 2015

Solution - Challenge 6 – Non-array Formula for Sum of Square of the Digits in a Numeric String

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

Below is a possible solution for the challenge - Challenge 6 – Non-array Formula for Sum of Square of the Digits in a Numeric String

=IFERROR(SUMPRODUCT(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)^2),"")

Sat 10 Jan 2015

Challenge 7 – Formula for Sum of Square of the Digits in an Alphanumeric String

By |Saturday, January 10th, 2015|Categories: Challenges|Tags: , , , |1 Comment

Last challenge was about giving a non-array formula for Sum of Square of the Digits in a Numeric String. Now, the challenge is for an alphanumeric string. This time, there is no restriction that you need to give only non-array formula only. You may give array formula also.

Let's say cell A1 contains an alphanumeric string. The challenge before you is to give me a formula to sum the square of the digits.

As an example if A1=a3c78q90A6, then answer would be

1 = 9+49+64+81+0+36 = 239

Note - You may choose to post the response in comments section.

Sat 13 Dec 2014

Challenge 6 – Non-array Formula for Sum of Square of the Digits in a Numeric String

By |Saturday, December 13th, 2014|Categories: Challenges|Tags: , , , |2 Comments

Let's say cell A1 contains a numeric string. The challenge before you is to give me a non-array formula to sum the square of the digits.

As an example if A1=378906, then answer would be

1 = 9+49+64+81+0+36 = 239

Note - You may choose to post the response in comments section.

Sun 02 Nov 2014

Challenge 2 - Sum of Numbers in the Diagonal of a Square

By |Sunday, November 02nd, 2014|Categories: Challenges|Tags: , , , |2 Comments

Suppose, you have a square of 8x8 like below

1

The challenge is following - You need to come up with a formula that sums up the square diagonally. L1 contains the value which tells us how many elements to consider. L2 has T or B (T - from Top, B - from Bottom) which tells us whether the summing up has to be done A1 downwards or H8 upwards. I have put data validation in L1 and L2 (Hence, you would be able to choose only 1 to 8 in L1 and B or T in L2. You can also choose blanks).

If L1=3 and L2 = B, sum would be 29+82+57 (H8 upwards).
If L1=4 and L2 = T, sum would be 76+90+46+37 (A1 downwards).
If L1= Non Blanks, L2 = Blanks - The direction is from Top. Hence L1=2, L2="", sum would be 76+90
If L1= Blanks, L2 = Blanks or Not Blanks, entire diagonal will be summed up. Hence, sum would 76+90+46+37+87+57+82+29.

Excel for the above exercise - Sum of Squares Challenge

Note - You may choose to post the response in comments section.