Ties using Rank Function

[diana]

Is there a way to break ties when you use the Rank function in excel? I have
a spreadsheet that takes scores and ranks them in order 1-10. But if two
people have the same score it shows the same rank for both. Is there a
function to correct this using a specific score? We would use quiz as the
tie breaker.
Column A Column B Column C
Quiz Score Total Score Rank
80 100 2
85 100 2
90 120 1

 

[Pete_UK]

You can combine the Total Score with the Quz Score (divided by 100 in
this example) using a formula like this in C2:

=B2 + A2/100

This will give you:

Column A Column B Column C
Quiz Score Total Score Tie-score
80 100 100.80
85 100 100.85
90 120 120.90

Then you can apply your rank formula to column C instead of column B.

Hope this helps.

Pete

 

[T. Valko]

Try this:

=RANK(B2,B$2:B$6)+SUMPRODUCT(--(B2=B$2:B$6),--(A2<A$2:A$6))

Copy down as needed.

 

[Bernd P]

Hello,

And if you would replace RANK by another SUMPRODUCT as shown at:
http://www.sulprobil.com/html/sorting.html
then this solution would also work for strings (not necessary here, as
it seems).

Regards,
Bernd