Sort rows when total equal
Posted by Sandy on August 29, 2001 2:39 AM
I have a spreadsheet, in essence a league table.
Col 1 has 42 golfers names, each can earn points by being placed in the top 6 of each competition. (6 points for 1st, 1 point for 6th) Therefore there are 42 rows.
At present there are 20 columns and a totals column in descending order. At present I sort the data on the totals column obviously with the highest number of points total at the top.
My problems is that a number of players have equal points and I want a formula that will sort those with equal points in descending order depending on the placing a competitor has gained
By that I mean that if a number of competitors have an equal total score they should be placed in order of the greater number of higher placing they have, i.e. player 1 has 6,0,4,3,0,0,3,3, player 2 has 0,6,0,4,4,3,5,0. Therefore the player with the second placing of 5 points (player 2 in this case) he should take precedence over player 1.
Hopefully this might help Eric and IML who helped earlier thanks to you both
Posted by Mark W. on August 29, 2001 7:52 AM
Sandy, in your example...
Player 1 has 19 points while Player 2 has 22.
Doesn't appear that you'll have a problem sorting
these.
Posted by Mark W. on August 29, 2001 8:35 AM
Consider an additional sort based on a weighted score...
Suppose you have 4 players with the following points...
{"Player 1",6,0,4,3,0,0,3,3
;"Player 2",0,6,0,4,4,3,5,0
;"Player 3",6,2,3,1,5,3,4,4
;"Player 4",2,6,0,6,4,6,1,3}
Their total points would be {19;22;28;28}; however,
Player 4 has clearly outperformed Player 3.
Why not make a descending sort of the point sums
your primary sort and then use the results of...
=SUM(FREQUENCY($B1:$I1,{6,5,4,3,2,1,0})*10^{7;6;5;4;3;2;1;0})
...as the descending sort key of your secondary
sort.
{"Player 4",2,6,0,6,4,6,1,3,28,30111110
;"Player 3",6,2,3,1,5,3,4,4,28,11221100
;"Player 2",0,6,0,4,4,3,5,0,22,11210030
;"Player 1",6,0,4,3,0,0,3,3,19,10130030}
From this weighted score you can clearly see that
Player 4 has (3) 6s; (0) 5s; (1) 4; (1) 3; (1) 2;
(1) 1; and (0) other.
Posted by Sandy on August 29, 2001 11:22 AM
Re: Consider an additional sort based on a weighted score...
Mark, Thanks I'll try thi and see what happens
Posted by Sandy on August 29, 2001 11:22 AM
Re: Sandy, in your example...
Posted by Sandy on August 29, 2001 11:24 AM
Re: Sandy, in your example...
Mark, U noticed but your second reply took into account the addition error. Thanks
