Prepare Bell Triangle for given n where n is number of rows. The methodology is as follows 1. Row 1 is always 1. 2. First number of any row is last number of previous row. 3. For other numbers in a row – Tn = T(n-1) of same row + T(n-1) of previous row.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 592
Challenge Difficulty: ⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Generate Bell Number Triangle with Power Query
_x000D_Power Query solution 1 for Generate Bell Number Triangle, proposed by Aditya Kumar Darak 🇮🇳:
let
N = 10,
Generate = List.Generate(
() => [a = 1, b = {1}],
each [a] <= N,
each [
a = [a] + 1,
b = List.Accumulate([b], List.MaxN([b], 1), (s, c) => s & {List.Last(s) + c})
],
each [b]
),
Return = Table.FromList(Generate, each _, N)
in
ReturnPower Query solution 2 for Generate Bell Number Triangle, proposed by Abdallah Ally:
let
length = 10,
Result = Table.FromList(
List.Generate(
() => [step = 0, nums = {1}],
each [step] < length,
each [
step = [step] + 1,
nums = List.Accumulate(
{1 .. step},
{List.Last([nums])},
(s, c) => s & {s{c - 1} + [nums]{c - 1}}
)
],
each [nums]
),
each _,
length
)
in
ResultSolving the challenge of Generate Bell Number Triangle with Excel
_x000D_Excel solution 1 for Generate Bell Number Triangle, proposed by Bo Rydobon 🇹🇭:
=REDUCE(
,
SEQUENCE(
B2
),
LAMBDA(
a,
i,
IFNA(
VSTACK(
a,
SCAN(
,
HSTACK(
MAX(
a
),
TAKE(
a,
-1
)
),
SUM
)
),
""
)
)
)Excel solution 2 for Generate Bell Number Triangle, proposed by Rick Rothstein:
=IFNA(
REDUCE(
1,
SEQUENCE(
B2-1
),
LAMBDA(
b,
y,
VSTACK(
b,
REDUCE(
1,
SEQUENCE(
y
),
LAMBDA(
a,
x,
LET(
r,
TOROW(
a,
3
),
t,
TAKE(
r,
,
-1
),
HSTACK(
t,
SCAN(
t,
r,
SUM
)
)
)
)
)
)
)
),
""
)Excel solution 3 for Generate Bell Number Triangle, proposed by John V.:
=REDUCE(
,
SEQUENCE(
B2
),
LAMBDA(
a,
v,
IFNA(
VSTACK(
a,
SCAN(
,
HSTACK(
MAX(
a
),
TAKE(
a,
-1
)
),
SUM
)
),
""
)
)
)Excel solution 4 for Generate Bell Number Triangle, proposed by Kris Jaganah:
=IFNA(REDUCE(1,SEQUENCE(B2-1),LAMBDA(v,w,VSTACK(v,REDUCE(1,SEQUENCE(w),LAMBDA(x,y,SCAN(0,HSTACK(MAX(x),x),SUM)))))),"")Excel solution 5 for Generate Bell Number Triangle, proposed by Julian Poeltl:
=IFNA(
REDUCE(
,
SEQUENCE(
B2
),
LAMBDA(
A,
B,
VSTACK(
A,
SCAN(
MAX(
TAKE(
A,
-1
)
),
SEQUENCE(
,
B,
0
),
LAMBDA(
C,
D,
IF(
D>0,
C+INDEX(
TAKE(
A,
-1
),
1,
D
),
C
)
)
)
)
)
),
""
)Excel solution 6 for Generate Bell Number Triangle, proposed by Timothée BLIOT:
=IFNA(REDUCE(1,SEQUENCE(B2-1),LAMBDA(w,v,VSTACK(w, HSTACK(TAKE(w,-1,-1),SCAN(TAKE(w,-1,-1),SEQUENCE(,v),LAMBDA(y,x, VALUE(y+INDEX(TAKE(w,-1,),,x)) ))) ))),"")Excel solution 7 for Generate Bell Number Triangle, proposed by Md. Zohurul Islam:
=LET(z,B2,seq,SEQUENCE(z-1),s,REDUCE(1,seq,LAMBDA(p,q,LET(a,MAX(p),b,TAKE(p,-1),d,HSTACK(a,b),e,SCAN(0,d,LAMBDA(x,y,x+y)),f,IFNA(VSTACK(p,e),""),f))),s)Excel solution 8 for Generate Bell Number Triangle, proposed by Pieter de B.:
=IFNA(REDUCE(1,SEQUENCE(B2-1,,2),LAMBDA(a,b,LET(c,TAKE(a,-1,-1),VSTACK(a,REDUCE(TAKE(a,-1,-1),SEQUENCE(b-1),LAMBDA(x,y,HSTACK(x,TAKE(x,-1,-1)+INDEX(TAKE(a,-1),,y)))))))),"")Excel solution 9 for Generate Bell Number Triangle, proposed by Julien Lacaze:
=LET(n, B2,
IFERROR(
REDUCE(,SEQUENCE(n),LAMBDA(a,v,VSTACK(a,IF(v = 1,1,
REDUCE(TAKE(a, -1, -1),SEQUENCE(, v - 1, 2),
LAMBDA(b,w, HSTACK(b, INDEX(b, 1, w - 1) + INDEX(a, v - 1, w - 1)))
))))),""))
the first REDUCE ensure that rows are stacking vertically.
Each row is calculated in the second REDUCE :
initial value is the previous row last value : TAKE(a,-1,-1)
it is calculating for other columns > SEQUENCE start at 2
then it's INDEX on current row (b) and previous row (a,v-1)Solving the challenge of Generate Bell Number Triangle with Python
_x000D_Python solution 1 for Generate Bell Number Triangle, proposed by Konrad Gryczan, PhD:
import pandas as pd
import numpy as np
path = "592 Bell Triangle.xlsx"
input_value = pd.read_excel(path, usecols="B", nrows=1, skiprows=1, header=None).iloc[0, 0]
test_10 = pd.read_excel(path, usecols="A:J", skiprows=3, nrows=10, header=None).fillna(0).values.astype(int)
M = np.full((input_value, input_value), -1, dtype=int)
M[0, 0] = 1
for i in range(1, input_value):
M[i, 0] = M[i - 1, i - 1]
for j in range(1, i + 1):
M[i, j] = M[i, j - 1] + M[i - 1, j - 1]
M[M == -1] = 0
print(np.array_equal(M, test_10)) # True
Python solution 2 for Generate Bell Number Triangle, proposed by Abdallah Ally:
import pandas as pd
# Create a function that generates a Bell Triangle items
def generate_bell_triangle(size):
values = [[1]]
for i in range(1, size):
value = [values[-1][-1]]
for j in range(1, i + 1):
value.append(value[j - 1] + values[-1][j - 1])
values.append(value)
items = [value + [""] * (size - len(value)) for value in values]
return items
# Perform data manipulation
df = pd.DataFrame(data=generate_bell_triangle(10))
df
Solving the challenge of Generate Bell Number Triangle with Python in Excel
_x000D_Python in Excel solution 1 for Generate Bell Number Triangle, proposed by Alejandro Campos:
def bell_triangle(n):
bell = [[1]]
for i in range(1, n):
row = [bell[i-1][-1]]
row.extend(row[j-1] + bell[i-1][j-1] for j in range(1, i+1))
bell.append(row)
return bell
df = pd.DataFrame(bell_triangle(xl("B2"))).fillna(' ')
df
Python in Excel solution 2 for Generate Bell Number Triangle, proposed by Aditya Kumar Darak 🇮🇳:
n = 10
rng = range(2, n + 1)
strt = [[1]]
temp = strt[-1]
rt = temp[-1]
result = [
((temp := strt[-1]), strt.append([temp[-1]] + [(rt := rt + x) for x in temp]))
for i in rng
]
df = pd.DataFrame(strt).fillna("")
Solving the challenge of Generate Bell Number Triangle with R
_x000D_R solution 1 for Generate Bell Number Triangle, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Excel/592 Bell Triangle.xlsx"
input = read_excel(path, range = "B2:B2", col_names = F) %>% pull()
test_10 = read_excel(path, range = "A4:J13", col_names = F) %>% as.matrix()
M = matrix(NA_integer_, nrow = input, ncol = input)
M[1, 1] = 1
for (i in 2:input) {
M[i, 1] = M[i - 1, i-1]
for(j in 2:i) {
M[i, j] = M[i, j - 1] + M[i - 1, j - 1]
}
}
all.equal(M, test_10, check.attributes = F)
## [1] TRUE
Solving the challenge of Generate Bell Number Triangle with Excel VBA
_x000D_Excel VBA solution 1 for Generate Bell Number Triangle, proposed by Md. Zohurul Islam:
Sub ExcelBI_ExcelCHallenge592()
Dim n, r, k, nk, mx
Dim a, b, d
Dim rng As Range
n = Range("B2").Value
nk = 1
Range("A4") = 1
For r = 2 To n
Set rng = Range(Cells(r + 2, 1), Cells(r + 2, 10))
'rng.Select
mx = WorksheetFunction.Max(rng)
Range("A" & r + 3) = mx
For k = 1 To nk
a = rng.Cells(k).Value
b = rng.Cells(k).Offset(1, 0).Value
d = a + b
Cells(r + 3, k).Offset(0, 1) = d
Next k
nk = nk + 1
Next r
End Sub