Tue 16 Jan 2018

Solution - Challenge 67 - Generate a Particular Sequence

By |Tuesday, January 16th, 2018|Categories: Solutions|Tags: , , , , |0 Comments

Below is a possible solution to the problem Challenge 67 - Generate a Particular Sequence -

This consists of 2 logic.

  1. Generate Triangular number. The formula for which is n*(n+1)/2
  2. Generate Column Labels (A, B, AA, ZZ....) from numbers, the formula for which is

=SUBSTITUTE(ADDRESS(1,3,4),1,"") where 3 is for column C. So, we need to vary this 3.

The combined formula would be -

=SUBSTITUTE(ADDRESS(1,(ROW(1:1)-1)*ROW(1:1)/2+1,4),1,"")

Sat 16 Dec 2017

Challenge 67 - Generate a Particular Sequence

By |Saturday, December 16th, 2017|Categories: Challenges|Tags: , , , , , |1 Comment

Study the sequence below and you need to write a formula which when dragged down should generate the below sequence.

For visual representation purpose, the sequence is in 5 columns but you need to generate the sequence in one column only.

You need to generate upto 100 entries.

The solution to this challenge will be published after a month i.e. on 16-Jan-18.

Mon 01 May 2017

Solution - Challenge 61 - Generate Multiplication Table

By |Monday, May 01st, 2017|Categories: Solutions|Tags: , , , , , , , , , |0 Comments

Below is a possible solution to the challenge - Challenge 61 - Generate Multiplication Table

Put following formula and drag right and down -

=ROWS($1:1)*COLUMNS($A:A)

Sat 01 Apr 2017

Challenge 61 - Generate Multiplication Table

By |Saturday, April 01st, 2017|Categories: Challenges|Tags: , , , , , , , , , |3 Comments

This time, I want to set a challenge which is not difficult and useful for your kids.

The challenge is to write a formula which can be dragged right and down to generate a multiplication table.

1

The solution to this challenge will be published after a month i.e. on 1-May-17.

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

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