RANK.AVG (RANK AVG) is a Statistical function that can return the rank of a given value, within the specified array of values. If there are values that share the same rank, the average rank of that set of values will be returned. In this guide, we’re going to show you how to use the RANK.AVG function and also throw in some tips and error handling methods.

Supported versions

  • Excel 2010 and newer versions

RANK AVG Function Syntax

RANK.AVG(number, ref, [order])

Arguments

number The number for which you want to find the rank.
ref An array of, or a reference to a list of numbers to rank against.
[order]

Optional. The order of the ranking.

  • 0: Descending order (default)
  • 1: Ascending order

RANK AVG Function Examples

RANK.AVG function returns the mathematical placement of a number in a given data set. The order of ranking can be controlled by a third, optional argument. If you would like the largest number to come first, use zero (0) for the optional argument. Otherwise, using one (1) ensures that the smallest number will come first. Let’s see both scenarios in examples.

Descending order

In the following example, you will see the largest number in the data set (9) gets the rank 1.5. Since there are two 9 values, RANK.AVG function returns 1.5 for each ((1 + 2) / 2). The counter continues from 3.

=RANK.AVG($B7,$B$7:$B$15,0)

Excel RANK.AVG Function 01

Ascending Order

Enter 1 to [order] argument to get rankings in ascending order. This time, the largest values (9) shares the average of the last place, 5.5.

=RANK.AVG($B20,$B$7:$B$15,1)

Excel RANK.AVG Function 02

Download Workbook

Tips and Remarks for the RANK AVG Function

  • RANK.AVG (RANK AVG) function is the updated version of the RANK function and has been released with 2010 version.
  • RANK.AVG function returns the average rank if there are multiple values that share the same rank. Use the RANK.EQ function instead to return the higher rank.
  • The function ignores non-numerical values entered for the ref argument.

Errors

  • If number is not existed in ref, RANK.AVG function returns #N/A error value.