Tom’s Tutorials For Excel: Ranking With Ties

Tom’s Tutorials For Excel: Ranking With Ties

You may have come across situations where you need to rank a series of numbers, such as a list of test scores or elapsed times in a footrace. The RANK function does a good job of indicating the scores in order of first, second, third, and so on. But what if there are ties among the scores being ranked?

In the following pictures, the table in range A4:C13 represents a static list of players, scores, and a scale range of 1 to 10 to accommodate an available ranking slot for each of the 10 players.

In this first picture, range E4:E13 is selected to call attention to the formula in cell E4 that uses a simple RANK function of the scores from range B4:B13. The formula is =RANK(B4,$B$4:$B$13).

Notice that the formula does not return a 3 or a 9 because there are two sets of ties among the scores: Tom and Sophia both scored 245, while Mike and Nick both scored 138.


You can break a ranked tie in either an ascending or descending fashion. Suppose the scoring table is an average of three rounds of golf where the lowest score is the best score and would be ranked #1. In that case, Bob would be ranked #1 because he had the best (lowest) golf score as shown in the next picture with the formula =RANK(B4,$B$4:$B$13,1)+COUNTIF($B$4:B4,B4)-1


If the scores were from a bowling match, where the highest score is the winner and deserving of the #1 rank, then Angelina’s score of 256 would be the winner. In the next picture, that formula is:
=RANK(B4,$B$4:$B$13,0)+COUNTIF($B$4:B4,B4)-1


The table highlighted in blue shows how to display the name of the person, instead of the ranking, to help identify the players according to their rank.

The following picture shows the names where a raw rank score exists. Because there is no rank returned for #3 or #9, an error will occur with ties, using the formula
=INDEX($A$4:$E$13,MATCH(C4,$E$4:$E$13,0),1)


To avoid errors, you can refer to the ascending or descending rankings in the yellow section, such as this formula for ascending names…
=INDEX($A$4:$F$13,MATCH(C4,$F$4:$F$13,0),1)


…and this formula for descending names:
=INDEX($A$4:$G$13,MATCH(C4,$G$4:$G$13,0),1)