Formulas and functions > Functions from A to Z > RANK.AVG (rank in a data set)

Syntax:

RANK.AVG(Number, Range [, Ascending])

Description:

Returns the rank of a number in a data set. The largest number's rank is 1, the 2nd largest number's rank is 2, etc. If there are duplicate values in the list, these are given the average rank.

Number is the number whose rank you want to determine. If Number does not occur in Range, a #N/A error value is returned.

Range is a range with numeric data – usually a reference to a cell range in which the given numbers are entered.

Ascending (optional) is a logical value that lets you specify whether to use ascending or descending order:

FALSE or omitted: Use descending order (the largest number's rank is 1).

TRUE: Use ascending order (the smallest number's rank is 1).

Example:

If the cells A1:A6 contain 3, 2, 7, 5, 9, 7:

RANK.AVG(9, A1:A6) returns 1

RANK.AVG(9, A1:A6, TRUE) returns 6

RANK.AVG(3, A1:A6) returns 5

RANK.AVG(7, A1:A6) returns 2.5

Note:

The RANK.AVG function supplements together with RANK.EQ the previous RANK function.

RANK.EQ returns the same result as RANK (for duplicate values in the list higher rank).

The RANK.AVG (for duplicate values in the list average rank) is completely new.

Compatibility notes:

Microsoft Excel supports this function only in version 2010 or later. In older versions, the function is unknown.