Today, we will explore various methods in Excel to construct Floyd's Triangle.
If you're interested in Floyd's Triangle, I will demonstrate its appearance and how to create it in Excel.
The number of rows in the triangle is determined by the value entered in cell A2.
Sequence starts from 1,
nth row contains n elements of numbers,
Row 1 contains 1 number,
Row 2 contains 2 numbers and so on and each numbers has the increment of 1.
Method-1:
=MAKEARRAY(A2,A2,LAMBDA(r,c,IF(r>=c,r*(r-1)/2+c,"")))
MAKEARRAY is the LAMBDA helper function, which will help us to create sequence of numbers. Arguments of this function are,
=MAKEARRAY(rows,columns,function)
In our case, both rows and columns are based on A2 cell value, which is 7,
So, this function will create 7*7 array of numbers. With the help of custom function, we create customized sequences.
In the function argument, we declare 2 variables in LAMBDA function,
r -> row,
c -> column,
our customized function is,
=IF(r>=c,r*(r-1)/2+c,"")
in first row first column,
r ->1
c ->1
then r is equal to c, so our condition is met, in that case,
r*(r-1)/2+c
i.e. 1*(1-1)/2+1
0+1 is equal to 1.
in first row 2nd column,
r -> 1
c -> 2
r is not greater than or equal to c, so our condition is not met, in that case we ask it to place zero length text string which is "".
in 2nd row 1st column,
r -> 2,
c ->1,
then r is greater than c, so our condition is met,
2*(2-1)/2+1
result is 2.
and so on.
Method-2:
=LET(s,SEQUENCE(A2),t,TOROW(s),REPT(SCAN(,s,SUM)-s+t,s>=t))
here, s is sequence of numbers from 1 to 7 in rows,
s -> {1;2;3;4;5;6;7}
t is sequence of numbers from 1 to 7 in columns,
t -> {1,2,3,4,5,6,7}
SCAN function helps us to scan 's' and generate cumulative sum.
Output of SCAN function will look like below,
={1;3;6;10;15;21;28}
Deduct s from the output of SCAN function,
={1;3;6;10;15;21;28}-{1;2;3;4;5;6;7}
result is,
={0;1;3;6;10;15;21}
if we add 't' with above array, it will create 7 * 7 array as shown below,
in first row first column,
s -> 1 and t -> 1, value in that cell is 1,
s is equal to t, our condition is met. So, it will deliver TRUE which is equivalent to 1.
In that case REPT function will help us to repeat 1 for 1 occurrence.
in first row 2nd column,
s -> 1 and t -> 2, value in that cell is 2,
s is not greater than or equal to t, our condition is not met. So, it will deliver FALSE which is equivalent to 0.
In that case REPT function will help us to repeat 2 for zero occurrence, so it will put zero length text string which is "".
and so on.
Method-3:
=IFNA(DROP(REDUCE("",SEQUENCE(A2),LAMBDA(a,v,VSTACK(a,SEQUENCE(,v,v*(v-1)/2+1)))),1),"")
Here also, we iterate over sequence of numbers from 1 to 7,
in each iteration, since we ignore row number argument in SEQUENCE function we will generate sequence of numbers in columns.
in first iteration,
in SEQUENCE function,
No of column is 1,
start number is 1*(1-1)/2+1, which is 1,
so, this will create 1,
in 2nd iteration,
in SEQUENCE function,
No of columns are 2,
start number is 2*(2-1)/2+1, which is 2,
so, this will create {2,3}
in 3rd iteration,
in SEQUENCE function,
No of columns are 3,
start number is 3*(3-1)/2+1, which is 4,
so, this will create {4,5,6}
REDUCE is the LAMBDA helper function which will return final output of our iteration.
But VSTACK function helps us here to stack the result of each iteration, so that we can get all results of our iteration.
Comments