Diagonal Sum Difference

Provide a formula to find the difference between sum of right diagonal and sum of left diagonal. Sum of right diagonal (Green ones) = 58 Sum of left diagonal (Yellow ones) = 31 Difference = 58-31 = 27

📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 14
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn

Solving the challenge of Diagonal Sum Difference with Power Query

Power Query solution 1 for Diagonal Sum Difference, proposed by Rick de Groot:

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45WMlfSUTIyABImQGxoohSrE61kCmQaAzFIztAALGRoBmJbAgkLEAMiZgFVZwQSMlaKjQUA", 
          BinaryEncoding.Base64
        ), 
        Compression.Deflate
      )
    ), 
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]
  ), 
  #"Changed Type" = Table.TransformColumnTypes(
    Source, 
    {
      {"Column1", Int64.Type}, 
      {"Column2", Int64.Type}, 
      {"Column3", Int64.Type}, 
      {"Column4", Int64.Type}
    }
  ), 
  #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type), 
  #"Added Custom" = Table.AddColumn(
    #"Added Index", 
    "DiagonalLeft", 
    each Table.Column(#"Added Index", Table.ColumnNames(#"Changed Type"){[Index]}){[Index]}
  ), 
  Custom1 = Table.AddColumn(
    #"Added Custom", 
    "DiagonalRight", 
    each Table.Column(#"Added Index", List.Reverse(Table.ColumnNames(#"Changed Type")){[Index]}){
      [Index]
    }
  ), 
  #"Calculated Sum" = List.Sum(Custom1[DiagonalLeft]) + List.Sum(Custom1[DiagonalRight])
in
  #"Calculated Sum"

