Generate the Clark’s Triangle. This is a kind of Pascal Triangle. The first element at top will be 0. Leftmost elements will be all 1. Rightmost elements will be multiplication table of 6. Any other element is sum of upper left and upper right number.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 602
Challenge Difficulty: ⭐️⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Generate Clark’s Triangle Pattern with Power Query
Power Query solution 1 for Generate Clark’s Triangle Pattern, proposed by Abdallah Ally:
let
Rows = 11,
Result = Table.FromRows(
List.Generate(
() => [r = 0, l = 1, v = {0}, n = Number.IntegerDivide(2 * Rows - 1 - l, 2)],
each [r] < Rows,
each [
r = [r] + 1,
l = 2 * r + 1,
v = List.Transform(
{0 .. l - 1},
(x) =>
if x = 0 then
1
else if x = l - 1 then
6 * r
else if Number.Mod(x, 2) = 0 then
[v]{x - 2} + [v]{x}
else
null
),
n = Number.IntegerDivide(2 * Rows - 1 - l, 2)
],
each List.Repeat({null}, [n]) & [v] & List.Repeat({null}, [n])
)
)
in
ResultSolving the challenge of Generate Clark’s Triangle Pattern with Excel
Excel solution 1 for Generate Clark’s Triangle Pattern, proposed by Bo Rydobon 🇹🇭:
=REDUCE(,SEQUENCE(11)-1,LAMBDA(a,i,LET(H,HSTACK,b,TAKE(a,-1),
IFNA(VSTACK(H("",a),H(1,IFERROR(H("",DROP(b,,-2)+DROP(b,,2),""),""),6*i)),""))))Excel solution 2 for Generate Clark’s Triangle Pattern, proposed by Bo Rydobon 🇹🇭:
=IFERROR(
REDUCE(
0,
SEQUENCE(
10
),
LAMBDA(
a,
i,
VSTACK(
HSTACK(
"",
a
),
HSTACK(
1,
BYCOL(
INDEX(
a,
i,
SEQUENCE(
,
i*2-1
)-{1;-1}
),
SUM
),
i*6
)
)
)
),
""
)Excel solution 3 for Generate Clark’s Triangle Pattern, proposed by John V.:
=LET(n,
11,
w,
2*n-1,
s,
SEQUENCE,
REDUCE(SWITCH(
s(
2,
w
),
n,
,
3*n-2,
1,
3*n,
6,
""
),
s(
n-2
),
LAMBDA(z,
v,
VSTACK(z,
MAP(s(
,
w
),
LAMBDA(x,
LET(y,
TAKE(
z,
-1
),
a,
INDEX(
y,
MAX(
1,
x-1
)
),
b,
INDEX(
y,
MIN(
w,
x+1
)
),
IFS(b=1,
b,
(a<"")*(b<""),
a+b,
a=6*v,
6+6*v,
1,
""))))))))Excel solution 4 for Generate Clark’s Triangle Pattern, proposed by Timothée BLIOT:
=LET(N,11,A,REDUCE(0,SEQUENCE(N-1),LAMBDA(w,v,VSTACK(w,IF(v>1, HSTACK(1,DROP(TAKE(w,-1,),,1)+DROP(TAKE(w,-1,),,-1),v*6),HSTACK(1,6))))),C,TOCOL(A,3),MAKEARRAY(N,N*2-1,LAMBDA(x,y,IF(AND(y-x<=N-1,x+y>=N+1,ISODD(x+y+N)),XLOOKUP((x-1)*x/2+(y-N+x-1)/2+1,SEQUENCE(ROWS(C)),C),""))))Excel solution 5 for Generate Clark’s Triangle Pattern, proposed by LEONARD OCHEA 🇷🇴:
=LET(n,
11,
S,
SEQUENCE,
H,
HSTACK,
V,
VSTACK,
C,
TAKE,
D,
DROP,
y,
S(
,
2*n-1
),
x,
S(
n
),
m,
D(REDUCE(0,
x,
LAMBDA(a,
b,
V(a,
IFS(y=n+1-b,
1,
y=n-1+b,
6*(b-1),
1,
D(
H(
0,
C(
a,
-1
)
),
,
-1
)+D(
H(
C(
a,
-1
),
0
),
,
1
))))),
1),
V(
IFERROR(
IF(
C(
m,
1
),
z,
""
),
0
),
D(
IF(
m,
m,
""
),
1
)
))Excel solution 6 for Generate Clark’s Triangle Pattern, proposed by Jaroslaw Kujawa:
=LET(v;REDUCE(MAKEARRAY(1;21;LAMBDA(r;c;IF(c=11;0;"")));SEQUENCE(10;;2);LAMBDA(a;x;VSTACK(a;MAKEARRAY(1;21;LAMBDA(r;c;IF(c=11-x+1;1;IF(c=11+x-1;(x-1)*6;IF(x>2;IFERROR(INDEX(TAKE(a;-1);1;c+1);0)+IF(c>1;IFERROR(INDEX(TAKE(a;-1);1;c-1);0))))))))));VSTACK(TAKE(v;1);IF(DROP(v;1);DROP(v;1);"")))Solving the challenge of Generate Clark’s Triangle Pattern with Python
Python solution 1 for Generate Clark’s Triangle Pattern, proposed by Konrad Gryczan, PhD:
import pandas as pd
import numpy as np
path = "602 Clarks Triangle.xlsx"
test = pd.read_excel(path,header=None, usecols="A:U", skiprows=1, nrows=11).fillna(0).to_numpy()
def create_clarks_matrix(n):
mat = np.zeros((n, n * 2 - 1), dtype=int)
mid = n - 1
for i in range(n):
for j in range(n * 2 - 1):
if j == mid - i:
mat[i, j] = 0 if i == 0 else 1
elif j == mid + i:
mat[i, j] = 6 * i
elif i > 0 and j > 0 and j < n * 2 - 2 and mat[i - 1, j - 1] != 0 and mat[i - 1, j + 1] != 0:
mat[i, j] = mat[i - 1, j - 1] + mat[i - 1, j + 1]
return mat
n = 11
clark = create_clarks_matrix(n)
print(np.array_equal(clark, test))
# True
Python solution 2 for Generate Clark’s Triangle Pattern, proposed by Abdallah Ally:
import pandas as pd
def generate_clarks_triangle(rows):
values = [[0]]
for r in range(1, rows):
length = 2 * r + 1
value = []
for x in range(length):
if x == 0:
value.append(1)
elif x == length - 1:
value.append(6 * r)
elif x % 2 == 0:
value.append(values[-1][x - 2] + values[-1][x])
else:
value.append('')
values.append(value)
items = [
[''] * n + values[i] + [''] * n for i in range(len(values))
if (n := (2 * rows - 1 - len(values[i])) // 2) >= 0
]
return items
# Generate Clark's Triangle for 11 rows
df = pd.DataFrame(data=generate_clarks_triangle(11))
df
Solving the challenge of Generate Clark’s Triangle Pattern with Python in Excel
Python in Excel solution 1 for Generate Clark’s Triangle Pattern, proposed by Alejandro Campos:
def generate_clarks_triangle(rows):
triangle = [[0]]
for i in range(1, rows):
row = []
for j in range(i + 1):
if j == 0:
row.append(1)
elif j == i:
row.append(6 * i)
else:
row.append(triangle[i - 1][j - 1] + triangle[i - 1][j])
triangle.append(row)
return triangle
def center_triangle(triangle):
max_length = len(triangle[-1]) * 2 - 1
centered_triangle = []
for row in triangle:
centered_row = [np.nan] * max_length
start_idx = (max_length - len(row) * 2 + 1) // 2
for i, val in enumerate(row):
centered_row[start_idx + i * 2] = val
centered_triangle.append(centered_row)
return centered_triangle
rows = xl("V2")
clarks_triangle = generate_clarks_triangle(rows)
centered_clarks_triangle = center_triangle(clarks_triangle)
df_centered = pd.DataFrame(centered_clarks_triangle).fillna(' ')
df_centered
Solving the challenge of Generate Clark’s Triangle Pattern with R
R solution 1 for Generate Clark’s Triangle Pattern, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Excel/602 Clarks Triangle.xlsx"
test = read_excel(path, range = "A2:U12", col_names = F) %>% as.matrix() %>% replace(is.na(.), 0)
create_clarks_matrix = function(n) {
mat = matrix(0, nrow = n, ncol = n * 2 - 1)
mid = n
for (i in 1:n) {
for (j in 1:(n * 2 - 1)) {
if (j == mid - (i - 1)) {
mat[i, j] = ifelse(i == 1, 0, 1)
} else if (j == mid + (i - 1)) {
mat[i, j] = 6 * (i - 1)
} else if (i > 1 && j > 1 && j < n * 2 - 1 && mat[i - 1, j - 1] != 0 && mat[i - 1, j + 1] != 0) {
mat[i, j] = mat[i - 1, j - 1] + mat[i - 1, j + 1]
}
}
}
return(mat)
}
n = 11
clark = create_clarks_matrix(n)
all.equal(clark, test, check.attributes = F)
# [1] TRUE
Solving the challenge of Generate Clark’s Triangle Pattern with Excel VBA
Excel VBA solution 1 for Generate Clark’s Triangle Pattern, proposed by Md. Zohurul Islam:
Sub ExcelBI_ExcelChallenge602()
Dim start As Range
Dim rng As Range
Dim n As Long, x As Long, i As Long
Dim a As Range, b As Range, c As Range
Dim v1, v2, v
Dim col As Long
n = 10
Set start = Range("K2")
start = 0
For x = 1 To n
Set a = start.Offset(x, -x)
Set b = start.Offset(x, x)
Set rng = Range(a, b)
col = rng.Columns.Count
For i = 1 To col
Set c = rng.Cells(i)
On Error Resume Next
v1 = c.Offset(-1, -1).Value
v2 = c.Offset(-1, 1).Value
v = v1 + v2
If v = 0 Then
c = ""
Else
c = v
End If
Next i
a = 1
b = 6 * x
Next x
Range("A:U").EntireColumn.AutoFit
End Sub
&&&