There are three ways to handle ties when ranking a list of values in Excel:
Method 1: Assign Highest Rank to Equal Values
=RANK(B2,$B$2:$B$11)
This particular formula will assign the same rank to equal values in the range B2:B11.
For example, if two values are both tied for the highest value in the list then both values will receive a rank of 1.
Method 2: Assign Average Rank to Equal Values
=RANK.AVG(B2,$B$2:$B$11)
This particular formula will assign the average rank to equal values in the range B2:B11.
For example, if two values are both tied for the highest value in the list then both values will receive a rank of 1.5 since the average of their rankings would be: (1 + 2) /2 = 1.5.
Method 3: Assign Highest Rank to Value that Appears First
=RANK(B2,$B$2:$B$11)+COUNTIF(B$2:B2,B2)-1
This particular formula will assign the highest rank to the value that appears first between two equal values in the range B2:B11.
For example, if two values are both tied for the highest value in the list then the value that appears first will receive a rank of 1 and the value that appears second will receive a value of 2.
The following example shows how to use each of these methods in practice.
Example: How to Rank Values with Ties in Excel
Suppose we have the following dataset in Excel that shows the exam scores received by various students in some class:
Suppose we would like to rank the exam scores of each student, assigning a value of 1 to the highest score and 10 to the lowest score.
We can type the following formulas into cells C2, D2 and E2 to apply different ranking methods:
- C2: =RANK(B2,$B$2:$B$11)
- D2: =RANK.AVG(B2, $B$2:$B$11)
- E2: =RANK(B2,$B$2:$B$11)+COUNTIF(B$2:B2,B2)-1
We can then click and drag these formulas down to each remaining cell in each column:
Notice that there are two values that are tied for the highest score: Doug and Eric both received an exam score of 95.
Here is how each ranking method handled this tie:
Method 1: RANK
This method simply assigned a rank of 1 to both of these highest values.
Method 2: RANK.AVG
This method assigned a rank of 1.5 to both of these highest values, which represented the average rank if each of these values received their own individual rank.
For example, if Doug received a rank of 1 and Eric received a rank of 2 then the average between these two would be 1.5.
Method 3: RANK + COUNTIF
This method assigned a rank of 1 to the first highest value that occurred and a 2 to the highest value that occurred next.
Since Doug appeared first in the dataset he received a rank of 1 and Eric then received a rank of 2.
When ranking values in your own dataset, feel free to choose whichever method you prefer best.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
How to Calculate Percentile Rank in Excel
How to Rank Values by Group in Excel
How to Rank Items by Multiple Criteria in Excel
