Home » Minimize Absolute Value Difference

Minimize Absolute Value Difference

Find the numbers where absolute difference between Number1 and Number2 in a row is minimum.

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

Solving the challenge of Minimize Absolute Value Difference with Power Query

Power Query solution 1 for Minimize Absolute Value Difference, proposed by Aditya Kumar Darak 🇮🇳: 
let
  Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content], 
  Diff = Table.AddColumn(Source, "Calc", each Number.Abs([Number1] - [Number2])), 
  Result = Table.SelectColumns(
    Table.SelectRows(Diff, each [Difference] = List.Min(Diff[Difference])), 
    Table.ColumnNames(Source)
  )
in
  Result
Power Query solution 2 for Minimize Absolute Value Difference, proposed by Brian Julius: 
let
  Source = Table.TransformColumnTypes(
    Table.FromRows(
      Json.Document(
        Binary.Decompress(
          Binary.FromText(
            "RcyxEcAwCAPAXVRTBGOIPQvn/dcIwkW6P0mnTKhDMF8cSdigo61W3qsdD63tqgXhbefG9t3wZ9x82f+pk5vKzwc=", 
            BinaryEncoding.Base64
          ), 
          Compression.Deflate
        )
      ), 
      let
        _t = ((type nullable text) meta [Serialized.Text = true])
      in
        type table [Number1 = _t, Number2 = _t]
    ), 
    {{"Number1", Int64.Type}, {"Number2", type number}}
  ), 
  AbsVal = Table.AddColumn(
    Source, 
    "Absolute Value", 
    each Number.Abs([Number1] - [Number2]), 
    type number
  ), 
  Filter = Table.RemoveColumns(
    Table.SelectRows(AbsVal, each [Absolute Value] = List.Min(AbsVal[Absolute Value])), 
    "Absolute Value"
  )
in
  Filter
Power Query solution 3 for Minimize Absolute Value Difference, proposed by Matthias Friedmann: 
let
  Source = Excel.CurrentWorkbook(){[Name = "MinAbsDiff"]}[Content], 
  #"Added Custom" = Table.AddColumn(Source, "Custom", each Number.Abs([Number1] - [Number2])), 
  #"Filtered Rows" = Table.SelectRows(
    #"Added Custom", 
    each [Custom] = List.Min(#"Added Custom"[Custom])
  )[[Number1], [Number2]]
in
  #"Filtered Rows"
Power Query solution 4 for Minimize Absolute Value Difference, proposed by Antriksh Sharma: 
let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "RcyxEcAwCAPAXVRTBGOIPQvn/dcIwkW6P0mnTKhDMF8cSdigo61W3qsdD63tqgXhbefG9t3wZ9x82f+pk5vKzwc=", 
          BinaryEncoding.Base64
        ), 
        Compression.Deflate
      )
    ), 
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [Number1 = _t, Number2 = _t]
  ), 
  ChangedType = Table.TransformColumnTypes(
    Source, 
    {{"Number1", Int64.Type}, {"Number2", Int64.Type}}
  ), 
  AddedCustom = Table.AddColumn(
    ChangedType, 
    "Diff", 
    each Number.Abs([Number2] - [Number1]), 
    type number
  ), 
  MinDiff = List.Min(AddedCustom[Diff]), 
  Result = Table.SelectRows(AddedCustom, each _[Diff] = MinDiff)[[Number1], [Number2]]
in
  Result
Power Query solution 5 for Minimize Absolute Value Difference, proposed by Shubham Vashisht: 
let
  Source    = Excel.CurrentWorkbook(){[Name = "Flowdata"]}[Content], 
  Newcolumn = Table.AddColumn(Source, "Custom", each Number.Abs([Number1] - [Number2])), 
  Filter    = Table.SelectRows(Newcolumn, each ([Custom] = List.Min(Newcolumn[Custom]))), 
  Final     = Table.RemoveColumns(Filter, {"Custom"})
in
  Final

Solving the challenge of Minimize Absolute Value Difference with Excel

