Home » Rank Cities by Percent Change

Rank Cities by Percent Change

List the top 3 cities where sum of %change from 2020 to 2021 and 2021 to 2022 is the highest. When %change is calculated, result should be rounded to 0 decimals as rankings will be decided on the basis of 0 decimal values. %change = (Next_Year – Previous_Year)/Previous_Year Hence for Austin % change from 2020 to 2021 = (94-77)/77 = 22% from 2021 to 2022 = (75-94)/94 = -20% Sum of %change = 22% – 20% = 2%.

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

Solving the challenge of Rank Cities by Percent Change with Power Query

Power Query solution 1 for Rank Cities by Percent Change, proposed by Kris Jaganah: 
let
  A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  B = Table.AddColumn(
    A, 
    "Cal", 
    each [a = Record.ToList(_), b = Number.Round((a{2} / a{1}), 2) + Number.Round((a{3} / a{2}), 2)][
      b
    ]
  ), 
  C = Table.AddRankColumn(B, "Rank", {"Cal", 1}, [RankKind = RankKind.Dense]), 
  D = Table.SelectRows(C[[Rank], [Cities]], each [Rank] < 4)
in
  D
Power Query solution 2 for Rank Cities by Percent Change, proposed by Alejandro Simón 🇵🇦 🇪🇸: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  A = Table.AddColumn(
    Source, 
    "A", 
    each 
      let
        a = _, 
        b = List.Skip(Record.ToList(a)), 
        c = List.Sum(
          List.Transform(
            {1 .. List.Count(b) - 1}, 
            each Number.Round((b{_} - b{_ - 1}) / b{_ - 1}, 2)
          )
        ), 
        d = Number.From(Number.ToText(c, "0.##"))
      in
        d
  ), 
  Sol = Table.SelectRows(
    Table.AddRankColumn(A, "Rank", {{"A", 1}}, [RankKind = RankKind.Dense]), 
    each [Rank] < 4
  )[[Rank], [Cities]]
in
  Sol
Power Query solution 3 for Rank Cities by Percent Change, proposed by Abdallah Ally: 
let
  Source  = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Change  = (x, y) => Number.Round((y - x) * 100 / x), 
  AddCol  = Table.AddColumn(Source, "Change", each Change([2020], [2021]) + Change([2021], [2022])), 
  AddRank = Table.AddRankColumn(AddCol, "Rank", {"Change", 1}, [RankKind = RankKind.Dense]), 
  Result  = Table.SelectRows(AddRank, each [Rank] < 4)[[Rank], [Cities]]
in
  Result
Power Query solution 4 for Rank Cities by Percent Change, proposed by Seokho MOON: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Change = Table.AddColumn(
    Source, 
    "Change", 
    each Number.Round([2021] / [2020], 2) + Number.Round([2022] / [2021], 2) - 2
  ), 
  Rank = Table.AddRankColumn(Change, "Rank", {"Change", 1}, [RankKind = RankKind.Dense])[
    [Rank], 
    [Cities]
  ], 
  Res = Table.SelectRows(Rank, each [Rank] <= 3)
in
  Res
