### Rank Numbers by Numerical Value in Excel

The RANK function ranks the size of 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 above, for the series of values

1, 6, 5, 8, 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.

### The RANK Function's Syntax and Arguments

A function's syntax refers to the layout of the function and includes the function's name, brackets, comma separators, and arguments.

The syntax for the RANK function is:

*= RANK ( Number, Ref, Order )*

**Number** - the number to be ranked. This can be:

- the actual number - row four above;
- the cell reference to the location of data - rows two and three.

**Ref** - 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 - row five above, where the number 5 is ranked first because it is the largest of the two numbers in the list.

**Order** - 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.

### RANK Function Example

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

### Entering the RANK Function

Since Excel 2010, the RANK function *cannot *be entered using the function's dialog box, like most other functions in the program.

To enter the function it must be entered manually - such as

*=RANK(C2,A2:E2,0)*

into cell F2 of the worksheet.

### 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 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 located in the range A6 to E6.

### Ranking Duplicate Numbers

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

For example, row four contains duplicate number 5's, both are ranked third, while the number one is ranked fifth - there is no fourth ranked value.

### Rank Function since Excel 2010

In Excel 2010, the RANK function was replaced by the:

*RANK.AVG* - Returns the rank of a number in a list of numbers: 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.