Returns the rank of a number in a list of numbers. The rank of a number is its size relative to other values in a list. (If you were to sort the list, the rank of the number would be its position.)
Syntax
RANK(number,ref,order)
Number is the number whose rank you want to find.
Ref is an array of, or a reference to, a list of numbers. Nonnumeric values in ref are ignored.
Order is a number specifying how to rank number.
- If order is 0 (zero) or omitted, the number is ranked as if ref were a list sorted in descending order.
- If order is any nonzero value, the number is ranked as if ref were a list sorted in ascending order.
Remarks
RANK gives duplicate numbers the same rank. However, the presence of duplicate numbers affects the ranks of subsequent numbers. For example, in a list of integers, if the number 10 appears twice and has a rank of 5, then 11 would have a rank of 7 (no number would have a rank of 6).
Example
The example may be easier to understand if you copy it to a blank spreadsheet.
How?- Create a blank spreadsheet.
- Select the example in the Help topic.
Selecting an example from Help
- Press CTRL+C.
- In the spreadsheet, select cell A1, and press CTRL+V.
- To switch between viewing the formula that returns the result and the result in the cell, select the cell and press F2 and then ENTER, or click Commands and Options on the spreadsheet toolbar, click the Formula tab, and look in the Formula in active cell (active cell) box.
| Data | |
|---|---|
| 7 | |
| 3.5 | |
| 3.5 | |
| 1 | |
| 2 | |
| Formula | Description (Result) |
| =RANK(A3,A2:A6,1) | Rank of 3.5 in the list above (3) |
| =RANK(A2,A2:A6,1) | Rank of 7 in the list above (5) |