Table of Contents
To rank values with ties in Google Sheets, you can use the RANK function. This function assigns a rank to each value in a selected range, taking into account any ties and assigning the same rank to tied values. For example, if there are three values tied for first place, they will all be assigned a rank of 1, and the next value will be assigned a rank of 4. This allows for a fair and accurate ranking of 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
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: