Using Excel's RANK Function

The RANK function ranks a number compared to other numbers in a list a data. The rank has no relation to the number's position in the list.

For example, in the image here, for the series of values 1, 6, 5, 8, and 10 in rows two and three, the number 5 has a rank of:

  • 4 — as the fourth-largest number in the list (row two)
  • 2 — as the second-smallest number in the list (row three)

Neither ranking matches its position as the third value from either end.

A number's rank will match its position in a list if the list is sorted to match the order of ranking. 

01
of 06

The RANK function's syntax and arguments

Rank Numbers in a List with the RANK Function in Excel 2007
Rank Numbers in a List with the RANK Function in Excel 2007.

Ted French

A function's syntax refers to the way the function is stated and includes the function's name, brackets, comma separators, and arguments.

The syntax for the RANK function is = RANK ( Number, Ref, Order ).

  • Number is number to be ranked. This can be:
    • The actual number (row four in the example)
    • The cell reference  to the location of data (rows two and three)
  • Ref is the array or range of cell references pointing to the list of numbers to use in ranking the Number argument. If non-numeric values are present in the range, they are ignored — for example, in row five, where the number 5 is ranked first because it is the largest of the two numbers in the list.
  • Order is a numeric value that determines whether the Number argument is ranked in ascending or descending order.
    • Descending order (largest to smallest): If Order is 0 (zero) or omitted
    • Ascending order (smallest to largest): If Order is any nonzero value, such as 1.

Note: The data in the Ref does not need to actually be sorted in ascending or descending order for the Number argument value to be ranked in that order.

02
of 06

RANK function example

In the image here, the RANK function is located in cells B7 to E7. It shows the ranking for the number 5 relative to the other numbers in each column.

03
of 06

Entering the RANK function

Since Excel 2010, you cannot enter the RANK function using the dialog box, as you can for most other functions in the program. Instead, you must enter it manually. In this case, you'd type =RANK(C2,A2:E2,0) into cell F2 of the worksheet.

04
of 06

Interpreting the results

The Number argument 5 in rows two to seven has the following rankings:

  • Row 2: fourth, because it is fourth-largest number when the Ref range is ranked in descending order;
  • Row 3: second; because it is second-smallest number when the Ref range is ranked in ascending order;
  • Row 4: fourth, because it is the fourth-largest number when the Ref range is ranked in descending order;
  • Row 5: first, because it is larger of the two numbers when the Ref range is ranked in descending order;
  • Row 6: #N/A, because the number 5 is not in the range A6 to E6.
05
of 06

Ranking duplicate numbers

If a list contains duplicate numbers, the function gives them both the same rank. Subsequent numbers in the list are ranked lower as a result.

For example, row four contains duplicate number 5s, and both are ranked third; the number 1 is ranked fifth, and there is no ​fourth-ranked value.

06
of 06

The RANK function since Excel 2010

In Excel 2010, the RANK function was replaced by:

  • RANK.AVG, which returns the rank of a number in a list of numbers — that is, its size relative to other values in the list. If more than one value has the same rank, the average rank is returned.
  • RANK.EQ, which returns the rank of a number in a list of numbers. Its size is relative to other values in the list; if more than one value has the same rank, the top rank of that set of values is returned.