Forum Discussion
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, 2nd, etc)
165
105
75
50
35
25
20
15
10
Expected answer with lowest point dropped
Name Points
Chris 530
Andrew 510
Paul 410
Steve 315
Bill 285
Jeff 250
Randy 240
Doc 190
Reed 165
1 Reply
Hellowillwonka,
Assuming A2:A10 contains Names, B2:B10 contains Finishes stored as text such as 1,1,2,3,7,8 and D2:D10 contains the points for positions 1 to 9 (165,105,75,50,35,25,20,15,10).
Use this dynamic formula
=LET(n,A2:A10,f,B2:B10,p,D2:D10,s,MAP(f,LAMBDA(x,LET(pos,--TEXTSPLIT(x,","),pts,INDEX(p,pos),SUM(DROP(SORT(pts),1))))),SORT(HSTACK(n,s),2,-1))
TEXTSPLIT converts the finish string into an array of positions. INDEX maps each position to the corresponding points. SORT orders the points so the lowest value is first and DROP removes that lowest score. SUM totals the remaining scores. MAP performs the calculation for each player. HSTACK combines names with the totals and SORT ranks the result by points in descending order. The result returns the expected ranking such as Chris 530, Andrew 510, Paul 410, Steve 315, Bill 285, Jeff 250, Randy 240, Doc 190 and Reed 165.
