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
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
18 Replies
An alternative could be Power Query which works in Excel versions starting from Excel 2010. In the attached file you can add data to the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table.
Very cool. I definitely need to step up my Power Query game. I went ahead and deleted the middle columns. Thanks for the solution.
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))) ) );Practically the same as Olufemi7 suggested
=SORT( HSTACK( REGEXEXTRACT(NameFinishes, "\w*",1), MAP( NameFinishes, LAMBDA(v, SUM( CHOOSEROWS( Points, DROP( SORT( --REGEXEXTRACT(v, "\d+",1),,,1 ),,-1) ) ) )) ), 2,-1)I don't understand exactly what you are trying to accomplish, so I think it would be best if you posted a screenshot so that I can understand where your data is located and manually set the results you want to get from that data. So explain your problem better so that I or another contributor to this forum can try to help you.
IlirU
My end goal is just getting a Standings Table to publish to my group the standings of our league.
My True Data looks like this (before I converted to a "challenge" type format. I'm sure there is an even better/shorter dynamic formula to get to desired results here:
Tourney Place Name Points 1st 2nd 3rd 4th 5th 6th 7th 8th 9th 1 1 Andrew 165 1 0 0 0 0 0 0 0 0 1 2 Randy 105 0 1 0 0 0 0 0 0 0 1 3 Chris 75 0 0 1 0 0 0 0 0 0 1 4 Bill 50 0 0 0 1 0 0 0 0 0 1 5 Jeff 35 0 0 0 0 1 0 0 0 0 1 6 Reed 25 0 0 0 0 0 1 0 0 0 1 7 Doc 20 0 0 0 0 0 0 1 0 0 1 8 Steve 15 0 0 0 0 0 0 0 1 0 1 9 Paul 10 0 0 0 0 0 0 0 0 1 2 1 Steve 165 1 0 0 0 0 0 0 0 0 2 2 Paul 105 0 1 0 0 0 0 0 0 0 2 3 Doc 75 0 0 1 0 0 0 0 0 0 2 4 Reed 50 0 0 0 1 0 0 0 0 0 2 5 Randy 35 0 0 0 0 1 0 0 0 0 2 6 Andrew 25 0 0 0 0 0 1 0 0 0 2 7 Chris 20 0 0 0 0 0 0 1 0 0 2 8 Jeff 15 0 0 0 0 0 0 0 1 0 2 9 Bill 10 0 0 0 0 0 0 0 0 1 3 1 Andrew 165 1 0 0 0 0 0 0 0 0 3 2 Chris 105 0 1 0 0 0 0 0 0 0 3 3 Paul 75 0 0 1 0 0 0 0 0 0 3 4 Randy 50 0 0 0 1 0 0 0 0 0 3 5 Doc 35 0 0 0 0 1 0 0 0 0 3 6 Steve 25 0 0 0 0 0 1 0 0 0 3 7 Bill 20 0 0 0 0 0 0 1 0 0 3 8 Jeff 15 0 0 0 0 0 0 0 1 0 3 9 Reed 10 0 0 0 0 0 0 0 0 1 4 1 Chris 165 1 0 0 0 0 0 0 0 0 4 2 Jeff 105 0 1 0 0 0 0 0 0 0 4 3 Steve 75 0 0 1 0 0 0 0 0 0 4 4 Paul 50 0 0 0 1 0 0 0 0 0 4 5 Doc 35 0 0 0 0 1 0 0 0 0 4 6 Bill 25 0 0 0 0 0 1 0 0 0 4 7 Reed 20 0 0 0 0 0 0 1 0 0 4 8 Andrew 15 0 0 0 0 0 0 0 1 0 4 9 Randy 10 0 0 0 0 0 0 0 0 1 5 1 Chris 165 1 0 0 0 0 0 0 0 0 5 2 Andrew 105 0 1 0 0 0 0 0 0 0 5 3 Paul 75 0 0 1 0 0 0 0 0 0 5 4 Reed 50 0 0 0 1 0 0 0 0 0 5 5 Steve 35 0 0 0 0 1 0 0 0 0 5 6 Bill 25 0 0 0 0 0 1 0 0 0 5 7 Jeff 20 0 0 0 0 0 0 1 0 0 5 8 Randy 15 0 0 0 0 0 0 0 1 0 5 9 Doc 10 0 0 0 0 0 0 0 0 1 6 1 Bill 165 1 0 0 0 0 0 0 0 0 6 2 Paul 105 0 1 0 0 0 0 0 0 0 6 3 Jeff 75 0 0 1 0 0 0 0 0 0 6 4 Andrew 50 0 0 0 1 0 0 0 0 0 6 5 Randy 35 0 0 0 0 1 0 0 0 0 6 6 Doc 25 0 0 0 0 0 1 0 0 0 6 7 Reed 20 0 0 0 0 0 0 1 0 0 6 8 Chris 15 0 0 0 0 0 0 0 1 0 6 9 Steve 10 0 0 0 0 0 0 0 0 1 In cell O1 apply below formula:
=LET( place, B2:B55, name, C2:C55, points, D2:D55, dr, DROP(PIVOTBY(name, place, points, SUM,, 0,, 0), 1), dp, DROP(dr,, 1), hs, HSTACK(dr, BYROW(dp, SUM) - BYROW(dp, MIN)), clm, COLUMNS(hs), VSTACK(HSTACK("Name", SEQUENCE(, clm - 2), "Total"), SORT(hs, clm, -1)) )Hope this helps.
Note: Thank you for marking my previous reply as a Valid Answer.
IlirU
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.
