Home » Find Max Points by Rounds

Find Max Points by Rounds

Provide a formula to know the name of the persons who have the highest Points on the basis of Round1+Round2+Round3. The answer in posted dataset would be “John, Shine” as both these have 12 points.

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

Solving the challenge of Find Max Points by Rounds with Power Query

Power Query solution 1 for Find Max Points by Rounds, proposed by Aditya Kumar Darak 🇮🇳: 
let
  Source   = Excel.CurrentWorkbook(){[Name = "data"]}[Content], 
  Total    = Table.AddColumn(Source, "Total", each List.Sum({[Round 1], [Round 2], [Round 3]})), 
  Max      = List.Max(Total[Total]), 
  Filtered = Table.SelectRows(Total, each ([Total] = Max))[Player], 
  Final    = Text.Combine(Filtered, ", ")
in
  Final
Power Query solution 2 for Find Max Points by Rounds, proposed by Bhavya Gupta: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Player"}, "Attribute", "Value"), 
  #"Grouped Rows" = Table.Group(
    #"Unpivoted Other Columns", 
    {"Player"}, 
    {{"Total", each List.Sum([Value]), type number}}
  ), 
  #"Filtered Rows" = Text.Combine(
    Table.SelectRows(#"Grouped Rows", each ([Total] = List.Max(#"Grouped Rows"[Total])))[Player], 
    ", "
  )
in
  #"Filtered Rows"
Power Query solution 3 for Find Max Points by Rounds, proposed by Antriksh Sharma: 
let
  Source      = Table, 
  TopScore    = List.Max(Table.ToList(Source, each List.Sum(List.Skip(_)))), 
  Rec         = Table.CombineColumnsToRecord(Source, "Scores", List.Skip(Table.ColumnNames(Source))), 
  TotalScores = Table.TransformColumns(Rec, {"Scores", each List.Sum(Record.ToList(_)), Int64.Type}), 
  Filter      = Table.SelectRows(TotalScores, each [Scores] = TopScore)
in
  Filter
Power Query solution 4 for Find Max Points by Rounds, proposed by Udit Chatterjee: 
let
  Source = #"Challenge-05", 
  // get total points by players 
  unpivotRoundCols = Table.UnpivotOtherColumns(Source, {"Player"}, "Attribute", "Value"), 
  getTotalPoints = Table.Group(
    unpivotRoundCols, 
    {"Player"}, 
    {{"Total Points", each List.Sum([Value]), type number}}
  ), 
  // get highest point 
  maxPoints = List.Max(Table.Column(getTotalPoints, "Total Points")), 
  // filter table by highest points 
  filteredTable = Table.SelectRows(getTotalPoints, each [Total Points] = maxPoints), 
  getPlayerNames = Table.Column(filteredTable, "Player")
in
  getPlayerNames

Solving the challenge of Find Max Points by Rounds with Excel

