Below is a possible solution to the problem Challenge 67 - Generate a Particular Sequence -
This consists of 2 logic.
=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,"")
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.
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)
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.
The solution to this challenge will be published after a month i.e. on 1-May-17.
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),"")
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)
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.
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
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
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
