Home » Best and Worst Rank Changes

Best and Worst Rank Changes

— This week will be FIFA World Cup week. All challenges will be related to FIFA World Cup only for this week. — Given are ELO ranking for Nov-21 and Nov-22 for FIFA teams. Find the countries which had the best and worst improvement in rankings. Morocco improved by 24 ranks which was the best and Belgium and Ecuador had worst improvement by -5 ranks.

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

Solving the challenge of Best and Worst Rank Changes with Power Query

Power Query solution 1 for Best and Worst Rank Changes, proposed by Kris Jaganah: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  #"Changed Type" = Table.TransformColumnTypes(
    Source, 
    {{"Nov-22 Rank", Int64.Type}, {"Team", type text}, {"Nov-21 Rank", Int64.Type}}
  ), 
  #"Added Custom" = Table.AddColumn(
    #"Changed Type", 
    "Custom", 
    each [#"Nov-21 Rank"] - [#"Nov-22 Rank"]
  ), 
  #"Added Conditional Column" = Table.AddColumn(
    #"Added Custom", 
    "Custom.1", 
    each if [Custom] > 0 then "Best" else if [Custom] < 0 then "Worst" else null
  ), 
  #"Removed Columns" = Table.RemoveColumns(
    #"Added Conditional Column", 
    {"Nov-21 Rank", "Nov-22 Rank"}
  ), 
  #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns", {"Custom.1", "Team", "Custom"}), 
  #"Filtered Rows" = Table.SelectRows(#"Reordered Columns", each ([Custom] = - 5 or [Custom] = 24)), 
  #"Sorted Rows" = Table.Sort(#"Filtered Rows", {{"Custom", Order.Descending}})
in
  #"Sorted Rows"
