# How to Rank Values with Ties in Excel (3 Methods)

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.