Formulas and functions > Functions from A to Z

Note: RANK is supplemented by the new identical function RANK.EQ and the completely new function RANK.AVG, which are available in newer versions of Microsoft Excel (2010 or later).

Syntax:

RANK(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 higher 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(9, A1:A6) returns 1

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

RANK(5, A1:A6) returns 4

RANK(7, A1:A6) returns 2

RANK (rank in a data set)

RANK (rank in a data set)