Provide a formula to create the triangle shown. This triangle is known as Pascal triangle. The triangle starts with 1 at the top. In this, a cell is a sum of two upper diagonal cells. For example, J6:=10 is sum of I5:=4 and K5:=6. You need not take care of color which has been given just to illustrate the separation
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 35
Challenge Difficulty: ⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Generate Pascal’s Triangle with Power Query
Power Query solution 1 for Generate Pascal’s Triangle, proposed by Matthias Friedmann:
lets you define the number of rows you want to have and will adapt accordingly:
let
rows = ( 10 /* adjust intended number of rows here */ ),
length = rows*2-1,
repeat = (length-1)/2,
initial = List.Repeat({0}, repeat)&{1}&List.Repeat({0}, repeat),
List =
List.Generate(
()=> [list = initial, i = 1],
each [i]<= rows,
each [
list = List.Transform(List.Positions([list]),
(i) =>({0} & List.RemoveLastN([list],1)){i} + (List.RemoveFirstN([list],1) & {0}){i}),
i = [i]+1
],
each [list]),
Table = Table.FromRows(List),
#"Replaced 0" = Table.ReplaceValue(Table,0,null,Replacer.ReplaceValue,Table.ColumnNames(Table))
in
#"Replaced 0"
Solving the challenge of Generate Pascal’s Triangle with Excel
Excel solution 1 for Generate Pascal’s Triangle, proposed by John V.:
=LET(n,
10,
s,
SEQUENCE(
,
2*n-1
),
c,
REDUCE(--(s=n),
SEQUENCE(
n-1
),
LAMBDA(
i,
x,
VSTACK(
i,
MMULT(
{1,
1},
IFERROR(
INDEX(
i,
x,
s+{1;-1}
),
)
)
)
)),
IF(
c,
c,
""
))Excel solution 2 for Generate Pascal’s Triangle, proposed by محمد حلمي:
=LET(
n,
10,
s,
SEQUENCE(
,
n
),
e,
s^0-1,
DROP(
REDUCE(
HSTACK(
e,
1,
e
),
s,
LAMBDA(
a,
v,
LET(
i,
TAKE(
a,
-1
),
VSTACK(
a,
IFNA(
HSTACK(
0,
i
)+DROP(
i,
,
1
),
)
)
)
)
),
,
-n
)
)Excel solution 3 for Generate Pascal’s Triangle, proposed by Julian Poeltl:
=LET(H,
10,
MAKEARRAY(H,
H+H-1,
LAMBDA(A,
B,
IF(AND(
MOD(
A+B,
2
),
A>ABS(
B-H
)
),
COMBIN(A-1,
(A+B-H)/2),
""))))Excel solution 4 for Generate Pascal’s Triangle, proposed by Aditya Kumar Darak 🇮🇳:
= LET(
_n,
19,
_mid,
(_n + 1) / 2,
_seq,
HSTACK(
SEQUENCE(
1,
_mid
),
SEQUENCE(
1,
_mid - 1,
_mid - 1,
-1
)
),
_top,
IF(
_mid = _seq,
1,
0
),
_seq2,
SEQUENCE(
1,
_n
),
_ref,
VSTACK(
_seq2 + 1,
_seq2 - 1
),
_calc,
REDUCE(
_top,
SEQUENCE(
_mid - 1
),
LAMBDA(
a,
b,
VSTACK(
a,
BYCOL(
IFERROR(
INDEX(
TAKE(
a,
-1
),
1,
_ref
),
0
),
LAMBDA(
c,
SUM(
c
)
)
)
)
)
),
IF(
_calc,
_calc,
""
))
Just change the 19 and it will change automatically.
For conditional formatting it's easy.
Make a new rule with the following formula by selecting the spilling range and the active cell is the cell with the formula.
= AND(
OFFSET(
formula_cell,
0,
-1
)<>"",
OFFSET(
formula_cell,
0,
1
)<>""
)Excel solution 5 for Generate Pascal’s Triangle, proposed by Timothée BLIOT:
=LET(N, 10,
NextRow, LAMBDA(PreviousRow,
LET(
RowLeft, HSTACK(0, PreviousRow),
RowRight, HSTACK(PreviousRow, 0),
HSTACK(RowLeft + RowRight)
)),
Triangle, LAMBDA(self,r,
IF(r = 1, {1},
LET(
SmallerTriangle, self(self, r-1),
LastRow, INDEX(SmallerTriangle, ROWS(SmallerTriangle), 0),
NewRow, NextRow(LastRow),
VSTACK(SmallerTriangle, NewRow)
))),
Numbers, IFERROR(Triangle(Triangle,N),""),
Padding, MAKEARRAY(N,N, LAMBDA(x,y, IF( y<=N-x," ","") )),
Merger, HSTACK(Padding, BYROW(Numbers, LAMBDA(a, TEXTJOIN( ", ,",1, a ) )) ),
IFERROR(TEXTSPLIT(TEXTJOIN("/",1, BYROW(Merger, LAMBDA(a, TEXTJOIN( ",",1, a ) )) ),",","/",0),""))Excel solution 6 for Generate Pascal’s Triangle, proposed by Bhavya Gupta:
=LET(n,
10,
Pascal,
REDUCE(
1,
SEQUENCE(
n
),
LAMBDA(
x,
y,
IF(
y=1,
x,
LET(
r,
TAKE(
x,
-1
),
VSTACK(
x,
MAP(
SEQUENCE(
,
y
),
LAMBDA(
t,
IF(
OR(
t=1,
t=y
),
1,
SUM(
INDEX(
r,
,
VSTACK(
t,
t-1
)
)
)
)
)
)
)
)
)
)
),
IFNA(INDEX(Pascal,
SEQUENCE(
n
),
MAKEARRAY(n,
n*2-1,
LAMBDA(x,
y,
LET(cr,
y-(n-x+1),
IF(ISEVEN(
cr
)*(cr>=0)*(cr
=(OFFSET(
B1,
,
-1
)<>"")*(OFFSET(
B1,
,
1
)<>"") Excel solution 7 for Generate Pascal’s Triangle, proposed by Charles Roldan:
=LAMBDA(n,
MAKEARRAY(n+1,
2*n+1,
LAMBDA(a,
b,
IF(AND(
MOD(
a+b,
2
),
a>ABS(
b-n-1
)
),
COMBIN(a-1,
(a+b-n)/2-1),
""))))(9)Excel solution 8 for Generate Pascal’s Triangle, proposed by Charles Roldan:
=LAMBDA(φ,
φ(φ))(LAMBDA(φ,
LAMBDA(n,
IF(n,
LET(p,
φ(φ)(n - 1),
r,
TAKE(
p,
-1
),
x,
VSTACK(
HSTACK(
0,
p,
0
),
HSTACK(
{0,
0},
r
) + HSTACK(
r,
{0,
0}
)
),
IFERROR(
REPT(
x,
x > 0
),
""
)),
1))))(9)Excel solution 9 for Generate Pascal’s Triangle, proposed by Charalampos Dimitrakopoulos:
=LAMBDA(
rows,
IF(
SEQUENCE(
rows,
2*rows-1,
1,
1
)<=rows-ABS(
SEQUENCE(
rows,
1,
0
)-ROWS(
SEQUENCE(
1,
rows
)
)
),
IFERROR(
COMBIN(
SEQUENCE(
rows,
1,
0
),
SEQUENCE(
1,
rows,
0
)
),
""
),
""
)
)(10)Excel solution 10 for Generate Pascal’s Triangle, proposed by Charalampos Dimitrakopoulos:
=LAMBDA(n,
MAKEARRAY(n,
2*n-1,
LAMBDA(r,
c,
TEXT(MOD(
r+c,
2
)*IFERROR(COMBIN(r-1,
(r+c-n)/2),
0),
"0;;")
)))(10)