Power Query solution 2 for Diagonal Sum Difference, proposed by Rick de Groot:

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45WMlfSUTIyABImQGxoohSrE61kCmQaAzFIztAALGRoBmJbAgkLEAMiZgFVZwQSMlaKjQUA", 
          BinaryEncoding.Base64
        ), 
        Compression.Deflate
      )
    ), 
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]
  ), 
  #"Changed Type" = Table.TransformColumnTypes(
    Source, 
    {
      {"Column1", Int64.Type}, 
      {"Column2", Int64.Type}, 
      {"Column3", Int64.Type}, 
      {"Column4", Int64.Type}
    }
  ), 
  #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type), 
  #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(
    #"Added Index", 
    {"Index"}, 
    "Attribute", 
    "Value"
  ), 
  #"Added Index1" = Table.AddIndexColumn(#"Unpivoted Other Columns", "Index.1", 0, 1, Int64.Type), 
  #"Inserted Modulo" = Table.AddColumn(
    #"Added Index1", 
    "Modulo", 
    each Number.Mod([Index.1], 4) + 1, 
    type number
  ), 
  #"Filtered Rows" = Table.SelectRows(
    #"Inserted Modulo", 
    each [Index] = [Modulo] or (Table.RowCount(#"Changed Type") + 1 - [Index]) = [Modulo]
  ), 
  #"Calculated Sum" = List.Sum(#"Filtered Rows"[Value])
in
  #"Calculated Sum"

Power Query solution 3 for Diagonal Sum Difference, proposed by Aditya Kumar Darak 🇮🇳:

let
  Source  = Excel.CurrentWorkbook(){[Name = "data"]}[Content], 
  Rows    = Table.RowCount(Source) - 1, 
  Series  = {0 .. Rows}, 
  GreenD  = List.Transform(Series, (f) => Table.ToRows(Source){f}{Rows - f}), 
  YellowD = List.Transform(Series, (f) => Table.ToRows(Source){f}{f}), 
  Result  = List.Sum(GreenD) - List.Sum(YellowD)
in
  Result

Power Query solution 4 for Diagonal Sum Difference, proposed by Aditya Kumar Darak 🇮🇳:

let
  Source  = Excel.CurrentWorkbook(){[Name = "data"]}[Content], 
  Rows    = Table.RowCount(Source) - 1, 
  Series  = {0 .. Rows}, 
  GreenD  = List.Transform(Series, (f) => Record.ToList(Source{f}){Rows - f}), 
  YellowD = List.Transform(Series, (f) => Record.ToList(Source{f}){f}), 
  Result  = List.Sum(GreenD) - List.Sum(YellowD)
in
  Result

Power Query solution 5 for Diagonal Sum Difference, proposed by Venkata Rajesh:

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45WMlfSUTIyABImQGxoohSrE61kCmQaAzFIztAALGRoBmJbAgkLEAMiZgFVZwQSMlaKjQUA", 
          BinaryEncoding.Base64
        ), 
        Compression.Deflate
      )
    ), 
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]
  ), 
  ColumnNames = Table.FromList(Table.ColumnNames(Source)), 
  #"Changed Type" = Table.TransformColumnTypes(
    Source, 
    {
      {"Column1", Int64.Type}, 
      {"Column2", Int64.Type}, 
      {"Column3", Int64.Type}, 
      {"Column4", Int64.Type}
    }
  ), 
  #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type), 
  #"Left Diagonal" = Table.AddColumn(
    #"Added Index", 
    "Left Diagonal", 
    each Record.Field(_, ColumnNames{[Index]}[Column1]), 
    Int64.Type
  ), 
  #"Right Diagonal" = Table.AddColumn(
    #"Left Diagonal", 
    "Right Diagonal", 
    each Record.Field(_, ColumnNames{Table.RowCount(#"Left Diagonal") - 1 - [Index]}[Column1]), 
    Int64.Type
  ), 
  Result = List.Sum(#"Right Diagonal"[Right Diagonal]) - List.Sum(#"Right Diagonal"[Left Diagonal])
in
  Result

Power Query solution 6 for Diagonal Sum Difference, proposed by Sergei Baklan:

let
  Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content], 
  nRows  = Table.RowCount(Source) - 1, 
  n      = {0 .. nRows}
in
  List.Sum(List.Transform(n, each Record.ToList(Table.ToRecords(Source){_}){nRows - _}))
    - List.Sum(List.Transform(n, each Record.ToList(Table.ToRecords(Source){_}){_}))

Solving the challenge of Diagonal Sum Difference with Excel

Excel solution 1 for Diagonal Sum Difference, proposed by Rick Rothstein:

=LET(A,A2:D5,S,SEQUENCE(4),ABS(SUM(INDEX(A,S,S))-SUM(INDEX(A,SEQUENCE(4,,4,-1),S))))

Excel solution 2 for Diagonal Sum Difference, proposed by John V.:

=LET(s,{1;2;3;4},SUM(INDEX(A2:D5,IF({1,0},5-s,s),s)*{1,-1}))

Excel solution 3 for Diagonal Sum Difference, proposed by محمد حلمي:

=LET(a;A2:D5;b;ROW(A1:A4);SUM(INDEX(a;b;6-ROW(A2:A5))-INDEX(a;b;b)))

Excel solution 4 for Diagonal Sum Difference, proposed by محمد حلمي:

=LET(
V,A2:D5,
A,ROW(V)-1,
SUM(
INDEX(V,A,SORT(A,,-1))-INDEX(V,A,A)))

Excel solution 5 for Diagonal Sum Difference, proposed by 🇰🇷 Taeyong Shin:

=SUM( (CHOOSECOLS(A2:D5, {4,3,2,1})-A2:D5)*MUNIT(ROWS(A2:D5)) )

Excel solution 6 for Diagonal Sum Difference, proposed by Julian Poeltl:

=LET(T,A2:D5,R,ROWS(T),SUM(MAKEARRAY(R,R,LAMBDA(A,B,A=R+1-B))*T)-SUM(MAKEARRAY(R,R,LAMBDA(A,B,A=B))*T))

Excel solution 7 for Diagonal Sum Difference, proposed by Aditya Kumar Darak 🇮🇳:

= SUM(SCAN(
 0,
 SEQUENCE(ROWS(data)),
 LAMBDA(
 a,
 b,
 INDEX(data, b, ROWS(data) - b + 1)
 - INDEX(data, b, b))))

Excel solution 8 for Diagonal Sum Difference, proposed by Aditya Kumar Darak 🇮🇳:

= LET(
 range,
 A2:D5,
 rows,
 ROWS(range),
 seq,
 SEQUENCE(rows),
 green,
 INDEX(range, seq, rows - seq + 1),
 yellow,
 INDEX(range, seq, seq),
 SUM(green - yellow))

Excel solution 9 for Diagonal Sum Difference, proposed by Timothée BLIOT:

=LET(N,COUNTA(2:2),Sequ,SEQUENCE(N),
Start1,A2,Start2,OFFSET(Start1,,N-1),
SUM(OFFSET(Start2,Sequ-1,-Sequ+1))-
SUM(OFFSET(Start1,Sequ-1,Sequ-1)))

Excel solution 10 for Diagonal Sum Difference, proposed by Oscar Mendez Roca Farell:

=SUMPRODUCT((ROW(A2:D5)-1=5-COLUMN(A2:D5))-(ROW(A2:D5)-1=COLUMN(A2:D5));A2:D5)

Excel solution 11 for Diagonal Sum Difference, proposed by Abdallah Ally:

=SUM(INDEX(A2:D5,SEQUENCE(ROWS(A2:D5)),SEQUENCE(ROWS(A2:D5),,ROWS(A2:D5),-1)))-SUM(INDEX(A2:D5,SEQUENCE(ROWS(A2:D5)),SEQUENCE(ROWS(A2:D5))))

Excel solution 12 for Diagonal Sum Difference, proposed by Charles Roldan:

=LAMBDA(x, LET(s, SEQUENCE(ROWS(x)), 
f, LAMBDA(t, SUM(INDEX(x, s, SORT(s, , t)))), 
f(-1) - f(1)))(A1:D4)

Excel solution 13 for Diagonal Sum Difference, proposed by Tolga Demirci, PMP, PMI-ACP, MOS-Expert:

=SUM(INDIRECT(MID(ADDRESS(1;COLUMN(A1:D5));2;1)&ROW(A2:D5)))-SUM(INDIRECT(MID(ADDRESS(1;COLUMN(B2:C5));2;1)&ROW(B2:D5)))-SUM(INDIRECT(MID(ADDRESS(1;COLUMN(A3:D4));2;1)&ROW(A3:D4)))+2*(SUM(INDIRECT(MID(ADDRESS(1;COLUMN(B3:C4));2;1)&ROW(B3:C4))))

Excel solution 14 for Diagonal Sum Difference, proposed by CA Raghunath Gundi:

=SUM(INDEX(B241:E244,SORT(SEQUENCE(4),,-1),SEQUENCE(4)))-SUM(INDEX(B241:E244,SEQUENCE(4),SEQUENCE(4)))

Excel solution 15 for Diagonal Sum Difference, proposed by Jardiel Euflázio:

=LET(
a,ROWS(A2:A5),
b,TOCOL(A2:D5),

SUM(
INDEX(b,SEQUENCE(a,,a,a-1)),
-INDEX(b,SEQUENCE(a,,,a+1))
)
)

Without LET:

=SUM(

INDEX(TOCOL(A2:D5),SEQUENCE(ROWS(A2:A5),,ROWS(A2:A5),ROWS(A2:A5)-1)),
-INDEX(TOCOL(A2:D5),SEQUENCE(ROWS(A2:A5),,,ROWS(A2:A5)+1))

)

Excel solution 16 for Diagonal Sum Difference, proposed by Jardiel Euflázio:

=LET(
a,ROWS(A2:A5),
b,SEQUENCE(a,a),

SUMPRODUCT(

A2:E5,
ISNUMBER(MATCH(b,SEQUENCE(a,,a,a-1),0))
-ISNUMBER(MATCH(b,SEQUENCE(a,,,a+1),0))
)
)

Excel solution 17 for Diagonal Sum Difference, proposed by Victor Momoh (MVP, MOS, R.Eng):

=LET(a,SEQUENCE(,4),b,A2:D5,SUM(INDEX(b,a,4-a+1)-INDEX(b,a,a)))

Excel solution 18 for Diagonal Sum Difference, proposed by Abdelrahman Omer, MBA, PMP:

=SUM(SORTBY(A2:D5,SEQUENCE(,4),-1)*MUNIT(4))-SUM(MUNIT(4)*A2:D5)

Excel solution 19 for Diagonal Sum Difference, proposed by Sergei Baklan:

=LET( n, ROWS( data), SUM( data*CHOOSECOLS( MUNIT(n), SEQUENCE(n,,n,-1) ) - MUNIT(n)*data ) )

Excel solution 20 for Diagonal Sum Difference, proposed by Cary Ballard, DML:

=SUM(TAKE(DROP(WRAPROWS(TOCOL(A2:D5), 3), 1, -2), 4)) -
 SUM(TAKE(WRAPROWS(TOCOL(A2:D5), 5), , 1))

Excel solution 21 for Diagonal Sum Difference, proposed by Md Ismail Hosen:

=LAMBDA(InputSquareMatrix,LET(
 DimensionN, ROWS(InputSquareMatrix),
 Trace, SUM(MAKEARRAY(DimensionN,1,LAMBDA(x,y,INDEX(InputSquareMatrix,x,x)))),
 TraceReverse, SUM(MAKEARRAY(DimensionN,1,LAMBDA(x,y,INDEX(InputSquareMatrix,x,DimensionN-x+1)))),
 Result, TraceReverse-Trace,
 Result
 )
)

Excel solution 22 for Diagonal Sum Difference, proposed by Juliano Santos Lima:

=LET(Green;N(INDIRECT(ADDRESS({1;2;3;4};{4;3;2;1})));Yellow;N(INDIRECT(ADDRESS({1;2;3;4};{1;2;3;4})));SUM(Green-Yellow))

The old fashioned way.
=SUM(INDIRECT(ADDRESS({1,2,3,4},{4,3,2,1})),-SUM(INDIRECT(ADDRESS({1,2,3,4},{1,2,3,4}))))

Excel solution 23 for Diagonal Sum Difference, proposed by Juliano Santos Lima:

=SUM(INDEX(A2:D5,SEQUENCE(4),SEQUENCE(4,,4,-1)),-MUNIT(4)*A2:D5)

Excel solution 24 for Diagonal Sum Difference, proposed by Jeff Blakley:

=LET(data, A2:D5,
 dim, ROWS(data),
 revUnit, SEQUENCE(dim)=SEQUENCE(1, dim, dim, -1),
 SUM(data * (revUnit - MUNIT(dim))))

Excel solution 25 for Diagonal Sum Difference, proposed by Nazmul Islam Jobair:

=LET(
_array,A2:D5,
_rows,ROWS(_array),
_seq,SEQUENCE(_rows),
_seqRev,SEQUENCE(_rows,,_rows,-1),
SUM(INDEX(_array,_seq,_seqRev)-INDEX(_array,_seq,_seq))
)

Excel solution 26 for Diagonal Sum Difference, proposed by Charalampos Dimitrakopoulos:

=REDUCE(0,Numbers,
LAMBDA(x,y,
 IF(ROW(y)=COLUMN(y),
 x-y,
 IF(ROW(y)+COLUMN(y)=ROWS(Numbers)+1,
 x+y,
 x)
 )))

Excel solution 27 for Diagonal Sum Difference, proposed by Riley Johnson:

=LET(
 data, $A$2:$D$5,

 _rows, ROWS( data ),
 _cols, COLUMNS( data ),

 diagonal_sum, LAMBDA(_data,_left?,
 LET(
 _row_indx,
 IF( _left?,
 SEQUENCE( _rows ),
 SEQUENCE( _rows, , _rows, -1 )
 ),
 _col_indx, SEQUENCE( _cols ),
&
 _diagonal_array, INDEX( _data, _row_indx, _col_indx ),
 SUM(_diagonal_array)
 )
 ),

 diagonal_sum( data, FALSE ) - diagonal_sum(data, TRUE )

)

Excel solution 28 for Diagonal Sum Difference, proposed by Agah Dikici:

=SUM(IF(ROW(A2:D5)+COLUMN(A2:D5)=6,1,0)*A2:D5)-SUM(IF(ROW(A2:D5)-COLUMN(A2:D5)=1,1,0)*A2:D5)

Excel solution 29 for Diagonal Sum Difference, proposed by Dawid Sebastian Gałęzyka:

=LET(
vArea, A2:D5,
vSize, ROWS(vArea), 
vRows, SEQUENCE(vSize),
vRDiag, INDEX(vArea, vRows, 1+vSize-vRows),
vLDiag, INDEX(vArea, vRows, vRows),
SUM(vRDiag) -SUM(vLDiag)
)

Excel solution 30 for Diagonal Sum Difference, proposed by Dawid Sebastian Gałęzyka:

=LET(
 vArea, A2:D5,
 vSize, MAX( ROWS(vArea), COLUMNS(vArea) ),
 vHMatrix, SEQUENCE(1, vSize, 1, 1),
 vVMatrix, SEQUENCE(vSize, 1, 1, 1),
 SUMPRODUCT(vArea, (vHMatrix=vSize+1-vVMatrix)-(vHMatrix=vVMatrix))
)

Excel solution 31 for Diagonal Sum Difference, proposed by Wojciech B.:

=SUM(INDEX(A2:D5,SEQUENCE(4,1,4,-1),SEQUENCE(4))) - SUM(MUNIT(4)*A2:D5)

Solving the challenge of Diagonal Sum Difference with Python in Excel

Python in Excel solution 1 for Diagonal Sum Difference, proposed by Aditya Kumar Darak 🇮🇳:

data = xl("A2:D5").values
result = sum(np.fliplr(data).diagonal()) - sum(data.diagonal())
result

Solving the challenge of Diagonal Sum Difference with Power Query

Solving the challenge of Diagonal Sum Difference with Excel

Solving the challenge of Diagonal Sum Difference with Python in Excel

Leave a Reply Cancel reply