Provide a formula to populate Yes or No against given 3×3 magic squares. The given magic square is that 3×3 grid where a. 1 to 9 appears once (This has been ensured in given squares. Hence, don’t check for this) b. Sum over Rows, Columns and Diagonals=15 (You will need to check only this. This number 15 is also called magic constant for this magic square)
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 25
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Check 3×3 Magic Square with Power Query
Power Query solution 1 for Check 3×3 Magic Square, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Columns = List.AllTrue(List.Transform(Table.ToColumns(Source), (f) => List.Sum(f) = 15)),
Rows = List.AllTrue(List.Transform(Table.ToRows(Source), (f) => List.Sum(f) = 15)),
Diagonal1 = List.Sum(List.Transform({0 .. 2}, (f) => Record.ToList(Source{f}){f})) = 15,
Diagonal2 = List.Sum(List.Transform({0 .. 2}, (f) => Record.ToList(Source{f}){2 - f})) = 15,
Final = List.AllTrue(({Columns, Rows, Diagonal1, Diagonal2}))
in
FinalPower Query solution 2 for Check 3×3 Magic Square, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name="Table7"]}[Content],
Cols = List.Accumulate ({0..Table.RowCount(Source)-1}, 0, (s,c)=> s + (if List.Sum(Table.ToColumns (Source){c}) = 15 then 15 else 0 )),
Rows = List.Accumulate ({0..Table.RowCount(Source)-1}, 0, (s,c)=> s + (if List.Sum(Table.ToRows (Source){c}) = 15 then 15 else 0 )),
Diag1 = List.Accumulate ({0..Table.RowCount(Source)-1}, 0, (s,c)=> s+List.Sum({Table.ToColumns(Source){c}{c}})),
Diag2 = List.Accumulate ({0..Table.RowCount(Source)-1}, 0, (s,c)=> s+List.Sum({List.Reverse( Table.ToColumns(Source)){c}{c}})),
Custom2 = if Cols+Rows+Diag1+Diag2 = 120 then "Yes" else "No"
in
Custom2
Show translation
Show translation of this commentPower Query solution 3 for Check 3×3 Magic Square, proposed by Brian Julius:
let
Source = Table.Split(
Table.SelectRows(
Table.TransformColumnTypes(
#"MagicSq Raw",
{{"Column1", Int64.Type}, {"Column2", Int64.Type}, {"Column3", Int64.Type}}
),
each [Column1] <> null
),
3
),
ToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
DupeCol = Table.RenameColumns(
Table.DuplicateColumn(ToTable, "Column1", "Column2"),
{{"Column1", "Nested1"}, {"Column2", "Nested2"}}
),
TransposeNested2 = Table.TransformColumns(DupeCol, {"Nested2", each Table.Transpose(_)}),
ToRows = Table.TransformColumns(
TransposeNested2,
{
{"Nested1", each List.Sum([Column1]) * List.Sum([Column2]) * List.Sum([Column3])},
{"Nested2", each List.Sum([Column1]) * List.Sum([Column2]) * List.Sum([Column3])}
}
),
Test = Table.AddColumn(
ToRows,
"Answer",
each if List.AllTrue({[Nested1] = 3375, [Nested2] = 3375}) then "Yes" else "No"
),
Clean = Table.SelectColumns(Test, {"Answer"})
in
CleanPower Query solution 4 for Check 3×3 Magic Square, proposed by Matthias Friedmann:
let
Source = Excel.CurrentWorkbook(){[Name = "MagicSquare"]}[Content],
#"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(
#"Added Index",
"MagicSquare",
each
let
ind = [Index],
square = Table.SelectColumns(
Table.Range(#"Added Index", ind, 3),
List.FirstN(Table.ColumnNames(#"Added Index"), 3)
),
diagonal1 = {square[Column1]{0}, square[Column2]{1}, square[Column3]{2}},
diagonal2 = {square[Column1]{2}, square[Column2]{1}, square[Column3]{0}}
in
if (try #"Added Index"[Column1]{[Index] - 1} = null otherwise false) then
List.AllTrue(List.Transform(Table.ToColumns(square), each List.Sum(_) = 15))
and List.AllTrue(
List.Transform(Table.ToColumns(Table.Transpose(square)), each List.Sum(_) = 15)
)
and List.Sum(diagonal1)
= 15 and List.Sum(diagonal2)
= 15
else
null
),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom", {"Index"})
in
#"Removed Columns"Power Query solution 5 for Check 3×3 Magic Square, proposed by Melissa de Korte:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
MagicSquare = List.Transform(
Table.Split(Source, Table.ColumnCount(Source)),
each List.AllTrue(
{
List.Sum(List.Transform({0 .. Table.ColumnCount(_) - 1}, (x) => Table.ToColumns(_){x}{x}))
= 15
}
& List.Transform(Table.ToRecords(_), (x) => List.Sum(Record.ToList(x)) = 15)
& List.Transform(Table.ToColumns(_), (x) => List.Sum(x) = 15)
)
)
in
MagicSquareSolving the challenge of Check 3×3 Magic Square with Excel
Excel solution 1 for Check 3×3 Magic Square, proposed by Rick Rothstein:
=LET(A,B2:D4,R,15,C,ROWS(A),AND(BYROW(A,LAMBDA(x,SUM(x)=R)),BYCOL(A,LAMBDA(x,SUM(x)=R)),SUM(A*MUNIT(C))=R,SUM(INDEX(A,SEQUENCE(C),1+C-SEQUENCE(C)))=R))Excel solution 2 for Check 3×3 Magic Square, proposed by Julian Poeltl:
=LET(S,B2:D4,IF((SUM((BYROW(S,LAMBDA(A,SUM(A)))=15)*(BYCOL(S,LAMBDA(A,SUM(A)))=15))=9)*(SUM(INDEX(S,SEQUENCE(3),SEQUENCE(3)))=15)*(SUM(INDEX(S,SEQUENCE(3,,3,-1),SEQUENCE(3)))=15),"Yes","No"))Excel solution 3 for Check 3×3 Magic Square, proposed by Aditya Kumar Darak 🇮🇳:
= LET(
_sum,
LAMBDA(a, SUM(a) = 15),
_row,
BYROW(B2:D4, _sum),
_col,
BYCOL(B2:D4, _sum),
_dig,
BYROW(INDEX(B2:D4, {1,2,3}, {1,2,3;3,2,1}), _sum),
IF(AND(_row, _col, _dig), "Yes", "No"))Excel solution 4 for Check 3×3 Magic Square, proposed by Timothée BLIOT:
=LET(
array,
B2:D4,
IF(
IF(
SUM(
IF(
BYROW(
array,
LAMBDA(
r,
SUM(
BYCOL(
r,
LAMBDA(
c,
SUM(
c
)
)
)
)
)
)
=15,
1,
0
)
)=3,
1,
0
)+
IF(
SUM(
INDEX(
array,
SEQUENCE(
ROWS(
array
)
),
COLUMNS(
array
)-SEQUENCE(
COLUMNS(
array
)
)+1
)
)=15,
1,
0
)
=2,
"Yes",
"No"
)
)Excel solution 5 for Check 3×3 Magic Square, proposed by Bhavya Gupta:
=LET(
m,
Matrix,
IF(
AND(
VSTACK(
BYROW(
m,
LAMBDA(
x,
SUM(
x
)
)
),
TOCOL(
BYCOL(
m,
LAMBDA(
x,
SUM(
x
)
)
)
),
SUM(
INDEX(
m,
{1,
2,
3},
{1,
2,
3}
)
),
SUM(
INDEX(
m,
{1,
2,
3},
{3,
2,
1}
)
)
)=15
),
"Yes",
"No"
)
)Excel solution 6 for Check 3×3 Magic Square, proposed by Charles Roldan:
=LET(
CheckMatrix,
{1,
1,
1,
0,
0,
0,
0,
0,
0;0,
0,
0,
1,
1,
1,
0,
0,
0;0,
0,
0,
0,
0,
0,
1,
1,
1;1,
0,
0,
1,
0,
0,
1,
0,
0;0,
1,
0,
0,
1,
0,
0,
1,
0;0,
0,
1,
0,
0,
1,
0,
0,
1;1,
0,
0,
0,
1,
0,
0,
0,
1;0,
0,
1,
0,
1,
0,
1,
0,
0},
AND(
MMULT(
CheckMatrix,
TOCOL(
B2:D4
)
)=15
)
)Excel solution 7 for Check 3×3 Magic Square, proposed by Matthias Friedmann:
=IF(A2<>"";"";
IF(AND(SUM(A3:C3);SUM(A4:C4)=15;SUM(A5:C5)=15;
SUM(A3:A5);SUM(B3:B5)=15;SUM(C3:C5)=15;
(A3+B4+C5)=15;(A5+B4+C3)=15);"Yes";"No"))Excel solution 8 for Check 3×3 Magic Square, proposed by Cary Ballard, DML:
=LET(
a,
B2:D4,
n,
15,
d,
LAMBDA(
x,
y,
z,
SUM(
TOCOL(
IFS(
SEQUENCE(
9
) = HSTACK(
x,
y,
z
),
TOCOL(
a
)
),
2
)
) = n
),
IF(
AND(
AND(
BYROW(
a,
SUM
) = n
),
AND(
BYCOL(
a,
SUM
) = n
),
d(
1,
5,
9
),
d(
3,
5,
7
)
),
"Yes",
"No"
)
)Excel solution 9 for Check 3×3 Magic Square, proposed by Juliano Santos Lima:
=SUM(
B2:D4*MUNIT(
3
)
)=SUM(
B2:D2
)
Second approach
=SUM(
INDEX(
B2:D4;
{1;
2;
3};
{1;
2;
3}
)
)=SUM(
B2:D2
)Excel solution 10 for Check 3×3 Magic Square, proposed by Juliano Santos Lima:
=AND(
SUM(
B2:D4*MUNIT(
3
)
)=SUM(
B2:D2
);
COUNTIF(
B2:D4;
B2:D4
)=1
)Excel solution 11 for Check 3×3 Magic Square, proposed by Ibrahim Sadiq:
=LET(
R,
B2:D4,
x,
ROWS(
R
),
y,
SEQUENCE(
x
),
z,
SEQUENCE(
x,
,
x,
-1
),
b,
BYROW(
R,
LAMBDA(
a,
SUM(
a
)
)
),
c,
SUM(
INDEX(
R,
z,
z
)
),
d,
SUM(
INDEX(
R,
y,
y
)
),
IF(
AND(
b=15,
c=15,
d=15
),
"Yes",
"No"
)
)Excel solution 12 for Check 3×3 Magic Square, proposed by Nazmul Islam Jobair:
=LET(
_sq,
B2:D4,
_magic,
15,
_dim,
ROWS(
_sq
),
_rowSum,
BYROW(
_sq,
SUM
) = _magic,
_colSum,
BYCOL(
_sq,
SUM
) = _magic,
_d1,
SUM(
_sq * MUNIT(
_dim
)
) = _magic,
_d2,
SUM(
INDEX(
_sq,
SEQUENCE(
_dim
),
SEQUENCE(
_dim,
,
_dim,
-1
)
)
) = _magic,
IF(
AND(
_rowSum,
_colSum,
_d1,
_d2
),
"Yes",
"No"
)
)Excel solution 13 for Check 3×3 Magic Square, proposed by Charalampos Dimitrakopoulos:
=LET(
square,
A2:C4,
a,
BYROW(
square,
LAMBDA(
r,
SUM(
r
)
)
),
b,
BYCOL(
square,
LAMBDA(
c,
SUM(
c
)
)
),
diag1,
SUM(
square * MUNIT(
3
)
),
diag2,
SUM(
square * {0,
0,
1; 0,
1,
0; 1,
0,
0}
),
check,
AND(
MAX(
a
) = MIN(
a
),
MAX(
b
) = MIN(
b
),
diag1 = diag2,
MAX(
a
) = MAX(
b
),
MAX(
a
) = diag1
),
check
)Excel solution 14 for Check 3×3 Magic Square, proposed by Kolyu Minevski:
=IF(
AND(
SUM(
B2:D2
)=15;
SUM(
B3:D3
)=15;
SUM(
B4:D4
)=15;
SUM(
B2:B4
)=15;
SUM(
C2:C4
)=15;
SUM(
D2:D4
)=15;
SUM(
B2;
C3;
D4
)=15;
SUM(
B4;
C3;
D2
)=15
);
"yes";
"no"
)Excel solution 15 for Check 3×3 Magic Square, proposed by Gonnie Levy – Tuurenho&ut:
=IF(AND(SUM(B2:D2)=15;SUM(B3:D3)=15;SUM(B4:D4)=15;SUM(B2:B4)=15;SUM(C2:C4)=15;SUM(D2:D4)=15;SUM(B2;C3;D4)=15;SUM(D2;C3;B4)=15);"Yes";"No") there is a shorter solution;
=IF(SUMPRODUCT(B2:B4;C2:C4;D2:D4)+SUM(B2;C3;D4)=240;"Yes";"No")