Power Query solution 5 for Rank Cities by Percent Change, proposed by CA Raghunath Gundi: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  ch_1 = Table.AddColumn(Source, "ch_1", each Number.Round([2021] / [2020] - 1, 2), Percentage.Type), 
  ch_2 = Table.AddColumn(ch_1, "ch_2", each Number.Round([2022] / [2021] - 1, 2), Percentage.Type), 
  #"ch_1&2" = Table.AddColumn(
    ch_2, 
    "sum", 
    each Number.Round(List.Sum({[ch_1], [ch_2]}), 2), 
    Percentage.Type
  ), 
  Grp = Table.Group(#"ch_1&2", {"sum"}, {{"Cities", each [Cities]}}), 
  Sort = Table.AddIndexColumn(
    Table.FirstN(Table.Sort(Grp, {{"sum", Order.Descending}}), 3), 
    "Rank", 
    1, 
    1
  ), 
  Result = Table.ExpandListColumn(Sort, "Cities")[[Rank], [Cities]]
in
  Result
Power Query solution 6 for Rank Cities by Percent Change, proposed by Krzysztof Kominiak: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  AddRank = Table.AddColumn(
    Source, 
    "Rank", 
    each [
      a = List.Skip(Record.ToList(_)), 
      b = List.Positions(a), 
      c = List.Accumulate(
        b, 
        {}, 
        (s, c) => s & {try Number.Round((a{c + 1} - a{c}) / a{c}, 2) otherwise null}
      ), 
      d = Number.Round(List.Sum(c), 2)
    ][d]
  )[[Cities], [Rank]], 
  Max3 = List.MaxN(List.Distinct(AddRank[Rank]), 3), 
  FilterRows = Table.SelectRows(AddRank, each List.Contains(Max3, [Rank])), 
  Result = Table.Sort(
    Table.TransformColumns(FilterRows, {"Rank", each List.PositionOf(Max3, _) + 1}), 
    {{"Rank", Order.Ascending}}
  )
in
  Result
Power Query solution 7 for Rank Cities by Percent Change, proposed by Alejandra Horvath CPA, CGA: 
let
  S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  C = Table.AddColumn(
    S, 
    "A", 
    each 
      let
        a  = Number.Round, 
        S1 = a(([2021] - [2020]) / [2020], 1), 
        S2 = a(([2022] - [2021]) / [2021], 1), 
        P  = a(S1 + S2, 1)
      in
        P
  ), 
  R = Table.AddRankColumn(C, "Rank", {{"A", 1}}, [RankKind = RankKind.Dense]), 
  Sol = Table.SelectRows(R, each [Rank] <= 3)[[Rank], [Cities]]
in
  Sol

Solving the challenge of Rank Cities by Percent Change with Excel

Excel solution 1 for Rank Cities by Percent Change, proposed by Bo Rydobon 🇹🇭: 
=LET(x,-BYROW(ROUND(C3:D21/B3:C21,2),SUM),r,MATCH(x,SORT(UNIQUE(x))),SORT(FILTER(HSTACK(r,A3:A21),r<4)))
Excel solution 2 for Rank Cities by Percent Change, proposed by 🇰🇷 Taeyong Shin: 
=LET(
    n,
    -MMULT(
        ROUND(
            C3:D21/B3:C21,
            2
        ),
        {1;1}
    ),
    r,
    XMATCH(
        n,
        GROUPBY(
            n,
            ,
            
        )
    ),
    GROUPBY(
        HSTACK(
            r,
            A3:A21
        ),
        ,
        ,
        ,
        0,
        ,
        r<=3
    )
)
Excel solution 3 for Rank Cities by Percent Change, proposed by Kris Jaganah: 
=LET(a,C3:C21,b,ROUND(a/B3:B21-1,2)+ROUND(D3:D21/a-1,2),c,ROUND(b,2),d,XMATCH(c,SORT(UNIQUE(c),,-1)),VSTACK({"Rank","Cities"},DROP(SORT(FILTER(HSTACK(b,d,A3:A21),d<4),{2,1},{1,-1}),,1)))
Excel solution 4 for Rank Cities by Percent Change, proposed by Julian Poeltl: 
=LET(D,BYROW(B3:D21,LAMBDA(A,ROUND(SUM(ROUND(DROP((A-DROP(A,,1))/A,,-1),2)),2))),S,SORT(HSTACK(A3:A21,D),2),X,DROP(S,,1),V,VSTACK(HSTACK("Rank","Cities"),HSTACK(XMATCH(X,UNIQUE(X)),TAKE(S,,1))),TAKE(V,XMATCH(3,TAKE(V,,1),,-1)))
Excel solution 5 for Rank Cities by Percent Change, proposed by Timothée BLIOT: 
=LET(A,A3:A21,B,B3:B21,C,C3:C21,D,D3:D21,R,LAMBDA(n,ROUND(n,2)), E,R((C-B)/B),F,R((D-C)/C), G,MAP(E+F,LAMBDA(x,SUM(--(x<=UNIQUE(R(E+F)))))), SORT(FILTER(HSTACK(G,A),G<=3)))
Excel solution 6 for Rank Cities by Percent Change, proposed by Duy Tùng: 
=LET(a,BYROW(ROUND(C3:D21/B3:C21,2),SUM),b,MATCH(a,GROUPBY(a,,,,,-1),),GROUPBY(HSTACK(b,A3:A21),,,,0,,b<4))
Excel solution 7 for Rank Cities by Percent Change, proposed by Sunny Baggu: 
=LET(
 _v,
     ROUND(100 * (C3:C21 - B3:B21) / B3:B21,
     0) +
 ROUND(100 * (D3:D21 - C3:C21) / C3:C21,
     0),
    
 _s,
     SORT(
         _v,
          ,
          -1
     ),
    
 _t3,
     TAKE(
         UNIQUE(
             _s
         ),
          3
     ),
    
 _s3,
     SEQUENCE(
         ROWS(
             _t3
         )
     ),
    
 _a,
     SORTBY(
         HSTACK(
             A3:A21,
              _v
         ),
          _v,
          -1
     ),
    
 _b,
     XLOOKUP(
         TAKE(
             _a,
              ,
              -1
         ),
          _t3,
          _s3
     ),
    
 DROP(
     FILTER(
         HSTACK(
             TAKE(
                 _b,
                  ,
                  1
             ),
              _a
         ),
          1 - ISNA(
              _b
          )
     ),
      ,
      -1
 )
)
Excel solution 8 for Rank Cities by Percent Change, proposed by Abdallah Ally: 
WITH CTE1 AS 
(
 SELECT
 Cities,
    
 ROUND(([2021] - [2020]) * 100/ [2020],
     0) + 
 ROUND(([2022] - [2021]) * 100 /[2021],
     0) AS Change
 FROM
 ExcelChallenge615
),
    
CTE2 AS 
(
 SELECT
 Cities,
    
 DENSE_RANK() OVER (ORDER BY Change DESC) AS Rank
 FROM
 CTE1
)
SELECT 
 Rank,
    
 Cities
FROM 
 CTE2
WHERE
 Rank < 4
Excel solution 9 for Rank Cities by Percent Change, proposed by Anshu Bantra: 
=LET(
 data_,
     A3:D21,
    
 chg_20_21_,
     ROUND((INDEX(
         data_,
          ,
          3
     ) - INDEX(
         data_,
          ,
          2
     )) / INDEX(
         data_,
          ,
          2
     ) * 100,
     0),
    
 chg_21_22_,
     ROUND((INDEX(
         data_,
          ,
          4
     ) - INDEX(
         data_,
          ,
          3
     )) / INDEX(
         data_,
          ,
          3
     ) * 100,
     0),
    
 chg_sum_,
     chg_20_21_ + chg_21_22_,
    
 sorted_indices_,
     XMATCH(
         chg_sum_,
          SORT(
              UNIQUE(
                  chg_sum_
              ),
               ,
               -1
          )
     ),
    
 sorted_data_,
     SORT(
         CHOOSECOLS(
             HSTACK(
                 data_,
                  sorted_indices_
             ),
              5,
              1
         ),
          1,
          1
     ),
    
 FILTER(
     sorted_data_,
      INDEX(
          sorted_data_,
           ,
           1
      ) < 4
 )
)
Excel solution 10 for Rank Cities by Percent Change, proposed by Md. Zohurul Islam: 
=LET(cty,A3:A21,a,C3:D21,b,B3:C21,hdr,HSTACK("Rank","Cities"),c,ROUND(a/b,2),d,BYROW(c,SUM),unq,UNIQUE(SORT(d,,-1)),e,XMATCH(d,unq),f,SORTBY(HSTACK(e,cty),e,1),g,FILTER(f,DROP(f,,-1)<=3),h,VSTACK(hdr,g),h)
Excel solution 11 for Rank Cities by Percent Change, proposed by Pieter de B.: 
=LET(a,
    A3:D21,
    c,
    CHOOSECOLS,
    z,
    LAMBDA(x,
    y,
    ROUND((y-x)/x%,
    )),
    n,
    z(
        c(
            a,
            2
        ),
        c(
            a,
            3
        )
    )+z(
        c(
            a,
            3
        ),
        c(
            a,
            4
        )
    ),
    r,
    MAP(
        n,
        LAMBDA(
            m,
            SUM(
                N(
                    UNIQUE(
                        n
                    )>m
                )
            )
        )
    )+1,
    c(
        GROUPBY(
            c(
                a,
                1
            ),
            r,
            SUM,
            ,
            0,
            2,
            r<4
        ),
        2,
        1
    ))
Excel solution 12 for Rank Cities by Percent Change, proposed by Hamidi Hamid: 
=LET(aa,
    A3:A21,
    bb,
    B3:B21,
    cc,
    C3:C21,
    dd,
    D3:D21,
    s,
    SORT(HSTACK(A3:D21,
    ROUND((cc-bb)/bb,
    2)+ROUND((dd-cc)/cc,
    2)),
    5,
    -1),
    b,
    LARGE(
        UNIQUE(
            TAKE(
                s,
                ,
                -1
            )
        ),
        SEQUENCE(
            3
        )
    ),
    g,
    XMATCH(
        TAKE(
                s,
                ,
                -1
            ),
        b,
        1,
        1
    ),
    t,
    HSTACK(
        s,
        g
    ),
    x,
    ROUND((cc-bb)/bb,
    2)+ROUND((dd-cc)/cc,
    2),
    y,
    MAP(
        LARGE(
            UNIQUE(
                x
            ),
            SEQUENCE(
            3
        )
        ),
        LAMBDA(
            a,
            ARRAYTOTEXT(
                FILTER(
                    A3:A21,
                    x>=a
                )
            )
        )
    ),
    z,
    UNIQUE(
        UNIQUE(
            TEXTSPLIT(
                CONCAT(
                    y&", "
                ),
                ,
                ", ",
                1
            ),
            1
        )
    ),
    HSTACK(
        VLOOKUP(
            z,
            t,
            6,
            0
        ),
        z
    ))
Excel solution 13 for Rank Cities by Percent Change, proposed by Asheesh Pahwa: 
=LET(p,
    ROUND(100*(C3:C21-B3:B21)/B3:B21,
    0),
    _p,
    ROUND(100*(D3:D21-C3:C21)/C3:C21,
    0),
    _s,
    p+_p,
    t,
    TAKE(
        SORT(
            UNIQUE(
                _s
            ),
            ,
            -1
        ),
        3
    ),
    s,
    SEQUENCE(
        ROWS(
            t
        )
    ),
    r,
    REDUCE(
        F2:G2,
        s,
        LAMBDA(
            x,
            y,
            VSTACK(
                x,
                IFNA(
                    HSTACK(
                        y,
                        FILTER(
                            A3:A21,
                            _s=INDEX(
                                t,
                                y,
                                
                            )
                        )
                    ),
                    y
                )
            )
        )
    ),
    r)
Excel solution 14 for Rank Cities by Percent Change, proposed by Imam Hambali: 
=LET(
d, ROUND(((C3:C21-B3:B21)/B3:B21)*100,0) + ROUND(((D3:D21-C3:C21)/C3:C21)*100,0),
t, TAKE(SORT(UNIQUE(d),,-1),3),
tt, SORT(FILTER(HSTACK(A3:A21,d), BYROW(d=TRANSPOSE(t),OR)),2,-1),
VSTACK({"Rank","Cities"}, HSTACK(XMATCH(TAKE(tt,,-1),t),TAKE(tt,,1)))
)
Excel solution 15 for Rank Cities by Percent Change, proposed by Ernesto Vega Castillo: 
=LET(
    a,
    -BYROW(
        ROUND(
            C3:D21/B3:C21,
            2
        ),
        SUM
    ),
    b,
    MATCH(
        a,
        SORT(
            UNIQUE(
                a
            )
        )
    ),
    DROP(
        GROUPBY(
            HSTACK(
                b,
                A3:A21
            ),
            b,
            MAX,
            ,
            0,
            1,
            b<=3
        ),
        ,
        -1
    )
)
Excel solution 16 for Rank Cities by Percent Change, proposed by Jorge Alvarez: 
=LET(re;BYROW(C3:E21;LAMBDA(v;
 LET(_v2020;TOMAR(v;1;1);
 _v2021;ELEGIRCOLS(TOMAR(v;1;2);2);
 _v2022;TOMAR(v;1;-1);
 REDONDEAR((_v2021-_v2020)/_v2020;2)+REDONDEAR((_v2022-_v2021)/_v2021;2))));
 or;ORDENAR(re;;-1);
 ori;APILARV("Ordenar";or);
 se;EXCLUIR(SCAN&(0;or<>ori;LAMBDA(a;v;SI(v;a+1;a)));-1);
 APILARH(FILTRAR(se;se<=3);FILTRAR(ORDENARPOR(B3:B21;re;-1);se<=3))
 )

Solving the challenge of Rank Cities by Percent Change with Python

Python solution 1 for Rank Cities by Percent Change, proposed by Konrad Gryczan, PhD: 
import pandas as pd
path = "615 Top 3 Percentage Change Sum.xlsx"
input = pd.read_excel(path, usecols="A:D", skiprows=1, nrows=20).rename(columns=lambda x: x if x == 'Cities' else f"{x}Y")
test = pd.read_excel(path, usecols="F:G", skiprows=1, nrows=6).rename(columns=lambda x: x.split('.')[0])
 .sort_values(["Rank", "Cities"]).reset_index(drop=True)
input['ch1'] = round((input['2021Y'] - input['2020Y']) / input['2020Y'], 2)
input['ch2'] = round((input['2022Y'] - input['2021Y']) / input['2021Y'], 2)
input['cum_ch'] = round(input['ch1'] + input['ch2'], 2)
input['Rank'] = input['cum_ch'].rank(method='dense', ascending=False).astype(int)
input = input.sort_values(by=['Rank', "Cities"])
input = input[['Rank','Cities']].reset_index(drop=True)
input = input[input['Rank'] <= 3]
print(all(input == test)) # True
Python solution 2 for Rank Cities by Percent Change, proposed by Abdallah Ally: 
import pandas as pd
change = lambda x, y: round((y - x) * 100 / x)
file_path = 'Excel_Challenge_615 - Top 3 Percentage Change Sum.xlsx'
df = pd.read_excel(file_path, usecols='A:D', skiprows=1)
# Perform data manipulation
df['Rank'] = (
 df
 .apply(lambda x: change(x[2020], x[2021]) + change(x[2021], x[2022]), axis=1)
 .rank(method='dense', ascending=False)
 .map(int)
)
df = df[['Rank', 'Cities']][df['Rank'] < 4].sort_values(by='Rank', ignore_index=True)
df

Solving the challenge of Rank Cities by Percent Change with Python in Excel

Python in Excel solution 1 for Rank Cities by Percent Change, proposed by Alejandro Campos: 
df = xl("A2:D21", headers=True)
df['Rank'] = (((df[2021] - df[2020]) / df[2020] * 100).round(0) +
 ((df[2022] - df[2021]) / df[2021] * 100).round(0)).
 rank(method='dense', ascending=False).astype(int)
df[df['Rank'] <= 3][['Rank', 'Cities']].sort_values(
 by=['Rank', 'Cities']).reset_index(drop=True)
Python in Excel solution 2 for Rank Cities by Percent Change, proposed by Anshu Bantra: 
df = xl("A2:D21", headers=True)
df['change'] =  round( (df[2021]-df[2020])/df[2020]*100, 0 ) +
 round( (df[2022]-df[2021])/df[2021]*100, 0 )
df['Rank'] = df['change'].rank(method='dense', ascending=False)
df = df.sort_values(by='Rank', ascending=True)
df[df['Rank']<4][['Rank','Cities']].reset_index(drop=True)

Solving the challenge of Rank Cities by Percent Change with R

R solution 1 for Rank Cities by Percent Change, proposed by Konrad Gryczan, PhD: 
library(tidyverse)
library(readxl)
path = "Excel/615 Top 3 Percentage Change Sum.xlsx"
input = read_excel(path, range = "A2:D21")
test = read_excel(path, range = "F2:G8") %>%
 arrange(Rank, Cities)
result = input %>%
 mutate(ch1 = round((`2021` - `2020`)/`2020`,2),
 ch2 = round((`2022` - `2021`)/`2021`,2),
 cum_ch = ch1 + ch2) %>%
 mutate(cum_ch = round(cum_ch,2)) %>%
 mutate(rank = dense_rank(-cum_ch)) %>%
 filter(rank <= 3) %>%
 arrange(rank, Cities) %>%
 select(Rank = rank, Cities)
all.equal(result, test, check.attributes = FALSE)
#> [1] TRUE

&&

Leave a Reply