— 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.
Post answers in Comment.
(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)
Download Practice File - https://lnkd.in/d97nW4i9
#excel, #excelchallenge, #powerquerychallenge, #daxchallenge, #m, #dax, #powerbi, #powerquery, #powerpivot, #sheets, #googlesheets, #data, #analytics, #businessintelligence, #finance, #office365, #python, #r
Excel BI’s LinkedIn Post
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))
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],
Total = Table.AddColumn (
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
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
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!
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)))))
