Scoring Rank with Tie Breaker

[bobdo]

Help! I need to break a tie once but not twice.

A B C D E F G H I
J K L M N
1 Total Points Won 6 9 8 10 6 5 9 7 6
8 6 7 8
2 Tie Breaker Points Won 3 5 5 7 4 4 5 4 3
4 3 4 5
I then use the ranking formula (=rank B1,B$1:N$1,0) to get:
3 Scoring Rank 9 2 4 1 9 13 2 7
9 4 9 7 4
Now I need a tie breaker formula to get me this result:
4 Rank after Tie Breaker 10 2 4 1 9 13 2 7 10
6 10 7 4

Can someone help me with this?

 

[Pete_UK]

I would suggest that you use row 4 to represent the points with tie-break,
i.e.. in B4 put this formula:

=IF(COUNTIF($B$1:$N$1,B1)>1,B1+B2/10,B1)

and copy this across, then in B5 you can have this rank formula:

=RANK(B4,$B4:$N4,0)

This will give you the ranks you state in your test data. Note that the tie
break scores are divided by 10, so as not to influence the main points total
too much, but you could divide by MAX($B2:$N2) if your numbers are larger
than 10.

Hope this helps.

Pete

 

[MX9]

RankP+RankS/10

I would concur with Pete's method as fully accurate and the most compact. Another method, just for fun and a little less compact, is based on the fact that you already have rows for rankings by Primary score (call it RankP) and by Secondary score (RankS).

In that case =RankP+RankS/10 gives the same tie-breaking result as Pete's method and for the same reason. It leaves the primary ranking intact (in the form of the base number), and it inverts the secondary rankings by dividing them by 10 such that the higher/worse the secondary rank, the greater the amount following the decimal point.

 

[bobdo]

Pete, it worked. Thanks so much. Sorry about the double post. My first
time and it just disappeared and I thought I had to do the whole thing over.

 

[Pete_UK]

You're welcome - thanks for feeding back.

Pete