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
A formula approach that works is to use
=GROUPBY(
name,
HSTACK(points, place),
VSTACK(
HSTACK(SUM, LAMBDA(x, TEXTJOIN(",", , SORT(x)))),
{"Total points", "Finishes"}
),
, 0,-2
)
This is a 'prettied up' version of the above that defines a Lambda function for the calculation.
Worksheet formula
= SUMMARISEλ(name, place)
where
SUMMARISEλ
= LAMBDA(name, place,
LET(
JOINλ, LAMBDA(x, TEXTJOIN(",", , SORT(x))),
header, {"Total points", "Finishes"},
points, INDEX(pointList, place),
GROUPBY(
name,
HSTACK(points, place),
VSTACK(HSTACK(SUM, JOINλ), header),
0, 0,-2
)
)
);