A Pascal Triangle starts with 1 at the top and each element is sum of two upper diagonal cells. You need to find the column sums for given number of rows of Pascal’s Triangle. An example with 7 rows of Pascal Triangle is given. Yellow row is an example of column sums for a 7 rows Pascal Triangle.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 509
Challenge Difficulty: ⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Sum Pascal Triangle Columns with Power Query
Power Query solution 1 for Sum Pascal Triangle Columns, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Origen = Excel.CurrentWorkbook(){[Name = "Tabla1"]}[Content],
Sol = Table.AddColumn(
Origen,
"Answer",
(x) =>
let
N = x[Rows],
Origen = List.Accumulate(
{2 .. N},
{{1}},
(s, c) =>
s
& (
{
if c <= 2 then
List.Repeat({1}, c)
else
let
a = List.Last(s),
b = {1} & List.Transform({1 .. List.Count(a) - 1}, each a{_} + a{_ - 1}) & {1}
in
b
}
)
),
Datos = List.Transform(
Origen,
each
let
a = List.RemoveLastN(List.Combine(List.Transform(_, (x) => {x, null}))),
b = List.Repeat({null}, N - (List.Count(a) - 1) / 2 - 1),
c = b & a & b
in
c
),
Sol = Text.Combine(
List.Transform(Table.ToColumns(Table.FromRows(Datos)), each Text.From(List.Sum(_))),
", "
)
in
Sol
)
in
SolSolving the challenge of Sum Pascal Triangle Columns with Excel
Excel solution 1 for Sum Pascal Triangle Columns, proposed by Bo Rydobon 🇹🇭:
=MAP(A2:A5,
LAMBDA(n,
LET(s,
SEQUENCE(
n
)-1,
t,
SEQUENCE(
,
n*2-1,
0
),
p,
TOCOL(IFERROR(COMBIN(s,
(t+MOD(
s,
2
))/2)*MOD(
t+1,
2
),
)),
ARRAYTOTEXT(
DROP(
GROUPBY(
TOCOL(
s-t
),
p,
SUM,
,
0,
,
p
),
,
1
)
))))Excel solution 2 for Sum Pascal Triangle Columns, proposed by محمد حلمي:
=MAP(A2:A5,LAMBDA(r,LET(s,SEQUENCE(,r),e,s^0-1,
x,BYCOL(DROP(
REDUCE(HSTACK(e,1,e),s,LAMBDA(a,v,
LET(i,TAKE(a,-1),
VSTACK(a,IFNA(HSTACK(0,i)+DROP(i,,1),))))),-1,-r),
LAMBDA(a,SUM(a))),
ARRAYTOTEXT(FILTER(x,x)))))Excel solution 3 for Sum Pascal Triangle Columns, proposed by Julian Poeltl:
=MAP(A2:A5,LAMBDA(R,TEXTJOIN(", ",,BYCOL(MAKEARRAY(R,R*2-1,LAMBDA(A,B,IFERROR(COMBIN(A-1,(B-R+A)/2)*ISODD(B+A+R),0))),LAMBDA(A,SUM(A))))))Excel solution 4 for Sum Pascal Triangle Columns, proposed by Timothée BLIOT:
=LET(A,REDUCE(1,SEQUENCE(11),LAMBDA(w,v,IFNA(VSTACK(w,HSTACK(TAKE(w,-1,),0)+HSTACK(0,TAKE(w,-1,))),""))),B,IFNA(DROP(REDUCE(0,SEQUENCE(10),LAMBDA(w,v,VSTACK(w,HSTACK(EXPAND("",,11-v), TOROW(IFNA( HSTACK(TOCOL(INDEX(A,v,)),""),"")))))),1,1),""),MAP(A2:A5,LAMBDA(x, ARRAYTOTEXT(LET(C,BYCOL(TAKE(B,x),LAMBDA(y,SUM(y))),FILTER(C,C<>0))))))Excel solution 5 for Sum Pascal Triangle Columns, proposed by LEONARD OCHEA 🇷🇴:
=MAP(A2:A5,LAMBDA(n,LET(s,SEQUENCE(n)-1,t,SEQUENCE(,2*n-1),m,IF(MOD(t-s+ISEVEN(n),2),(t+s-n)/2,""),ARRAYTOTEXT(BYCOL( IF((m>=0)*(m<=s),COMBIN(s,m),0),SUM)))))Excel solution 6 for Sum Pascal Triangle Columns, proposed by ferhat CK:
=LET(n,
MAX(
A2:A5
),
a,
SEQUENCE(
n
),
b,
SEQUENCE(
,
n
),
d,
a>=b,
e,
d*b-1,
f,
TOCOL(IF(d,
(n-1)*(2*a-1)+2*b-1,
LN(
0
)),
3),
g,
HSTACK(
XLOOKUP(
SEQUENCE(
n,
2*n-1
),
f,
TOCOL(
COMBIN(
a-1,
e
),
3
),
""
)
),
BYROW(
A2:A5,
LAMBDA(
x,
LET(
r,
BYCOL(
TAKE(
g,
x
),
SUM
),
s,
FILTER(
r,
r>0
),
TEXTJOIN(
", ",
TRUE,
s
)
)
)
))Excel solution 7 for Sum Pascal Triangle Columns, proposed by Charles Roldan:
=MAP(A2:A5, LAMBDA(n, LET(f, LAMBDA(x, LET(s, SEQUENCE((x + 1) / 2, , 0), SUM(COMBIN(2 * s + n - x, s)))), ARRAYTOTEXT(REDUCE(f(n), SORT(SEQUENCE(n - 1), , -1), LAMBDA(a,b, HSTACK(f(b), a, f(b))))))))Excel solution 8 for Sum Pascal Triangle Columns, proposed by Charles Roldan:
=MAP(A2:A5, LAMBDA(n, ARRAYTOTEXT(BYCOL(MAKEARRAY(n, 2 * n - 1, LAMBDA(a,b, IF(AND(ISODD(a + b - n), a > ABS(b - n)), COMBIN(a - 1, (a + b - n) / 2)))), LAMBDA(x, SUM(x))))))Excel solution 9 for Sum Pascal Triangle Columns, proposed by Bilal Mahmoud kh.:
=LET(P,
10,
n,
REDUCE(,
SEQUENCE(
P
),
LAMBDA(x,
y,
IF(y=1,
"1",
IF(y=2,
VSTACK(
x,
"1,,1"
),
VSTACK(x,
TEXTJOIN(",",
FALSE,
LET(a,
INDEX(
x,
COUNTA(
x
),
1
),
b,
TEXTSPLIT(
a,
,
",",
TRUE
),
c,
VSTACK(INDEX(
b,
1,
1
),
"",
(INDEX(
b,
SEQUENCE(
COUNTA(
b
)-1,
,
2
),
1
)+INDEX(
b,
SEQUENCE(
COUNTA(
b
)-1,
,
1
),
1
))&",",
INDEX(
b,
COUNTA(
b
),
1
)),
c))))))),
m,
MAP(
SEQUENCE(
P,
,
P,
-1
),
n,
LAMBDA(
r,
s,
REPT(
" ,",
r
)&s&REPT(
" ,",
r
)
)
),
o,
TEXTSPLIT(
TEXTJOIN(
"-",
FALSE,
m
),
",",
"-"
),
DROP(
DROP(
BYCOL(
IFERROR(
--o,
0
),
LAMBDA(
t,
SUM(
t
)
)
),
,
-1
),
,
1
))Excel solution 10 for Sum Pascal Triangle Columns, proposed by Pieter de Bruijn:
=MAP(A2:A5,
LAMBDA(n,
LET(s,
SEQUENCE(
,
2*n-1
),
ARRAYTOTEXT(BYCOL(REDUCE(--(s=n),
SEQUENCE(
n-1
),
LAMBDA(
x,
y,
VSTACK(
x,
MMULT(
{1,
1},
IFERROR(
INDEX(
x,
y,
s+{1;-1}
),
)
)
)
)),
SUM)))))Excel solution 11 for Sum Pascal Triangle Columns, proposed by Anil Kumar Goyal:
= map(rows,
.f = (.n) {
accumulate(
seq(
.n - 1
),
.f = ~ c(
0,
.x
) + c(
.x,
0
),
.init = 1
) %>%
map_dfr(
~ set_names(
.x,
str_glue(
"C_{.n + 2*(seq_along(.x) - mean(seq_along(.x)))}"
)
)
) %>%
select(
str_sort(
tidyselect::peek_vars(),
numeric = TRUE
)
) %>%
summarise(
across(
everything(),
~ sum(
.,
na.rm = TRUE
)
)
) %>%
pivot_longer(
everything(),
names_to = NULL
) %>%
pull(
value
)
}))Solving the challenge of Sum Pascal Triangle Columns with Python in Excel
Python in Excel solution 1 for Sum Pascal Triangle Columns, proposed by Abdallah Ally:
def pascal_triangle(size):
values = []
rows = range(size)
cols = range(size)
for i in rows:
value = []
for j in cols:
if i == 0:
value = [0] * (size - 1) + [1]
break
if j == 0:
value.append(1 if i == max(rows) else 0)
else:
value.append(values[-1][j - 1] + values[-1][j + 1])
values.append(value + value[::-1][1:])
results = [str(sum([x[i] for x in values])) for i in range(2 * size - 1)]
return ', '.join(results)
df = xl("A1:B5", headers=True)
# Perform data munging
df['My Answer'] = df['Rows'].map(pascal_triangle)
df
Solving the challenge of Sum Pascal Triangle Columns with R
R solution 1 for Sum Pascal Triangle Columns, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Excel/509 Pascal Triangle Column Sums.xlsx"
input = read_excel(path, range = "A1:A5")
test = read_excel(path, range = "B1:B5") %>%
mutate(`Answer Expected` = ifelse(`Answer Expected` == "1, 1", "1, 1, 1", `Answer Expected`))
generate_pascal_triangle = function(n) {
triangle = matrix(0, n, 2*n - 1)
triangle[1, n] = 1
for (i in 2:n) {
for (j in 1:(2*n - 1)) {
if (j == 1) {
triangle[i, j] <- triangle[i - 1, j + 1]
} else if (j == 2*n - 1) {
triangle[i, j] <- triangle[i - 1, j - 1]
} else {
triangle[i, j] <- triangle[i - 1, j - 1] + triangle[i - 1, j + 1]
}
}
}
return(triangle)
}
colsum_pascal_triangle = function(n) {
triangle = generate_pascal_triangle(n)
colsum = colSums(triangle) %>%
paste(collapse = ", ")
return(colsum)
}
result = input %>%
mutate(`Answer Expected` = map_chr(Rows, colsum_pascal_triangle)) %>%
select(`Answer Expected`)
identical(result, test)
# [1] TRUE
R solution 2 for Sum Pascal Triangle Columns, proposed by Anil Kumar Goyal:
library(tidyverse)
pascal_nos <- function(n) {
accumulate(seq(n - 1),
.f = ~ c(0, .x) + c(.x, 0),
.init = 1) %>%
map_dfr(~ set_names(
.x,
str_glue("C_{n + 2*(seq_along(.x) - mean(seq_along(.x)))}")
)) %>%
select(str_sort(tidyselect::peek_vars(), numeric = TRUE)) %>%
summarise(across(everything(), ~ sum(., na.rm = TRUE))) %>%
pivot_longer(everything(), names_to = NULL) %>%
pull(value)
}
df <- data.frame(
rows = c(2, 4, 7, 10)
)
df %>%
mutate(Answer = map(rows, pascal_nos))
&&&