Excel solution 1 for Find Max Points by Rounds, proposed by Rick Rothstein: 
=LET(
    S,
    B2:B6+C2:C6+D2:D6,
    TEXTJOIN(
        ", ",
        1,
        IF(
            S=MAX(
                S
            ),
            A2:A6,
            ""
        )
    )
)
Excel solution 2 for Find Max Points by Rounds, proposed by محمد حلمي: 
=LET(
    D;
    MMULT(
        B2:D6;
        {1;
        1;
        1}
    );
    
    TEXTJOIN(
        " ";
        ;
        IF(
            D=MAX(
                D
            );
            A2:A6;
            ""
        )
    )
)
Excel solution 3 for Find Max Points by Rounds, proposed by محمد حلمي: 
=TEXTJOIN(
    " ";
    ;
    IF(
        MMULT(
            B2:D6;
            {1;
            1;
            1}
        )=
        MAX(
            MMULT(
            B2:D6;
            {1;
            1;
            1}
        )
        );
        A2:A6;
        ""
    )
)
Excel solution 4 for Find Max Points by Rounds, proposed by Julian Poeltl: 
=TEXTJOIN(
    ", ",
    ,
    LET(
        T,
        A2:D6,
        S,
        BYROW(
            T,
            LAMBDA(
                A,
                SUM(
                    A
                )
            )
        ),
        FILTER(
            TAKE(
                T,
                ,
                1
            ),
            S=MAX(
                S
            )
        )
    )
)
Excel solution 5 for Find Max Points by Rounds, proposed by Timothée BLIOT: 
=TEXTJOIN(" ",TRUE,FILTER(A2:A6, (B2:B6+C2:C6+D2:D6) = MAX(B2:B6+C2:C6+D2:D6)))
Excel solution 6 for Find Max Points by Rounds, proposed by Bhavya Gupta: 
=LET(a, BYROW(B2:D6,LAMBDA(x, SUM(x))),TEXTJOIN(", ",FALSE,FILTER(A2:A6,a=MAX(a))))
Excel solution 7 for Find Max Points by Rounds, proposed by Charles Roldan: 
=LET(
 Table,
     A1:D6,
    
 Data,
     DROP(
         Table,
          1
     ),
    
 Names,
     TAKE(
         Data,
          ,
          1
     ),
    
 Scores,
     DROP(
         Data,
          ,
          1
     ),
    
 Is,
     LAMBDA(
         f,
          LAMBDA(
              x,
               x = f(
                   x
               )
          )
     ),
    
 FILTER(Names,
     Is(
         MAX
     )(BYROW(
         Scores,
          SUM
     )))
)
Excel solution 8 for Find Max Points by Rounds, proposed by Antriksh Sharma: 
=LET(
    
     Player,
     A2:A6,
    
     Scores,
     B2:D6,
    
     TopScore,
     MAX(
         BYROW(
             B2:D6,
              SUM
         )
     ),
    
     TopPlayers,
     FILTER(
         A2:A6,
          BYROW(
             B2:D6,
              SUM
         ) = TopScore
     ),
    
     VSTACK(
         
          {"Player",
          "Total Score"},
         
          HSTACK(
              TopPlayers,
               EXPAND(
                   TopScore,
                    2,
                    1,
                    TopScore
               )
          )
          
     )
    
)
Excel solution 9 for Find Max Points by Rounds, proposed by Tolga Demirci, PMP, PMI-ACP, MOS-Expert: 
=TEXTJOIN(",";;IF(MAXIFS($E$2:E2;$E$2:E2;FILTER(E2:E6;MAX(E2:E6);""))=MAX(E2:E6);A2:A6;""))
Excel solution 10 for Find Max Points by Rounds, proposed by CA Raghunath Gundi: 
=TAKE(SORTBY(A2:A6,B2:B6+C2:C6+D2:D6,-1),2)
Excel solution 11 for Find Max Points by Rounds, proposed by Jardiel Euflázio: 
=TEXTJOIN
(
", ";;
IF(

SUBTOTAL(9;OFFSET(B1:D1;ROW(INDIRECT("1:"&ROWS(A2:A6)));))=
MAX(SUBTOTAL(9;OFFSET(B1:D1;ROW(INDIRECT("1:"&ROWS(A2:A6)));)));
A2:A6;
""

)
)
Excel solution 12 for Find Max Points by Rounds, proposed by Cary Ballard, DML: 
=FILTER(A2:A6, LARGE(BYROW(B2:D6, SUM), 1) = BYROW(B2:D6, SUM))
Excel solution 13 for Find Max Points by Rounds, proposed by RIJESH T.: 
=LET(p,A68:D72,
s,BYROW(p,LAMBDA(a,SUM(a))),
ARRAYTOTEXT(FILTER(A68:A72,s=MAX(s))))
Excel solution 14 for Find Max Points by Rounds, proposed by Juliano Santos Lima: 
=FILTER(A2:A6,E2:E6=MAX(MMULT(B2:D6,{1,1,1})))
Excel solution 15 for Find Max Points by Rounds, proposed by Yasir Ali Khan: 
=FILTER(A2:A6, MMULT(B2:D6, TRANSPOSE({1,1,1})) = MAX(MMULT(B2:D6, TRANSPOSE({1,1,1}))))
Excel solution 16 for Find Max Points by Rounds, proposed by Nabil Mourad: 
=ARRAYTOTEXT(
    
     LET(
         
          a,
         BYROW(
             B2:D6,
             LAMBDA(
                 x,
                 SUM(
                     x
                 )
             )
         ),
         
          b,
         A2:A6,
         
          FILTER(
              b,
              a=MAX(
                  a
              )
          )
          
     )
    
)
Excel solution 17 for Find Max Points by Rounds, proposed by Muthukumar R.: 
=TEXTJOIN(",",TRUE,IF(E1:E5=MAX(E1:E5),A1:A5,""))
Excel solution 18 for Find Max Points by Rounds, proposed by Hugo Barreto 🇵🇾: 
=UNIRCADENAS(";";
    ;
    SI((B2:B6)+(C2:C6)+(D2:D6)=MAX((B2:B6)+(C2:C6)+(D2:D6));
    A2:A6;
    ""))
Excel solution 19 for Find Max Points by Rounds, proposed by Peter Compton: 
=FILTER(
    A2:A6,
    BYROW(
        B2:D6,
        LAMBDA(
            x,
            SUM(
                x
            )
        )
    )=LARGE(
        BYROW(
        B2:D6,
        LAMBDA(
            x,
            SUM(
                x
            )
        )
    ),
        1
    )
)

Solving the challenge of Find Max Points by Rounds with Python in Excel

Python in Excel solution 1 for Find Max Points by Rounds, proposed by Alejandro Campos: 
result = ', '.join((df := xl("A1:D6", headers=True)).assign(
 Total_Points=df.iloc[:, 1:].sum(1)).
 loc[lambda x: x.Total_Points == x.Total_Points.max(), 'Player'])
Python in Excel solution 2 for Find Max Points by Rounds, proposed by Aditya Kumar Darak 🇮🇳: 
data = xl("A1:D6", headers=True)
data["Total"] = data.iloc[:, 1:].sum(axis=1)
maxPoints = data["Total"].max()
select = data[data["Total"] == maxPoints]
result = ", ".join(select["Player"])
Python in Excel solution 3 for Find Max Points by Rounds, proposed by Antriksh Sharma: 
df = xl("A1:D6", headers= True)
df['Total Score'] = df.apply(lambda x: sum(x.tolist()[1:]), axis = 1)
max_n = df['Total Score'].max()
df[df['Total Score'] == max_n]
 [['Player', 'Total Score']]
 .reset_index(drop = True)

Solving the challenge of Find Max Points by Rounds with DAX

DAX solution 1 for Find Max Points by Rounds, proposed by Zoran Milokanović: 
EVALUATE {CONCATENATEX(TOPN(1, Input, Input[Round 1] + Input[Round 2] + Input[Round 3], DESC), Input[Player], ", ")}

&&&

Leave a Reply