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.

Sun 13 Mar 2016

Solution - Challenge 36 – Generate Triangular Numbers

By |Sunday, March 13th, 2016|Categories: Solutions|Tags: , , , , , , |0 Comments

Below is a possible solution to Challenge 36 – Generate Triangular Numbers.

Enter below formula anywhere and drag down -

=IF(ROWS($1:1)=1,1,INDIRECT(ADDRESS(ROW()-1,COLUMN()))+ROWS(($1:1)))

A workbook containing the above solution can be downloaded from Solution - Challenge 36 – Generate Triangular Numbers.

Edit - 23-Aug-16 - A better solution is to use below formula and drag down

=ROWS($1:1)*(ROWS($1:1)+1)/2

Sat 05 Mar 2016

Tips & Tricks 131 - Repeat a Number and Increment and Repeat....

By |Saturday, March 05th, 2016|Categories: Tips and Tricks|Tags: , , , |1 Comment

Suppose, you have been given the task of repeating a number and increment that number and repeat it. For example -

1,1,1,1,2,2,2,2,3,3,3,3.....(Here, we are repeating it 4 times and incrementing and repeating 4 times again and so on)

Then you can use following formula

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

Suppose, you want to start the number with 5 not 1, then you can use below formula -

=ROUNDUP(ROWS($1:1)/4,0)+4

Hence, general structure of the formula is

=ROUNDUP(ROWS($1:1)/X,0)+Y-1

X - Number of times a particular number is repeated
Y - Starting Numbers

Hence, if you want to start with number 7 and you want to repeat it 5 times, then following formula should be used

=ROUNDUP(ROWS($1:1)/5,0)+6

Sat 20 Feb 2016

Tips & Tricks 130 - Generate Sequential Numbers and Repeat them

By |Saturday, February 20th, 2016|Categories: Tips and Tricks|Tags: , , , |0 Comments

Suppose, you have been given the task to generate a sequence of numbers and repeat them. For example -

1,2,3,4,1,2,3,4,1,2,3,4

You can use the below formula and drag down -

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

Replace 4 with with any other number to generate any other sequence. Hence, if you want to generate 1,2,3,4,5,6,7,8,9,10,1,2,3,4,5,6,7,8,9,10,1,2,3,4,5,6,7,8,9,10 then formula becomes -

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

The structure of the formula is

=MOD(ROWS($1:1)-1,X)+Y

X - Number of numbers
Y - Starting Number

Utilizing above formula, you want to generate the sequence 5,6,7,8,9,10,5,6,7,8,9,10,5,6,7,8,9,10, then use below formula (You need 6 numbers and stating number is 5)

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

Sat 13 Feb 2016

Challenge 36 - Generate Triangular Numbers

By |Saturday, February 13th, 2016|Categories: Challenges|Tags: , , , , , , |1 Comment

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, 171, 190, 210, 231, 253, 276, 300, 325, 351, 378, 406

The challenge is to write a formula which can be put down in any cell and which when dragged down will generate the above sequence.

Hence, if I put the formula in say A1, it should generate the above sequence. Same formula if put in C4 and dragged down, should generate the above sequence. Same formula if put in F8 and dragged down, should generate the above sequence. Hence, the formula should not require any tweaking to generate the sequence in different columns. Same formula should work in any cell.

The solution to the above challenge will be published after a month i.e. on 13-Mar-16.

 

Sat 05 Sep 2015

Tips & Tricks 107 - Autofill on a Filtered List

By |Saturday, September 05th, 2015|Categories: Tips and Tricks|Tags: , , , , , , , |0 Comments

Everybody is quite aware about Autofill. There are various ways to Autofill. Refer to following article for Autofill -

Article 7 – Generate a Sequence of Numbers

Now, apply a filter on your range and all the techniques fail. If you drag, all cells are filled with 1 and no other techniques also work. The reason is that Autofill works only on a contiguous range. Once, you apply filter, the range becomes non-contiguous.

Now, here comes the trick -

1. Apply the filter.
2. Let's assume that first row is 3 and you wanted to fill it in column B.
3. Put following formula in B3 and drag down
=COUNTIF($B$1:B2,"<>"&"")

If you don't want to drag down -
3.1 Put the above formula in B3.
3.2 Select all the cells including B3.
3.2 Press F2
3.3 CTRL+Enter

Above steps will fill the filtered list with 1, 2, 3.....

Mon 13 Jul 2015

Solution - Challenge 13 – Generating Digit Product Sequences

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

A proposed solution is listed below for the challenge - Challenge 13 – Generating Digit Product Sequences

Put a starting seed in A1.

Put following formula in A2 and drag down -

=A1+PRODUCT(INDEX(--MID(SUBSTITUTE(A1,0,1),ROW(INDIRECT("1:"&LEN(A1))),1),,))

For a seed value of 1 in A1, it will generate following sequence -

1, 2, 4, 8, 16, 22, 26, 38, 62, 74, 102, 104, 108, 116, 122, 126, 138, 162, 174, 202, 206, 218, 234, 258, 338, 410, 414, 430, 442, 474, 586, 826, 922, 958, 1318, 1342, 1366, ...

 

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.

Sat 11 Jul 2015

Solution - Challenge 1 – Single Formula for Fibonacci Numbers

By |Saturday, July 11th, 2015|Categories: Solutions|Tags: , , , , |0 Comments

This is a possible solution to Challenge 1 – Single Formula for Fibonacci Numbers

There can be many solutions to a given problem in Excel. Below are solutions proposed by me -

A1 and drag down -

=IF(ROW()<3,ROW()-1,SUM(OFFSET($A$1,ROW()-3,0,2)))

=IF(ROW()<3,ROW()-1,SUM(INDIRECT("A"&ROW()-2&":A"&ROW()-1)))

If you belong to that class where you worry about volatality of OFFSET and INDIRECT, use below which uses INDEX and INDEX is a non-volatile function -

=IF(ROW()<3,ROW()-1,SUM(INDEX(A:A,ROW()-2):INDEX(A:A,ROW()-1)))