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

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



On May 2, 12:13*am, diana wrote:
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

Try this:

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

Copy down as needed.

--
Biff
Microsoft Excel MVP


"diana" wrote in message
...
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

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