Find the sum of digits of both the diagonals for the grids given.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 345
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Sum digits along diagonals of grid with Power Query
Power Query solution 1 for Sum digits along diagonals of grid, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Table3"]}[Content],
R = Table.ToRows(Source),
N = List.Count(R) - 1,
S = List.Sum(
List.TransformMany(
List.TransformMany({0 .. N}, (i) => {R{i}{i}, R{i}{N - i}}, (i, o) => o),
(i) => Text.ToList(Text.From(i)),
(i, o) => Number.From(o)
)
)
in
SPower Query solution 2 for Sum digits along diagonals of grid, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
A = Excel.CurrentWorkbook()[Content],
Fx = (f) =>
let
D1 = List.Transform({0 .. Table.ColumnCount(f) - 1}, each Table.ToRows(f){_}{_}),
D2 = List.Transform({0 .. Table.ColumnCount(f) - 1}, each List.Reverse(Table.ToRows(f){_}){_})
in
List.Sum(
List.Transform(
List.Combine(List.Transform(D1 & D2, each Text.ToList(Text.From(_)))),
Number.From
)
),
Sol = List.Transform({0 .. List.Count(A) - 1}, each Fx(A{_}))
in
SolPower Query solution 3 for Sum digits along diagonals of grid, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
D1 = List.Transform({0..Table.ColumnCount(f)-1}, each Table.ToRows(f){_}{_}),
D2 = List.Transform({0..Table.ColumnCount(f)-1}, each List.Transform(Table.ToRows(f), List.Reverse){_}{_})
in List.Sum(List.Transform(List.Combine(List.Transform(D1&D2, each Text.ToList(Text.From(_)))), Number.From))
Fx(Table1)
Fx(Table2)
Fx(Table3)
Para todo efecto sería así aplicandolo a una sola tabla
let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
D1 = List.Transform({0..Table.ColumnCount(Source)-1}, each Table.ToRows(Source){_}{_}),
D2 = List.Transform({0..Table.ColumnCount(Source)-1}, each List.Transform(Table.ToRows(Source), List.Reverse){_}{_}),
Sol = List.Sum(List.Transform(List.Combine(List.Transform(D1&D2, each Text.ToList(Text.From(_)))), Number.From))
in
Sol
Power Query solution 4 for Sum digits along diagonals of grid, proposed by Ramiro Ayala Chávez:
let
Origen = Excel.CurrentWorkbook(){[Name = "Tabla3"]}[Content],
a = List.Combine(Table.ToRows(Origen)),
b = List.Alternate(a, Table.RowCount(Origen), 1, 1),
c = List.Transform(List.Combine(List.Transform(b, each Text.ToList(Text.From(_)))), Number.From),
d = Table.FromColumns(List.Transform(Table.ToRows(Origen), each List.Reverse(_))),
e = List.Combine(Table.ToRows(d)),
f = List.Alternate(e, Table.RowCount(Origen), 1, 1),
g = List.Transform(List.Combine(List.Transform(f, each Text.ToList(Text.From(_)))), Number.From),
Sol = Table.FromValue(List.Sum(List.RemoveNulls(c & g)), [DefaultColumnName = "Answer Expected"])
in
SolPower Query solution 5 for Sum digits along diagonals of grid, proposed by Rafael González B.:
let
tbl1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
tbl2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
tbl3 = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
tcom = Table.FromList({tbl1} & {tbl2} & {tbl3}, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
Fn_SumDiag = (tbl as table) =>
let
ListCol = Table.ToColumns(tbl),
LT = List.Transform,
a = List.Accumulate(
{0..List.Count(ListCol)-1},
{},
(s,c) => let
o = s & {ListCol{c}{c}} & {List.Reverse(ListCol){c}{c}},
p = LT(o, each Text.From(_))
in
p
),
b = LT(LT(a, each LT(Text.ToList(_), each Number.From(_))), each List.Sum(_)),
c = List.Sum(b)
in
c,
Anw = Table.AddColumn(tcom, "Anwer Expected", each Fn_SumDiag([Column1]))[[Anwer Expected]]
in
Anw
🧙♂️🧙♂️🧙♂️
Solving the challenge of Sum digits along diagonals of grid with Excel
Excel solution 1 for Sum digits along diagonals of grid, proposed by Bo Rydobon 🇹🇭:
=LET(z,
A2:E6,
m,
MUNIT(
ROWS(
z
)
),
SUM(--(0&MID(
CONCAT(
IF(
m+SORTBY(
m,
-ROW(
z
)
),
z,
""
)
),
SEQUENCE(
99
),
1
))))Excel solution 2 for Sum digits along diagonals of grid, proposed by Rick Rothstein:
=LET(a,
A13:E17,
b,
ROWS(
a
),
c,
CONCAT(a*MAKEARRAY(b,
b,
LAMBDA(r,
c,
((r=c)+(r=1+b-c))>0))),
SUM(
0+MID(
c,
SEQUENCE(
LEN(
c
)
),
1
)
))Excel solution 3 for Sum digits along diagonals of grid, proposed by John V.:
=LET(r,A2:B3,n,ROWS(r),b,MUNIT(n),SUM(--(0&MID(CONCAT(r*(b+SORTBY(b,-SEQUENCE(n)))),ROW(1:99),1))))Excel solution 4 for Sum digits along diagonals of grid, proposed by محمد حلمي:
=LET(q,
A2:A17,
j,
SCAN(
1,
q=0,
LAMBDA(
a,
d,
a+d
)
)*(q>0),
x,
MAP(j,
LAMBDA(d,
LET(
v,
FILTER(
A2:E17,
j=d
),
r,
ROWS(
v
),
s,
SEQUENCE(
r
),
SUM(--("0"&MID(
CONCAT(
INDEX(
v,
s,
HSTACK(
s,
r+1-s
)
)
),
SEQUENCE(
99
),
1
)))))),
IFS(
A1:A16,
"",
j,
x,
1,
""
))Excel solution 5 for Sum digits along diagonals of grid, proposed by محمد حلمي:
=LET(a,
A2:B3,
r,
ROWS(
a
),
s,
SEQUENCE(
r
),
SUM(--("0"&MID(
CONCAT(
INDEX(
a,
s,
HSTACK(
s,
r+1-s
)
)
),
SEQUENCE(
99
),
1
))))Excel solution 6 for Sum digits along diagonals of grid, proposed by Kris Jaganah:
=LET(a,MAP(A1:E17,LAMBDA(x,SUM(--MID(x,SEQUENCE(LEN(x)),1)))),b,SCAN(0,TAKE(a,,1),LAMBDA(x,y,IF(ISERR(y),0,x+1))),c,SCAN(0,b,LAMBDA(x,y,IF(y=1,x+y,x))),d,FILTER(HSTACK(a,b,c),b<>0),e,DROP(d,,-2),f,TAKE(d,,-2),g,DROP(f,,-1),h,DROP(f,,1),i,XLOOKUP(h,h,g,,,-1),j,TOCOL(e),k,ROWS(e),l,COLUMNS(e),m,TOCOL(INDEX(g,SEQUENCE(k),SEQUENCE(,l,,0))),n,TOCOL(INDEX(h,SEQUENCE(k),SEQUENCE(,l,,0))),o,--(j>=0),p,TOCOL(j,3),q,TOCOL(m/o,3),r,TOCOL(n/o,3),s,XLOOKUP(r,r,q,,,-1),t,UNIQUE(r),u,XLOOKUP(t,r,s)^2,v,DROP(REDUCE("",u,LAMBDA(x,y,VSTACK(x,SEQUENCE(y)))),1),w,MOD(v,s),aa,IF(w=0,s,w),bb,SORTBY(aa,r,1,aa,1),cc,SORTBY(aa,r,1,aa,-1),dd,MAP(t,LAMBDA(x,SUM(IF(((aa=bb)+(aa=cc))*(r=x),p)))),IF(b=1,XLOOKUP(c,t,dd),""))Excel solution 7 for Sum digits along diagonals of grid, proposed by Julian Poeltl:
=LET(A,
A2:B3,
L,
SQRT(
COUNTA(
A
)
),
M,
MAKEARRAY(L,
L,
LAMBDA(A,
B,
IF(OR(A=B,
(A+B)=L+1),
1,
0))),
P,
M*A,
SUM(
MAP(
P,
LAMBDA(
A,
SUM(
MID(
A,
SEQUENCE(
LEN(
A
)
),
1
)*1
)
)
)
))Excel solution 8 for Sum digits along diagonals of grid, proposed by Timothée BLIOT:
=LET(M,
A2:B3,
R,
ROWS(
M
),
A,
MUNIT(
R
),
B,
SORTBY(
A,
SEQUENCE(
R
),
-1
),
SUM(MAP((A+B)*M,
LAMBDA(
x,
SUM(
--MID(
x,
SEQUENCE(
LEN(
x
)
),
1
)
)
))))Excel solution 9 for Sum digits along diagonals of grid, proposed by Hussein SATOUR:
=LET(
a,
TOCOL(
A2:B3
),
b,
SQRT(
COUNT(
a
)
),
SUM(
IFERROR(
--MID(
CONCAT(
CHOOSEROWS(
a,
SEQUENCE(
b,
,
,
b+1
),
SEQUENCE(
b,
,
b,
b-1
)
)
),
SEQUENCE(
99
),
1
),
0
)
)
)Excel solution 10 for Sum digits along diagonals of grid, proposed by Oscar Mendez Roca Farell:
=LET(_d,
A2:B3,
_r,
ROWS(
_d
),
_n,
SEQUENCE(
_r
),
_f,
LAMBDA(x,
(_n=TOROW(
x
))),
_m,
_d*(_f(
_n
)+_f(
_r-_n+1
)),
_c,
CONCAT(
TOCOL(
_m
)
),
SUM(
TOCOL(
--MID(
_c,
SEQUENCE(
LEN(
_c
)
),
1
),
2
)
))Excel solution 11 for Sum digits along diagonals of grid, proposed by Sunny Baggu:
=LET(
r,
ROWS(
A2:B3
),
_e1,
LAMBDA(
_v,
SUM(
--MID(
_v,
SEQUENCE(
LEN(
_v
)
),
1
)
)
),
_e1(
CONCAT(
INDEX(
TOCOL(
A2:B3
),
VSTACK(
SEQUENCE(
r,
,
1,
r + 1
),
SEQUENCE(
r,
,
r,
r - 1
)
)
)
)
)
)Excel solution 12 for Sum digits along diagonals of grid, proposed by Sunny Baggu:
=LET(
_s,
SEQUENCE(
ROWS(
A2:B3
)
),
_rs,
TOROW(
_s
),
_revs,
SORT(
_rs,
,
-1,
1
),
_v,
CONCAT(TOCOL(IF((_s = _rs) + (_s = _revs),
A2:B3,
1 / x),
3)),
SUM(
--MID(
_v,
SEQUENCE(
LEN(
_v
)
),
1
)
)
)Excel solution 13 for Sum digits along diagonals of grid, proposed by Sunny Baggu:
=LAMBDA(
rng,
LET(
_a,
SEQUENCE(
ROWS(
rng
)
),
_b,
SORT(
_a,
,
-1
),
_c,
CONCAT(
VSTACK(
INDEX(
rng,
_a,
_a
),
INDEX(
rng,
_a,
_b
)
)
),
SUM(
--MID(
_c,
SEQUENCE(
LEN(
_c
)
),
1
)
)
)
)(A13:E17)Excel solution 14 for Sum digits along diagonals of grid, proposed by LEONARD OCHEA 🇷🇴:
=LET(g,
A13:E17,
f,
ROWS(
g
),
s,
SEQUENCE(
f
),
t,
TOROW(
s
),
c,
CONCAT(IF((s=t)+(s+t=f+1),
g,
"")),
REDUCE(
0,
SEQUENCE(
LEN(
c
)
),
LAMBDA(
a,
b,
a+MID(
c,
b,
1
)
)
))Excel solution 15 for Sum digits along diagonals of grid, proposed by Abdallah Ally:
=LET(
a,
A2:B3,
b,
ROWS(
a
),
c,
CHOOSECOLS(
a,
SEQUENCE(
,
b,
b,
-1
)
),
d,
MUNIT(
b
),
e,
CONCAT(
a*d,
c*d
),
SUM(
--MID(
e,
SEQUENCE(
LEN(
e
)
),
1
)
)
)Excel solution 16 for Sum digits along diagonals of grid, proposed by 🇵🇪 Ned Navarrete C.:
=LET(
s,
SEQUENCE(
ROWS(
A13:E17
)
),
m,
INDEX(
A13:E17,
VSTACK(
s,
s
),
VSTACK(
s,
SORT(
s,
,
-1
)
)
),
REDUCE(
0,
m,
LAMBDA(
c,
v,
c+SUM(
--MID(
v,
SEQUENCE(
LEN(
v
)
),
1
)
)
)
)
)Excel solution 17 for Sum digits along diagonals of grid, proposed by Bilal Mahmoud kh.:
=SUM(
MAP(
A2:B3,
LAMBDA(
a,
SUM&(
MID(
a,
TOROW(
SEQUENCE(
LEN(
a
)
)
),
1
)*1
)
)
)
)Excel solution 18 for Sum digits along diagonals of grid, proposed by Pieter de Bruijn:
=LET(
a,
A2:B3,
b,
ROWS(
a
),
s,
SEQUENCE(
b*2
),
m,
MOD(
s-1,
b
)+1,
SUM(
IFERROR(
--MID(
INDEX(
a,
m,
IF(
s>b,
m,
b+1-m
)
),
SEQUENCE(
,
15
),
1
),
0
)
)
)Excel solution 19 for Sum digits along diagonals of grid, proposed by Ziad A.:
=LET(
a,
A13:E17,
c,
COLUMNS(
a
),
SUM(
MAKEARRAY(
ROWS(
a
),
c,
LAMBDA(
i,
j,
IF(
OR(
i=j,
i=c-j+1
),
INDEX(
QUERY(
,
"select "®EXREPLACE(
INDEX(
a,
i,
j
),
"B",
"+"
)
),
2
)
)
)
)
)
)Excel solution 20 for Sum digits along diagonals of grid, proposed by Edwin Tisnado:
=LET(a,
A13:E17,
t,
ROWS(
a
),
l,
CONCAT(MAKEARRAY(t,
t,
LAMBDA(x,
y,
(x=y)+(t-x+1=y)<>0))*a),
SUM(
--MID(
l,
SEQUENCE(
LEN(
l
)
),
1
)
))Excel solution 21 for Sum digits along diagonals of grid, proposed by Abdelrahman Omer, MBA, PMP:
=LET(a,
A13:E17,
b,
SQRT(
COUNT(
a
)
),
d,
CONCAT(a*(MAKEARRAY(b,
b,
LAMBDA(r,
c,
((r=c)+(r=1+b-c))>0)))),
SUM(
0+MID(
d,
SEQUENCE(
LEN(
d
)
),
1
)
))Excel solution 22 for Sum digits along diagonals of grid, proposed by Hazem Hassan:
=LET(a,
A7:D10,
c,
ROWS(
a
),
b,
MUNIT(
c
),
d,
TOCOL(IF(IF(b,
b,
CHOOSECOLS(b,
(c+1)-SEQUENCE(
,
c
))),
a,
1/0),
3),
SUM(
IFERROR(
1*MID(
d,
SEQUENCE(
,
MAX(
LEN(
d
)
)
),
1
),
0
)
))Solving the challenge of Sum digits along diagonals of grid with R
R solution 1 for Sum digits along diagonals of grid, proposed by Konrad Gryczan, PhD:
With matrices today.
library(tidyverse)
library(readxl)
M1 = read_excel("Matrix Sum of Diagonal Digits.xlsx", range = "A2:B3", col_names = F) %>% as.matrix()
M2 = read_excel("Matrix Sum of Diagonal Digits.xlsx", range = "A7:D10", col_names = F) %>% as.matrix()
M3 = read_excel("Matrix Sum of Diagonal Digits.xlsx", range = "A13:e17", col_names = F) %>% as.matrix()
A1 = 47
A2 = 205
A3 = 236
diagonals_sum_of_digits = function(M) {
d1 = diag(M)
d2 = diag(M[, ncol(M):1])
s1 = sum(as.numeric(unlist(strsplit(as.character(d1), ""))))
s2 = sum(as.numeric(unlist(strsplit(as.character(d2), ""))))
return(s1 + s2)
}
identical(diagonals_sum_of_digits(M1), A1)
# [1] TRUE
identical(diagonals_sum_of_digits(M2), A2)
# [1] TRUE
identical(diagonals_sum_of_digits(M3), A3)
# [1] TRUE
&&