Power Query solution 2 for Best and Worst Rank Changes, proposed by Luan Rodrigues: 
let
  Fonte = Data, 
  a = Table.AddColumn(Fonte, "Personalizar", each [#"Nov-21 Rank"] - [#"Nov-22 Rank"]), 
  b = Table.AddColumn(a, "Personalizar.1", each 0), 
  g = Table.Group(
    b, 
    {"Personalizar.1"}, 
    {
      {"Contagem", each List.MaxN(_[Personalizar], 1){0}}, 
      {"Min", each List.MinN(_[Personalizar], 1){0}}, 
      {"tab", each _}
    }
  ), 
  e = Table.ExpandTableColumn(g, "tab", {"Team", "Personalizar"}, {"Team", "Improved by"}), 
  Result = Table.Sort(
    Table.SelectRows(
      Table.AddColumn(
        e, 
        "Category", 
        each 
          if [Contagem] = [Improved by] then
            "Best"
          else if [Improved by] = [Min] then
            "Worst"
          else
            false
      ), 
      each [Category] <> false
    ), 
    {"Improved by", Order.Descending}
  )[[Category], [Team], [Improved by]]
in
  Result
Power Query solution 3 for Best and Worst Rank Changes, proposed by Brian Julius: 
let
  Source = Table.TransformColumnTypes(
    ImproveRaw, 
    {{"Nov-22 Rank", Int64.Type}, {"Nov-21 Rank", Int64.Type}}
  ), 
  Improvement = Table.RemoveColumns(
    Table.AddColumn(Source, "Improved by", each [#"Nov-21 Rank"] - [#"Nov-22 Rank"]), 
    {"Nov-22 Rank", "Nov-21 Rank"}
  ), 
  Max = List.Max(Improvement[Improved by]), 
  Min = List.Min(Improvement[Improved by]), 
  Category = Table.AddColumn(
    Improvement, 
    "Category", 
    each if [Improved by] = Min then "Worst" else if [Improved by] = Max then "Best" else "Mid"
  ), 
  Filter = Table.SelectRows(Category, each ([Category] <> "Mid")), 
  ReorderSort = Table.Sort(
    Table.ReorderColumns(Filter, {"Category", "Team", "Improved by"}), 
    {{"Category", Order.Ascending}, {"Team", Order.Ascending}}
  )
in
  ReorderSort
Power Query solution 4 for Best and Worst Rank Changes, proposed by Bhavya Gupta: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Improvement = Table.AddColumn(Source, "Improved By", each [#"Nov-21 Rank"] - [#"Nov-22 Rank"])[
    [Team], 
    [Improved By]
  ], 
  Grouped = Table.Group(Improvement, {"Improved By"}, {{"Team", each [Team]}})[
    [Team], 
    [Improved By]
  ], 
  Sorted = Table.ToRows(Table.Sort(Grouped, {{"Improved By", Order.Descending}})), 
  Custom = Table.FromRecords(
    List.Transform(
      List.Split(List.Combine({{"Best"}, List.First(Sorted), {"Worst"}, List.Last(Sorted)}), 3), 
      each Record.FromList(_, {"Category", "Team", "Improved By"})
    )
  ), 
  ExpectedOutput = Table.ExpandListColumn(Custom, "Team")
in
  ExpectedOutput
Power Query solution 5 for Best and Worst Rank Changes, proposed by Victor Wang: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Recs = List.Transform(
    Table.ToRecords(Source), 
    each [Team = [Team]] & [r = [#"Nov-21 Rank"] - [#"Nov-22 Rank"]]
  ), 
  Finish = 
    let
      min = List.Min(Recs, 0, each [r])[r], 
      max = List.Max(Recs, 0, each [r])[r]
    in
      Table.RenameColumns(
        Table.FromRecords(
          List.Transform(List.Select(Recs, each [r] = max), each [Category = "Best"] & _)
            & List.Transform(List.Select(Recs, each [r] = min), each [Category = "Worst"] & _)
        ), 
        {"r", "Improved by"}
      )
in
  Finish
Power Query solution 6 for Best and Worst Rank Changes, proposed by Venkata Rajesh: 
let
  Source = Data, 
  ImprovedBy = Table.AddColumn(
    Source, 
    "Improved by", 
    each [#"Nov-21 Rank"] - [#"Nov-22 Rank"], 
    Int64.Type
  ), 
  Category = Table.AddColumn(
    ImprovedBy, 
    "Category", 
    each 
      if [Improved by] = List.Max(#"ImprovedBy"[Improved by]) then
        "Best"
      else if [Improved by] = List.Min(#"ImprovedBy"[Improved by]) then
        "Worst"
      else
        null, 
    Text.Type
  ), 
  #"Filtered Rows" = Table.SelectRows(Category, each ([Category] <> null))[
    [Category], 
    [Team], 
    [Improved by]
  ], 
  #"Sorted Rows" = Table.Sort(
    #"Filtered Rows", 
    {{"Category", Order.Ascending}, {"Team", Order.Ascending}}
  )
in
  #"Sorted Rows"
Power Query solution 7 for Best and Worst Rank Changes, proposed by Mahmoud Bani Asadi: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Subtract = Table.AddColumn(
    Source, 
    "Improved By", 
    each [#"Nov-21 Rank"] - [#"Nov-22 Rank"], 
    type number
  ), 
  MinMax = Table.Combine(
    {Table.MaxN(Subtract, "Improved By", each [Improved By] = List.Max(Subtract[Improved By]))}
      & {Table.MinN(Subtract, "Improved By", each [Improved By] = List.Min(Subtract[Improved By]))}
  ), 
  Category = Table.AddColumn(
    MinMax, 
    "Category", 
    each if [Improved By] = List.Max(Subtract[Improved By]) then "Best" else "Worst"
  )[[Category], [Team], [Improved By]]
in
  Category
Power Query solution 8 for Best and Worst Rank Changes, proposed by Deron Huskey: 
let
  Source = Excel.CurrentWorkbook(){[Name = "ELORankings"]}[Content], 
  DefaultChange = Table.TransformColumnTypes(
    Source, 
    {{"Nov-22 Rank", Int64.Type}, {"Team", type text}, {"Nov-21 Rank", Int64.Type}}
  ), 
  AddImprovedBy = Table.AddColumn(
    DefaultChange, 
    "Improved By", 
    each [#"Nov-21 Rank"] - [#"Nov-22 Rank"]
  ), 
  ChangeImprovedByToWholeNumber = Table.TransformColumnTypes(
    AddImprovedBy, 
    {{"Improved By", Int64.Type}}
  ), 
  AddCategory = Table.AddColumn(
    ChangeImprovedByToWholeNumber, 
    "Category", 
    each 
      if [Improved By] = List.Min(ChangeImprovedByToWholeNumber[Improved By]) then
        "Worst"
      else if [Improved By] = List.Max(ChangeImprovedByToWholeNumber[Improved By]) then
        "Best"
      else
        null
  ), 
  DropOtherCols = Table.SelectColumns(AddCategory, {"Category", "Team", "Improved By"}), 
  ExcludeNulls = Table.SelectRows(DropOtherCols, each ([Category] <> null)), 
  SortByCategoryTeam = Table.Sort(
    ExcludeNulls, 
    {{"Category", Order.Ascending}, {"Team", Order.Ascending}}
  )
in
  SortByCategoryTeam
Power Query solution 9 for Best and Worst Rank Changes, proposed by Emil M.: 
let
 Source = Excel.Workbook(File.Contents("C:DownloadsElo Ratings.xlsx"), null, true), 
 Sheet2_Sheet = Source{[Item = "Sheet2", Kind = "Sheet"]}[Data], 
 #"Removed Other Columns" = Table.SelectColumns(Sheet2_Sheet, {"Column3", "Column2", "Column1"}), 
 #"Promoted Headers" = Table.PromoteHeaders(#"Removed Other Columns", [PromoteAllScalars = true]), 
 Change = Table.AddColumn(
 #"Promoted Headers", 
 "Improved By", 
 each [#"Nov-21 Rank"] - [#"Nov-22 Rank"], 
 type number
 ), 
 #"Result" = Table.Sort(
 Table.SelectRows(
 Change, 
 each (
 if [Improved By] = List.Min(Change[Improved By]) then
 "Min"
 else if [Improved By] = List.Max(Change[Improved By]) then
 "Max"
 else
 null
 )
 <> null
 ), 
 {{"Improved By", Order.Descending}}
 )
in
 #"Result"

Solving the challenge of Best and Worst Rank Changes with Excel

Excel solution 1 for Best and Worst Rank Changes, proposed by Rick Rothstein: 
=LET(d,
    C2:C21-A2:A21,
    m,
    MAX(
        d
    ),
    n,
    MIN(
        d
    ),
    SORT(FILTER(HSTACK(
        IF(
            d=m,
            "Best",
            "Worst"
        ),
        B2:B21,
        d
    ),
    m*(d=m)+n*(d=n)),
    1))

Exploded View
-----------------------------------------------
=LET(
    
     diff,
     C2:C21 - A2:A21,
    
     maximum,
     MAX(
         diff
     ),
    
     minimum,
     MIN(
         diff
     ),
    
     SORT(
         
          FILTER(
              
               HSTACK(
                   IF(
                       diff = maximum,
                        "Best",
                        "Worst"
                   ),
                    B2:B21,
                    diff
               ),
              
               IF(
                   diff = maximum,
                    maximum,
                    IF(
                        diff = minimum,
                         minimum
                    )
               )
               
          ),
         
          1
          
     )
    
)
Excel solution 2 for Best and Worst Rank Changes, proposed by John V.: 
=LET(t,B2:B21,d,C2:C21-A2:A21,VSTACK(FILTER(HSTACK(MID("Best"&t,{1,5},{4,99}),d),d=MAX(d)),FILTER(HSTACK(MID("Worst"&t,{1,6},{5,99}),d),d=MIN(d))))

► The "short" one (shortening Rick Rothstein formula):
=LET(d,C2:C21-A2:A21,c,d=MAX(d),SORT(FILTER(HSTACK(IF(c,"Best","Worst"),B2:B21,d),c+(d=MIN(d)))))
Excel solution 3 for Best and Worst Rank Changes, proposed by 🇰🇷 Taeyong Shin: 
=LET(
    d,
    C2:C21-A2:A21,
    F,
    LAMBDA(
        x,
        fn,
        FILTER(
            CHOOSE(
                {1,
                2,
                3},
                x,
                B2:B21,
                d
            ),
            d=fn(
                d
            )
        )
    ),
    VSTACK(
        F(
            "Best",
            MAX
        ),
        F(
            "Worst",
            MIN
        )
    )
)
Excel solution 4 for Best and Worst Rank Changes, proposed by Kris Jaganah: 
=LET(
    a,
    HSTACK(
        IF(
            C2:C21-A2:A21>0,
            "Better",
            "Worst"
        ),
        B2:B21,
        C2:C21-A2:A21
    ),
    b,
    CHOOSECOLS(
        a,
        3
    ),
    VSTACK(
        FILTER(
            a,
            b=LARGE(
                b,
                1
            )
        ),
        FILTER(
            a,
            b=SMALL(
                b,
                1
            )
        )
    )
)
Excel solution 5 for Best and Worst Rank Changes, proposed by Kris Jaganah: 
=LET(
    a,
    A2:A21,
    b,
    B2:B21,
    c,
    C2:C21,
    d,
    HSTACK(
        b,
        c-a
    ),
    e,
    HSTACK(
        IF(
            CHOOSECOLS(
                d,
                2
            )>0,
            "Best",
            "Worst"
        ),
        d
    ),
    f,
    FILTER(
        e,
        CHOOSECOLS(
            e,
            3
        )=LARGE(
            CHOOSECOLS(
            e,
            3
        ),
            1
        )
    ),
    g,
    FILTER(
        e,
        CHOOSECOLS(
            e,
            3
        )=SMALL(
            CHOOSECOLS(
            e,
            3
        ),
            1
        )
    ),
    VSTACK(
        f,
        g
    )
)
Excel solution 6 for Best and Worst Rank Changes, proposed by Aditya Kumar Darak 🇮🇳: 
=LET(
    
     _d,
     A2:C21,
    
     _cr,
     INDEX(
         _d,
          ,
          1
     ),
    
     _pr,
     INDEX(
         _d,
          ,
          3
     ),
    
     _t,
     INDEX(
         _d,
          ,
          2
     ),
    
     _s,
     _pr - _cr,
    
     _e,
     LAMBDA(
         a,
          b,
          IFNA(
              FILTER(
                  HSTACK(
                      b,
                       _t,
                       _s
                  ),
                   _s = a
              ),
               b
          )
     ),
    
     _b,
     _e(
         MAX(
             _s
         ),
          "Best"
     ),
    
     _w,
     _e(
         MIN(
             _s
         ),
          "Worst"
     ),
    
     _r,
     VSTACK(
         _b,
          _w
     ),
    
     _r
    
)
Excel solution 7 for Best and Worst Rank Changes, proposed by Timothée BLIOT: 
=LET(Teams,B2:B21,Improvements,C2:C21-A2:A21,
Ordered,SORT(HSTACK(Teams,Improvements),2,-1),
BestWorst,FILTER(Ordered,--(MAX(INDEX(Ordered,,2))=INDEX(Ordered,,2))+--(MIN(INDEX(Ordered,,2))=INDEX(Ordered,,2))),
HSTACK(IF(INDEX(BestWorst,,2)=MAX(INDEX(BestWorst,,2)),"Max","Worst"),BestWorst))
Excel solution 8 for Best and Worst Rank Changes, proposed by Charles Roldan: 
=LET(
Team, B2:B21, 
ΔR, C2:C21 - A2:A21,
Tag, XLOOKUP(ΔR, VSTACK(MAX(ΔR), MIN(ΔR)), {"Best";"Worst"}, ""),
SORT(FILTER(HSTACK(Tag, Team, ΔR), LEN(Tag)), 3, -1))
Excel solution 9 for Best and Worst Rank Changes, proposed by Owen Price: 
=LET(
    d,
    $A$2:$C$21,
    
    diff,
    INDEX(
        d,
        ,
        3
    )-INDEX(
        d,
        ,
        1
    ),
    
    min,
    MIN(
        diff
    ),
    
    max,
    MAX(
        diff
    ),
    
    label,
    IFS(
        diff=min,
        "Worst",
        diff=max,
        "Best",
        TRUE,
        ""
    ),
    
    filt,
    SORT(
        FILTER(
            HSTACK(
                label,
                INDEX(
                    d,
                    ,
                    2
                ),
                diff
            ),
            label<>""
        ),
        3,
        -1
    ),
    
    VSTACK(
        {"Category",
        "Team",
        "Improved by"},
        filt
    )
)
Excel solution 10 for Best and Worst Rank Changes, proposed by Stefan Olsson: 
={
SORTN(
    {B2:B21,
     C2:C21-A2:A21},
     1,
    3,
    2,
    FALSE
);
SORTN(
    {B2:B21,
     C2:C21-A2:A21},
     1,
    3,
    2,
    TRUE
)
Excel &solution 11 for Best and Worst Rank Changes, proposed by Victor Momoh (MVP, MOS, R.Eng): 
=LET(
    a,
    C2:C21-A2:A21,
     
    b,
    HSTACK(
        B2:B21,
        a
    ),
     
    c,
    "Best",
     
    d,
    "Worst",
     
    VSTACK(
        IFNA(
            HSTACK(
                c,
                FILTER(
                    b,
                    a=MAX(
                        a
                    )
                )
            ),
            c
        ),
        IFNA(
            HSTACK(
                d,
                FILTER(
                    b,
                    a=MIN(
                        a
                    )
                )
            ),
            d
        )
    )
)
Excel solution 12 for Best and Worst Rank Changes, proposed by Paolo Pozzoli: 
=LET(teams;B2:B21;
diff;C2:C21-A2:A21;
hstack;STACK.ORIZ(teams;diff);
output;STACK.VERT(SE.NON.DISP.(STACK.ORIZ("Best";FILTRO(hstack;SCEGLI.COL(hstack;2)=MAX(diff)));"Best");SE.NON.DISP.(STACK.ORIZ("Worst";FILTRO(hstack;SCEGLI.COL(hstack;2)=MIN(diff)));"Worst"));
output)

Solving the challenge of Best and Worst Rank Changes with DAX

DAX solution 1 for Best and Worst Rank Changes, proposed by Zoran Milokanović: 
EVALUATE
SELECTCOLUMNS(
 FILTER(
 ADDCOLUMNS(Input, 
 "I", Input[Nov-21 Rank] - Input[Nov-22 Rank],
 "T", RANKX(Input, Input[Nov-21 Rank] - Input[Nov-22 Rank], , DESC, Dense),
 "B", RANKX(Input, Input[Nov-21 Rank] - Input[Nov-22 Rank], , ASC, Dense)
 ),
 OR([T] = 1, [B] = 1)
 ),
 "Category", IF([T] = 1, "Best", "Worst"),
 Input[Team],
 "Improved by", [I]
)
ORDER BY
 [Category], Input[Team]

Solving the challenge of Best and Worst Rank Changes with SQL

SQL solution 1 for Best and Worst Rank Changes, proposed by Zoran Milokanović: 
SELECT
 F.CATEGORY
, F.TEAM
,F.IMPROVED_BY
FROM
(
 SELECT
 DECODE(SIGN(D.NOV_21_RANK - D.NOV_22_RANK), -1, 'Worst', 'Best') AS CATEGORY
 , D.TEAM
 ,D.NOV_21_RANK - D.NOV_22_RANK AS IMPROVED_BY
 ,RANK() OVER (PARTITION BY 
 DECODE(SIGN(D.NOV_21_RANK - D.NOV_22_RANK), -1, 'Worst', 'Best')
 ORDER BY ABS(D.NOV_21_RANK - D.NOV_22_RANK) DESC) AS TOP_N
 FROM DATA D
) F
WHERE
 F.TOP_N = 1
ORDER BY
 1, 2
;

Leave a Reply