# How to Rank Values with Ties in Google Sheets

There are three ways to handle ties when ranking a list of values in Google Sheets:

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.

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.

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.

The following example shows how to use each of these methods in practice.

## Example: How to Rank Values with Ties in Google Sheets

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.