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:

Google Sheets rank with ties

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.

Additional Resources

The following tutorials explain how to perform other common tasks in Google Sheets:

How to Calculate Percentile Rank in Google Sheets
How to Use a RANK IF Formula in Google Sheets
How to Rank Items by Multiple Columns in Google Sheets

Leave a Reply

Your email address will not be published. Required fields are marked *