You can use the RANK.AVG function to rank values in a given range in Google Sheets. If there is a tie, the function assigns them the average rank.
With this function, you can specify whether you want to assign rank 1 to the smallest value or the largest value. Additionally, the RANK.AVG function can be combined with the ARRAYFORMULA function to rank all values in the list at once.
For example, =RANK.AVG(A1, A1:A) will return the rank of the value in cell A1 within column A. =ARRAYFORMULA(IFNA(RANK.AVG(A:A, A:A))) will return the rank for all values in column A in one go!
RANK.AVG Function: Syntax and Arguments
Syntax:
RANK.AVG(value, data, [is_ascending])- value: The value or values (when using ARRAYFORMULA) whose rank will be returned.
- data: The array or range containing the values to rank.
- is_ascending: Specifies TRUE if you want to assign rank 1 to the lowest value in the range; otherwise, FALSE (default) will assign rank 1 to the largest value.
Examples
In the following example, I have student names in column A and their marks in column B. Since the formula contains a header row with field labels, we will use the range B2:B for the rank calculation.
To get the rank of the first student in the range, you can use the following RANK.AVG formula in cell C2, if you want to assign rank #1 to the highest mark in the range:
=RANK.AVG(B2, $B$2:$B)
Alternatively, use the following formula to assign rank #1 to the lowest mark in the range:
=RANK.AVG(B2, $B$2:$B, TRUE)To get the rank for all students, simply drag the formula down or use the ARRAYFORMULA as follows:
=ARRAYFORMULA(IFNA(RANK.AVG(B2:B, B2:B))) // descending order
=ARRAYFORMULA(IFNA(RANK.AVG(B2:B, B2:B, TRUE))) // ascending order
In addition to the ARRAYFORMULA, I have included the IFNA function with the RANK.AVG formula. This removes #N/A errors in empty rows returned by the RANK.AVG function. This is particularly useful when you use a range for the value part of the function.
Resources
- How to Use the RANK Function in Google Sheets
- How to Use the RANK.EQ Function in Google Sheets
- How to Rank Without Ties in Google Sheets
- Ranking a Non-Existing Number in Google Sheets Data
- Rank Without Duplicates in Google Sheets
- How to Rank Group Wise in Google Sheets in Sorted or Unsorted Groups
- Top 10 Ranking Without Duplicate Names in Google Sheets
- Compare and Highlight Up and Down in Ranking in Google Sheets
- Find the Rank of an Item in Each Column in Google Sheets
- Highlight Top 10 Ranks in Single or Each Column in Google Sheets
- How to Rank Data by Alphabetical Order in Google Sheets
- How to Rank Text Uniquely in Google Sheets
