How to Use the RANK.AVG Function in Google Sheets

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)
RANK.AVG Non-Array Formula

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
RANK.AVG Array Formula

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

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Count Consecutive Duplicates in Excel (Dynamic Array Formula)

Counting consecutive duplicates in Excel is useful for analyzing patterns, detecting repetitive sequences, and...

How to Break RANK Ties Alphabetically in Google Sheets

The RANK function in Google Sheets is commonly used to assign rankings to numerical...

Google Sheets: Highlight an Entire Column If Any Cell Has an Error

Google Sheets allows you to highlight an entire column if any cell has an...

Filter Top N per Category in Excel (Using FILTER & COUNTIFS)

You can use a combination of FILTER and COUNTIFS to filter the top N...

More like this

How to Break RANK Ties Alphabetically in Google Sheets

The RANK function in Google Sheets is commonly used to assign rankings to numerical...

Google Sheets: Highlight an Entire Column If Any Cell Has an Error

Google Sheets allows you to highlight an entire column if any cell has an...

Google Sheets: Extract Top N per Group from Query Aggregation

When working with grouped and aggregated data in Google Sheets, you might need to...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.