# Excel BI Challenge Workout 191

— CAN YOU SOLVE THIS - EXCEL CHALLENGE 191 —
(Solutions in any language are also welcome for Excel Challenges)

Rank the players on the basis of Round 1 + Round 2 + Round 3.
The ranks have to be distinct and in case of tie (ex. Shine and Kevin), earlier occurring entry will have higher rank.
Note - 1 is the highest rank.

(Your formula need not be a single formula. You can write multiple formulas to arrive at a solution. Also your formula need not be different from others as long as you have worked out your formula independently)

#excel, #excelchallenge, #powerquerychallenge, #daxchallenge, #m, #dax, #powerbi, #powerquery, #powerpivot, #sheets, #googlesheets, #data, #analytics, #businessintelligence, #finance, #office365, #python, #r

I had to resort to a ridiculous workaround because apparently RANK doesn’t work on calculated arrays in the same manner as the _IFS functions

=LET(A, B2:D6,
B, BYROW(A, LAMBDA(A, SUM(A)))-TAKE(A,,1)/1000,
C, HSTACK(SORT(B,,-1),SEQUENCE(COUNT(B))),
VLOOKUP(B, C, 2, 0))

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Rank = Table.FromColumns({List.Transform(Table.ToRows(Source), each List.Sum(List.Skip(_)))}),
Sort = Table.Sort(Idx,{{"Column1", Order.Descending}}),
in
Sol

Ranking w/ #powerquery. #bitanbit #powerbi

let
Source = Excel.CurrentWorkbook(){[Name="Input"]}[Content],
Solution =
let
n = List.Transform(Table.ToRows(Source), each List.Sum(List.Skip(_))),
i = {1..List.Count(n)},
o = List.Sort([List.Zip](http://list.zip/)({n, i}), {{each _{0}, 1}, {each _{1}, 0}})
in
List.Transform(List.Sort(List.Transform(i, each {_} & o{_-1}), {each _{2}, 0}), each _{0})
in
Solution

Here is one way out in Power Query.

let
Source = Excel.CurrentWorkbook(){[ Name = "data" ]}[Content],
Source,
"Total",
each [
Field = Record.FieldNames ( _ ),
Select = List.Select ( Field, ( f ) => Text.StartsWith ( f, "Round " ) ),
Values = Record.FieldValues ( Record.SelectFields ( _, Select ) ),
Total = List.Sum ( Values )
][Total]
),
Rank  = Table.AddRankColumn ( Total, "Rank", { "Total", 1 }, [ RankKind = RankKind.Ordinal ] ),
Return = Table.AddColumn ( Source, "Rank", each Rank{[ Player = [Player] ]}[Rank] )
in
Return

Ranking alternative w/ #powerquery. #bitanbit #powerbi

let
Source = Excel.CurrentWorkbook(){[Name="Input"]}[Content],
Solution =
let
n = List.Transform(Table.ToRows(Source), each List.Sum(List.Skip(_)))
in
List.Transform(List.Positions(n), (p) => List.PositionOf(List.Reverse(List.Sort(n)), n{p}) + List.Count([List.Select](http://list.select/)(List.FirstN(n, p + 1), each _ = n{p})))
in
Solution

My #powerquery solution:

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
AddSum = Table.AddColumn(Source, "Sum", each List.Sum({[Round 1], [Round 2], [Round 3]}), Int64.Type),
Sort = Table.RemoveColumns( Table.Sort(Rank,{{"Index", Order.Ascending}}), {"Index"})
in
Sort

let
Source = Excel.CurrentWorkbook(){[Name = "tblData"]}[Content],
SortPlayer = Table.Sort(Source, {each List.Sum(List.Skip(Record.FieldValues(_))), 1})[Player],
each List.PositionOf(SortPlayer, [Player]) + 1
),
ChangeType =
let
in
{{name{0}, type text}}
& List.Transform( List.Skip(name), each {_, Int64.Type} )
)
in
ChangeType

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Sorted Rows" = Table.Sort(#"Added Index",{{"Sum", Order.Descending}}),
#"Sorted Rows1" = Table.Sort(#"Added Index1",{{"Index", Order.Ascending}}),
#"Removed Other Columns" = Table.SelectColumns(#"Sorted Rows1",{"Expected Answer"})
in
#"Removed Other Columns"

Here is one way out in Excel

=LET(
_d, B2:D6,
_ttl, BYROW(_d, LAMBDA(a, SUM(a))),
_dup, _ttl - ROW(_d)%%%,
_srt, SORT(_dup, , -1),
_r, XMATCH(_dup, _srt),
_r
)

Here:
d = Data
ttl = Total
dup = A quick solution to make duplicates unique
srt = Sorted duplicates
r = Return

## LinkedIn post byJohn Jairo V.

Hi to all!

Two options:

=LET(f,ROW(B2:D6),b,B2:B6+C2:C6+D2:D6+f%,MMULT(N(b<=TOROW(b)),f^0))

This one modify Bo Rydobon formula:

=LET(b,ROW(B2:D6)%-B2:B6-C2:C6-D2:D6,MATCH(b,SORT(b)))

Blessings!

let
Fonte = Tabela1,
sum = Table.AddColumn(Fonte, "Sum", each List.Sum( List.RemoveFirstN( Record.FieldValues(_),1))),
clas = Table.Sort(sum,{{"Sum", Order.Descending}}),
Ind = Table.AddIndexColumn(clas, "Rank", 1, 1, Int64.Type),
res = Table.Sort(Ind,each List.PositionOf(Fonte[Player],[Player]) )
in
res

## LinkedIn Post by:Oscar Mendez Roca Farell

hi everyone, my proposal solution:

=LET(_d, B2:D6,_f, ROW(_d),_s, BYROW(_d, LAMBDA(r, SUM(r))),_m,_s+MMULT((_s=TOROW(_s))*(_f<=TOROW(_f)),_s^0)/10,MAP(_m,LAMBDA(a,SUM(--(a<=_m)))))

=LET(a,BYROW(B2:D6,LAMBDA(x,SUM(x))),
b,SORT(HSTACK(A2:A6,a),2,-1),
XMATCH(A2:A6,CHOOSECOLS(b,1),0,1))

M-Code

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Rank = let a = List.Transform({0..Table.RowCount(Source)-1}, each _/100-List.Sum(List.Skip(Record.ToList(Source{_})))) in List.Transform(a, each List.PositionOf(List.Sort(a),_)+1)
in
Rank

=LET(_p,A2:A6,XLOOKUP(_p,SORTBY(_p,BYROW(B2:D6,LAMBDA(a,SUM(a))),-1),SEQUENCE(ROWS(_p))))

Formula solution:

=LET(r,B2:D6,
a,BYROW(r,LAMBDA(x,SUM(x))),
s,SEQUENCE(ROWS(r)),
1+MMULT((TOROW(a)>a)+((TOROW(a)=a)*(TOROW(s)<s)),s^0))

Thank you Excel BI

=LET(r, B2:D6, n, BYROW(r, LAMBDA(z, SUM(z))), SCAN(,XMATCH(n, SORT(n,,-1)), LAMBDA(x,y, IF(y=x, y+1,y))))

=LET(
s,B2:B6+C2:C6+D2:D6,
l,SEQUENCE(ROWS(s)),
XMATCH(s,LARGE(s,l))+
MAP(l,s,LAMBDA(a,b,SUM(--(TAKE(s,a)=b))))-1)