Excel solution 1 for Minimize Absolute Value Difference, proposed by Rick Rothstein: 
=FILTER(
    A2:B10,
    ABS(
        A2:A10-B2:B10
    )=MIN(
        MAP(
            A2:A10,
            B2:B10,
            LAMBDA(
                x,
                y,
                ABS(
                    x-y
                )
            )
        )
    )
)
Excel solution 2 for Minimize Absolute Value Difference, proposed by John V.: 
=LET(
    d,
    ABS(
        A2:A10-B2:B10
    ),
    FILTER(
        A2:B10,
        d=MIN(
            d
        )
    )
)
Excel solution 3 for Minimize Absolute Value Difference, proposed by محمد حلمي: 
=LET(
    A,
    A2:A10,
    B,
    B2:B10,
    
    C,
    ABS(
        A-B
    ),
    FILTER(
        A2:B10,
        MIN(
            C
        )=C
    )
)
Excel solution 4 for Minimize Absolute Value Difference, proposed by محمد حلمي: 
=C))
Excel solution 5 for Minimize Absolute Value Difference, proposed by محمد حلمي: 
=LET(
    A,
    A2:A10,
    B,
    B2:B10,
    FILTER(
        A2:B10,
        
        MIN(
            ABS(
                A-B
            )
        )=ABS(
                A-B
            )
    )
)
Excel solution 6 for Minimize Absolute Value Difference, proposed by 🇰🇷 Taeyong Shin: 
=LET(
    
     num,
     ABS(
         MMULT(
             A2:B10,
              {1;-1}
         )
     ),
    
     FILTER(
         A2:B10,
          num = MIN(
              num
          )
     )
    
)
Excel solution 7 for Minimize Absolute Value Difference, proposed by Julian Poeltl: 
=LET(
    T,
    A1:B10,
    TT,
    DROP(
        T,
        1
    ),
    D,
    ABS(
        TAKE(
            TT,
            ,
            1
        )-TAKE(
            TT,
            ,
            -1
        )
    ),
    VSTACK(
        TAKE(
        T,
        1
    ),
        FILTER(
            TT,
            D=MIN(
                D
            )
        )
    )
)
Excel solution 8 for Minimize Absolute Value Difference, proposed by Aditya Kumar Darak 🇮🇳: 
= LET(
    
     _n1,
    
     A2:A10,
    
     _n2,
    
     B2:B10,
    
     _diff,
    
     ABS(
         _n1 - _n2
     ),
    
     FILTER(
         HSTACK(
             _n1,
              _n2
         ),
          _diff = MIN(
              _diff
          )
     )
)
Excel solution 9 for Minimize Absolute Value Difference, proposed by Timothée BLIOT: 
=FILTER(
    A2:B10,
    IF(
        ABS(
            A2:A10-B2:B10
        )=MIN(
            ABS(
            A2:A10-B2:B10
        )
        ),
        1,
        0
    )
)
Excel solution 10 for Minimize Absolute Value Difference, proposed by Duy Tùng: 
=LET(
    a,
    A2:B10,
    b,
    ABS(
        MMULT(
            a,
            {1;-1}
        )
    ),
    FILTER(
        a,
        MIN(
            b
        )=b
    )
)
Excel solution 11 for Minimize Absolute Value Difference, proposed by Charles Roldan: 
=LET(
    x,
     A2:B10,
     d,
     ABS(
         MMULT(
             x,
              {1;-1}
         )
     ),
     FILTER(
         x,
          d = MIN(
              d
          )
     )
)
Excel solution 12 for Minimize Absolute Value Difference, proposed by Stefan Olsson: 
=query(
    {A2:B10,
    byrow(
        A2:B10,
        lambda(
            rw,
            max(
                rw
            )-min(
                rw
            )
        )
    )},
    "Select Col1, Col2 order by Col3 limit 2 label Col1 'Number 1', Col2 'Number 2'",
    0
)
Excel solution 13 for Minimize Absolute Value Difference, proposed by Oscar Javier Rosero Jiménez: 
=MIN(
    dif
)))
Excel solution 14 for Minimize Absolute Value Difference, proposed by Victor Momoh (MVP, MOS, R.Eng): 
=FILTER(
    $A$2:$B$10,
    LET(
        a,
        ABS(
            A2:A10-B2:B10
        ),
        a=MIN(
            a
        )
    )
)
Excel solution 15 for Minimize Absolute Value Difference, proposed by Paolo Pozzoli: 
= ROWS, SCEGLI.COL =CHOOSECOLS, SCEGLI.RIGA = CHOOSEROWS, SEQUENZA = SEQUENCE, DATI.ORDINA.PER = SORTBY )

=LET(
    
    nos;
    A2:B10;
    
    righe;
    RIGHE(
        nos
    );
    
    diffs;
    ASS(
        SCEGLI.COL(
            SCEGLI.RIGA(
                nos;
                SEQUENZA(
                    righe
                )
            );
            1
        )-SCEGLI.COL(
            SCEGLI.RIGA(
                nos;
                SEQUENZA(
                    righe
                )
            );
            2
        )
    );
    
    orderedNos;
    DATI.ORDINA.PER(
        nos;
        diffs;
        1
    );
    
    first2Couples;
    SCEGLI.RIGA(
        orderedNos;
        {12}
    );
    
    first2Couples
)
Excel solution 16 for Minimize Absolute Value Difference, proposed by RIJESH T.: 
=LET(
    n,
    A2:B10,
    a,
    ABS(
        TAKE(
            n,
            ,
            1
        )-TAKE(
            n,
            ,
            -1
        )
    ),
    FILTER(
        n,
        a=MIN(
            a
        )
    )
)
Excel solution 17 for Minimize Absolute Value Difference, proposed by Fábio Gatti: 
=LET(
    
     Numbers,
    A2:B10,
    
    
     Diff,
    ABS(
        CHOOSECOLS(
            Numbers,
            1
        )-CHOOSECOLS(
            Numbers,
            2
        )
    ),
    
     MinN,
    MIN(
        Diff
    ),
    
     Result,
    FILTER(
        Numbers,
        Diff=MinN
    ),
    
    
     Result
    
)
Excel solution 18 for Minimize Absolute Value Difference, proposed by Charalampos Dimitrakopoulos: 
=FILTER(
    A2:B10,
    MIN(
        ABS(
            A2:A10-B2:B10
        )
    )=ABS(
            A2:A10-B2:B10
        )
)
Excel solution 19 for Minimize Absolute Value Difference, proposed by Riley Johnson: 
=LET(
    
     a,
     A2:A10,
    
     b,
     B2:B10,
    
    
     diff,
     ABS(
          a - b 
     ),
    
     FILTER(
          HSTACK(
              a,
              b
          ),
          diff = MIN(
              diff
          ),
          ""
     )
    
)

Solving the challenge of Minimize Absolute Value Difference with Python in Excel

Python in Excel solution 1 for Minimize Absolute Value Difference, proposed by Alejandro Campos: 
df = xl("A1:B10", headers=True)
df['AbsDiference'] = abs(df['Number1'] - df['Number2'])
min_diff = df['AbsDiference'].min()
result = df[df['AbsDiference'] == min_diff].reset_index(drop=True).drop(columns='AbsDiference')
result

Solving the challenge of Minimize Absolute Value Difference with DAX

DAX solution 1 for Minimize Absolute Value Difference, proposed by Zoran Milokanović: 
EVALUATE TOPN(1, Input, ABS(Input[Number1] - Input[Number2]), ASC)

Leave a Reply