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:

This 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, they are given the higher rank.

Number is the number whose rank is to be determined. If Number does not occur in Range, the function returns a #N/A error value.

Range is a range with numerical 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, the following applies:

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)