Rank Numbers by Numerical Value With Excel's RANK Function

Close-Up Of Numbers On Blackboard

Getty Images / Marco Guidi / EyeEm

 

The RANK function ranks a number compared to other numbers in a given data set. The rank itself has no relation to the number's position in the list. For an example, in the image shown within this article, 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; however, a number's rank will match its position in a list if the list is sorted to match the order of ranking.

While the RANK function is currently compatible with all version's of Excel, Microsoft is phasing it out in favor of RANK.AVG and RANK.EQ.

RANK.AVG 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 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.

01
of 02

RANK Function's Syntax and Arguments

Screenshot of Excel ranking the number 5 with a dataset

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:

  • Number is number to be ranked. This can bet the actual number (row four in the example) or 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. Setting the Order to 0 or omitting it will rank in descending order, while any nonzero value will rank in ascending order.

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 02

Enter RANK Functions in Excel

Screenshot of Excel with varying Ranking examples

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 the following into cell F2 of the worksheet.

=RANK(C2,A2:E2,0)

This simple formula will reference cell C2 as the number to be ranked (first argument), cells A2 to E2 as the range, and sort in a descending order (third argument).

Note the image above for each formula and how it operates. The Number argument 5 in rows two to seven have the following rankings:

  • Row 2: fourth, because it is the fourth-largest number when the Ref range is ranked in descending order;
  • Row 3: second; because it is the 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.

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.