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

 

[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