• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Rank -- rank 1-4 based on total points and personal record

D

D Eckert

New Member
I have a spreadsheet that tracks wrestler's points based on each match. They have three rounds, one match each round. the drop down options for the Win versus Lose determine the point values. Those all work fine.

I have a WIN column that will count the number of Wins and and Lose column that counts Losses
I then have a Personal Record that will compare the two (example 3-0, 2-1) etc.

I have eight teams, 4 in POOL A and 4 in POOL B

I need to Rank 1-4 for each pool based on their total points first, and then, if a tie, look at the personal record.

For example -- please see attached screenshot


As you can see from the screenshot, for the A Pool, there are no ties
18 = First
12 = Second
6 = Third
0 = Fourth

but for the B group, two wrestlers both accumulated 12 points,
however one wrestler had a 3-0 personal records (3 wins, 0 losses) while the other wrestler had a 2-1 PR, (2 wins, 1, loss)
The player with the 3-0 needs to be marked as 2 since they have the better win to loss ration.


I have tried SumProduct

=1+SUMPRODUCT(($P$2:$P$5>P2)*($Q$2:$Q$5>Q2))
(this one works well for ranking, however, does not take into account if the number is negative or positive -- it just looks at True or False)

and

=RANK(Q2,$Q$2:$Q$5) (Yes, I know this one is only looking at one set of data)
Even Rank.AVG would be good if it can then look at the secondary data rather than just give both tied numbers a .5 deliniation

I would really like to not have to have an additional column just to rank a second time in the case of a tie. This is a huge Workbook with multiple spreadsheets due to the amount of data needed.
 

Attachments

  • Wrestler\'s RankExample.JPG
    Wrestler\'s RankExample.JPG
    49.6 KB · Views: 7
Last edited:
It appears that the total matches is always equal for all the participants. If this is the case the number of points + number of (wins X 0.001) would give you a rank to sort by.

In your example 12 points + (3 wins X 0.001) = 12.003
12 points + (2 wins X 0.001) = 12.002

12.003 is the greater number so ranked higher. I'm using 0.001 as it would take 1000 matches before the win loss ratio would look like a point and throw the whole thing off.
 
The max total for points is 18 - 6 for each round

Is there anyway to do this without having to make the value a decimal?
the Coaches use the point values for their own sets of information and tracking and technically, the wrestlers cannot earn partial points.

There is still a possibility that two wrestlers can have 2-1 W-L ratio

Really need to be able to take both sets of criteria into account, not just manipulate the point total.
 
D Eckert said:
The max total for points is 18 - 6 for each round

Is there anyway to do this without having to make the value a decimal?
the Coaches use the point values for their own sets of information and tracking and technically, the wrestlers cannot earn partial points.

There is still a possibility that two wrestlers can have 2-1 W-L ratio

Really need to be able to take both sets of criteria into account, not just manipulate the point total.
Click to expand...

If two or more wrestlers have the same points and W-L ratio then they are ranked the same. I don't understand your issue.

The points are the points and would not change if you made a separate perhaps even hidden column for the ranking equation.
 
If I rank just by Total Points, or just by Personal Record -- I will almost always have a tie.

It is rare to have a tie in both areas, so I need the formula to evaluate both items. if there is a tie after that, that is a manual item that I don't think I can overcome but most often, due to the way wrestler's earn points, there is more likely to be a tie in one of the two categories every time.

If the formula can look at total points first, and then look at personal record to say that wrestler 1 with 12 points and a 3-0 has a higher rank value than wrestler 2 with 12 points and a 2-1 ratio, that would be ideal.
 
Okay, let's simplify this a bit. I want a formula that acts like Fantasy Football league results. Fantasy Football evaluates points and Win to Loss ratio in order to then rank the league members from 1st place on down. This is what I need to do as well.
 
okay, I have been experimenting and I think I am closer.
=(1+SUMPRODUCT(--($R$2:$R$9=R2),--($U$2:$U$9>$U2)))+SUMPRODUCT(--($R$2:$R$9=R2), --($U$2:$U$9>$U2),--($T$2:$T$9>$T2))
So here's a clarifier
Have two sets of ranks I need to get per group of A: 1 through 4 for group A and 1 through 4 for group
B
Rank needs to take into account W-L ratio and points with a tie breaker column of Most Pins 1st and then if still tied, a coin toss, each of these with their own column
with the example above, I am not currently taking into account the Most Pins column Q and the Tcolumn of coin toss currently doesn't change the results.
 
SOLVED -- ended up having to adjust a few columns and formulas but was able to make it work.

used the following for POINTS
=IF(AND($R2="N/A",$T2="Won",$U2="N/A"),(--$P2+(Q2*0.001)+0.001),
IF(AND($R2="N/A",$T2="Loss",$U2="N/A"),(--$P2+(Q2*0.001)),
IF(OR($R2="DSQ",$R2="LEFT",-1,),
IF(AND($R2="N/A",$T2="Won",$U2="Won"),(--$P2+(Q2*0.001)+0.002),(--$P2+(Q2*0.001))))))

then had a tiebreaker column for a Coin Toss
if still a tie, had a secondary Final Tie Breaker column

then Rank
=1+SUMPRODUCT(($W$2:$W$9=$W2)*($S$2:$S$9>$S2))

WORKED WONDERFULLY, and thankfully, this year, NO TIES!


D Eckert said:
I have a spreadsheet that tracks wrestler's points based on each match. They have three rounds, one match each round. the drop down options for the Win versus Lose determine the point values. Those all work fine.

I have a WIN column that will count the number of Wins and and Lose column that counts Losses
I then have a Personal Record that will compare the two (example 3-0, 2-1) etc.

I have eight teams, 4 in POOL A and 4 in POOL B

I need to Rank 1-4 for each pool based on their total points first, and then, if a tie, look at the personal record.

For example -- please see attached screenshot


As you can see from the screenshot, for the A Pool, there are no ties
18 = First
12 = Second
6 = Third
0 = Fourth

but for the B group, two wrestlers both accumulated 12 points,
however one wrestler had a 3-0 personal records (3 wins, 0 losses) while the other wrestler had a 2-1 PR, (2 wins, 1, loss)
The player with the 3-0 needs to be marked as 2 since they have the better win to loss ration.


I have tried SumProduct

=1+SUMPRODUCT(($P$2:$P$5>P2)*($Q$2:$Q$5>Q2))
(this one works well for ranking, however, does not take into account if the number is negative or positive -- it just looks at True or False)

and

=RANK(Q2,$Q$2:$Q$5) (Yes, I know this one is only looking at one set of data)
Even Rank.AVG would be good if it can then look at the secondary data rather than just give both tied numbers a .5 deliniation

I would really like to not have to have an additional column just to rank a second time in the case of a tie. This is a huge Workbook with multiple spreadsheets due to the amount of data needed.
Click to expand...
 
You must log in or register to reply here.
Back
Top