Given the r (number of rows) value in column A, generate the given triangles. Example are given for r = 2, 3, 4 & 7.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 448
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Generate Triangles By Row Count with Power Query
Power Query solution 1 for Generate Triangles By Row Count, proposed by Rick de Groot:
let
myValue = 7,
Source = List.Generate(
()=> [ input = myValue, x = 1, y = {{1}} ],
each [x] <= [input],
each [ input = [input], x = [x] + 1,
y = let previous = [y],
new = List.Transform( {1..x}, (x) => x + List.Last( List.Last( [y] ) ) ) ,
combine = previous & { new } in combine
],
each [y]
) ,
Select = List.Last( Source ),
FormList = List.Transform( Select, each let myList = _ & List.Repeat( { null }, myValue - List.Count(_) ) in List.Reverse( List.Skip( myList ) ) & myList ),
Res = Table.ReverseRows( Table.FromRows( FormList, myValue*2 - 1 ) )
in
Res
hashtag#BIGorilla hashtag#powerqueryhow hashtag#powerquery
Power Query solution 2 for Generate Triangles By Row Count, proposed by Aditya Kumar Darak 🇮🇳:
let
N = 4,
Generate = List.Transform(
{1 .. N},
(n) =>
[
S1 = n * (n + 1) / 2,
S2 = n * (n - 1) / 2 + 1,
L = {S2 .. S1},
C = List.Reverse(List.Skip(L)) & L,
B = List.Repeat({null}, N - n),
R = Table.FromRows({B & C})
][R]
),
Combine = Table.Combine(Generate),
Return = Table.ReverseRows(Combine)
in
ReturnPower Query solution 3 for Generate Triangles By Row Count, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = 7,
Acc = List.Skip(List.Accumulate({1 .. Source}, {0}, (s, c) => s & {List.Last(s) + c})),
Lista = List.Reverse(
List.Transform(
{0 .. Source - 1},
each try
List.RemoveFirstN(List.FirstN({1 .. List.Last(Acc)}, Acc{_}), Acc{_ - 1})
otherwise
{1}
)
),
Lista2 = List.Transform(Lista, each List.Reverse(List.Skip(_)) & _),
Sol = Table.FromRows(
List.Transform(
{0 .. Source - 1},
each List.Repeat({null}, _) & Lista2{_} & List.Repeat({null}, _)
)
)
in
SolPower Query solution 4 for Generate Triangles By Row Count, proposed by Ramiro Ayala Chávez:
let
Rows = 7,
n = List.Generate(
() => [i = 2, j = 2, l = {1}],
each [i] < Rows + 2,
each [i = [i] + 1, j = [j] + [i], l = {[j] .. j - 1}],
each [l]
),
a = List.Transform(n, each List.InsertRange(_, 0, List.Reverse(List.LastN(_, List.Count(_) - 1)))),
b = List.Generate(
() => [i = 0, k = Rows - 1],
each [i] < List.Count(a),
each [i = [i] + 1, k = [k] - 1],
each List.Repeat({null}, [k]) & a{[i]} & List.Repeat({null}, [k])
),
Sol = Table.FromRows(List.Reverse(b))
in
SolSolving the challenge of Generate Triangles By Row Count with Excel
Excel solution 1 for Generate Triangles By Row Count, proposed by Bo Rydobon 🇹🇭:
=LET(r,
A14,
s,
r-SEQUENCE(
r
),
t,
ABS(
SEQUENCE(
,
r*2-1
)-r
),
IF(sExcel solution 2 for Generate Triangles By Row Count, proposed by Bo Rydobon 🇹🇭:
=LET(s,
-SORT(
-SEQUENCE(
A14
)
),
t,
TOROW(
s
),
u,
IF(sExcel solution 3 for Generate Triangles By Row Count, proposed by Rick Rothstein:
=LET(a,
A14,
t,
2*a,
s,
ABS(
SEQUENCE(
,
t-1,
1-a
)
)+SCAN(0,
SEQUENCE(
a,
,
a,
-1
),
LAMBDA(s,
x,
(2-x+x*x)/2)),
MAKEARRAY(
a,
t-1,
LAMBDA(
r,
c,
IF(
OR(
r>c,
r>t-c
),
"",
INDEX(
s,
r,
c
)
)
)
))Excel solution 4 for Generate Triangles By Row Count, proposed by John V.:
=LET(n,
A9,
r,
SEQUENCE(
n
)-1,
c,
SEQUENCE(
,
2*n-1
),
IF((rExcel solution 5 for Generate Triangles By Row Count, proposed by محمد حلمي:
=LET(
s,
SEQUENCE(
A14
)-1,
i,
TOROW(
s
),
x,
IF(
SORTBY(
s>=i,
-s
),
HSTACK(
SORT(
SCAN(
1,
s,
LAMBDA(
a,
d,
a+d
)
),
,
-1
)+i
),
""
),
HSTACK(
DROP(
SORT(
x,
,
-1,
1
),
,
-1
),
x
)
)Excel solution 6 for Generate Triangles By Row Count, proposed by Kris Jaganah:
=LET(a,
14,
b,
a*(a+1)/2,
c,
SEQUENCE,
d,
c(
,
a,
b-a+1
),
e,
c(
,
a-1,
b,
-1
),
f,
HSTACK(
e,
d
),
g,
c(
a-1,
,
a-1,
-1
),
h,
SCAN(
,
g,
SUM
),
VSTACK(f,
IF(f-h<=g*(g+1)/2,
f-h,
"")))Excel solution 7 for Generate Triangles By Row Count, proposed by Julian Poeltl:
=LET(R,
A9,
S,
R-SEQUENCE(
R
)+1,
T,
TRANSPOSE(
S
),
C,
(S^2-S)/2+T,
I,
IF(
S>=T,
C,
""
),
HSTACK(
I,
CHOOSECOLS(
I,
SEQUENCE(
R-1,
,
R-1,
-1
)
)
))Excel solution 8 for Generate Triangles By Row Count, proposed by Timothée BLIOT:
=LET(A,
A2,
MAKEARRAY(A,
2*A-1,
LAMBDA(x,
y,
IF(AND(
2*A-y>=x,
y>=x
),
(A-x)*(A-x+1)/2+1+ABS(
A-y
),
""))))
Given: n*(n+1)Excel solution 9 for Generate Triangles By Row Count, proposed by Sunny Baggu:
=LET(
_s,
SEQUENCE(
A14
),
_s1,
_s - 1,
_rt,
SORT(
SCAN(
1,
_s1,
LAMBDA(
a,
v,
a + v
)
),
,
-1
),
_t1,
_rt + SORT(
TOROW(
_s1
),
,
-1,
1
),
_t2,
_rt + TOROW(
_s1
),
_ft1,
IF(
_s <= TOROW(
_s
),
_t1,
""
),
_ft2,
SORTBY(
_ft1,
TOROW(
_s
),
-1
),
HSTACK(
_ft1,
DROP(
_ft2,
,
1
)
)
)Excel solution 10 for Generate Triangles By Row Count, proposed by LEONARD OCHEA 🇷🇴:
=LET(n,
A2,
s,
n-SEQUENCE(
n
)+1,
t,
TOROW(
s
),
i,
IF(s>=t,
(s^2-s)/2+t,
""),
HSTACK(
i,
CHOOSECOLS(
i,
DROP(
t,
,
1
)
)
))Excel solution 11 for Generate Triangles By Row Count, proposed by Abdallah Ally:
=LET(a,
A5,
b,
REDUCE(1,
SEQUENCE(
a-1
),
LAMBDA(x,
y,
VSTACK(x,
SEQUENCE(,
y+1,
y*(y+1)/2+1)))),
c,
HSTACK(
DROP(
SORTBY(
b,
-SEQUENCE(
,
a
)
),
,
-1
),
b
),
IFNA(
SORTBY(
c,
-SEQUENCE(
a
)
),
""
))Excel solution 12 for Generate Triangles By Row Count, proposed by Abdallah Ally:
=LET(a,
A14,
b,
SEQUENCE(,
a*(a+1)/2),
c,
IFNA(DROP(REDUCE("",
SEQUENCE(
a
),
LAMBDA(x,
y,
VSTACK(x,
SEQUENCE(,
y,
y*(y-1)/2+1)))),
1),
""),
d,
DROP(
SORTBY(
c,
-SEQUENCE(
,
a
)
),
,
-1
),
SORTBY(
HSTACK(
d,
c
),
-SEQUENCE(
a
)
))Solving the challenge of Generate Triangles By Row Count with Python
Python solution 1 for Generate Triangles By Row Count, proposed by Rayan S.:
def generate_triangle(r):
triangle = []
for row in range(r, 0, -1):
current_row = []
middle_value = row
for offset in range(row - 1, -row, -1):
current_row.append(middle_value + abs(offset))
triangle.append(current_row)
return triangle
def display_triangle(triangle):
max_width = len(triangle[0])
for row in triangle:
row_str = ", ".join(map(str, row))
centered_row_str = row_str.center(max_width * 3)
print(centered_row_str)
r_values = [2, 3, 4, 7]
for r in r_values:
print(f"Triangle for r = {r}:")
triangle = generate_triangle(r)
display_triangle(triangle)
print() # Newline for readability
Solving the challenge of Generate Triangles By Row Count with Python in Excel
Python in Excel solution 1 for Generate Triangles By Row Count, proposed by Abdallah Ally:
import pandas as pd
def inverted_triangle(n):
values = []
for i in range(n):
start = i * (i + 1) // 2 + 1
value = list(range(start, start + i + 1))
value = value[::-1][:-1] + value
empty = [''] * (((2 * n - 1) - len(value)) // 2)
value = empty + value + empty
values.insert(0, value)
return values
df = pd.DataFrame(inverted_triangle(7))
df
&&&