Table of Contents
To rank values with ties in Google Sheets, you can use the RANK function. This function allows you to assign a rank to each value in a specified range, taking into account ties by using the “average” or “min” options. The “average” option will assign an average rank to tied values, while the “min” option will assign the lowest rank to tied values. This can be useful for organizing data in a spreadsheet and determining the relative positions of values. Additionally, you can also use the SORT function to sort the data in ascending or descending order before applying the RANK function. Overall, the RANK function in Google Sheets provides a simple and efficient way to rank values with ties accurately.
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
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: