Forum Discussion
Solved
Looking for help with a single Dynamic Formula Ranking Points by player and dropping lowest score
Name Finishes Andrew 1,1,2,3,7,8 Randy 2,4,5,5,8,9 Chris 1,1,2,3,7,8 Bill 1,4,6,6,7,9 Jeff 2,3,5,7,8,8 Reed 4,4,6,7,7,9 Doc 3,5,5,6,7,9 Steve 1,3,5,6,8,9 Paul 2,2,3,3,4,9 Points (1st, 2n...
In cell E2 apply this formula:
=LET( d, A2:.C15, tk, TAKE, br, BYROW, ts, TEXTSPLIT, tj, TEXTJOIN, SORT(HSTACK(tk(d,, 1), br(--DROP(ts(tj(";",, br(--ts(tj(";",, CHOOSECOLS(d, 2)), ",", ";"), LAMBDA(a, ARRAYTOTEXT(CHOOSEROWS(SORT(tk(d,, -1),, -1), a))))), ", ", ";"),, -1), SUM)), 2, -1) )Hope this helps.
IlirU
Just to demonstrate it is always possible to make a solution more complicated!
Worksheet formula
= RANKEDλ(Name, Finishes)
// RANKEDλ calls SCOREλ by player then sorts results descending
RANKEDλ = LAMBDA(name, finishes,
LET(
pts, MAP(finishes, SCOREλ),
SORTBY(CONCATENATE(name, ": ", pts), pts,-1)
)
);
// SCOREλ converts finishes into a point score
SCOREλ = LAMBDA(fin,
LET(
places, DROP(REGEXEXTRACT(fin, "\d+", 1),,-1),
SUM(INDEX(points, VALUE(places)))
)
);