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.

Filter the Bottom 10 Items in a Pivot Table in Google Sheets

This tutorial explains how to filter the bottom 10 items in a Pivot Table...

Hyperlink to Jump to the Last Used Row in Excel

In a vertical range, you can create a hyperlink to jump to the last...

Find the Last Used Row’s Last Value Address in Excel

In a large vertical dataset in Excel, how do you find the cell address...

Find the Last Used Row Number in Excel

When working with large datasets such as sales records, purchase data, or bills of...

More like this

Filter the Bottom 10 Items in a Pivot Table in Google Sheets

This tutorial explains how to filter the bottom 10 items in a Pivot Table...

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

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.