The RANK Function: More Than a Bad Smell
The adjective “Rank” means “having a foul or offensive smell.” (As in “the rank air of death after the battle”) and “very bad” (as in “the rank incompetence of the South African Post Office”). But Excel uses it as a noun, in the sense of “the position in an ordered list”.
The RANK function returns the order of a number in a list of numbers. You do not have to sort the list but, if you were to sort it, the rank of the number would be its position. RANK does not work with text.
Syntax: RANK(number, ref, [order])
Arguments:
- Number (required): The cell reference of the number the rank of which you want to find.
- Ref (required): An array of, or a reference to, a list of numbers. Non-numeric values in ref are ignored. The list can be in any order.
- Order (optional): A number specifying how to rank number. If order is 0 (zero) or omitted, Excel ranks number as if ref were sorted in descending order, largest with lowest rank. If order is any nonzero value, Excel ranks number as if ref were sorted in ascending order.
1. Notes
If “number” does not appear in the list “ref”, RANK and its siblings below return #N/A.
RANK gives duplicate numbers the same rank. The duplicate numbers do not affect the ranks of later numbers. For example, say we have a list of integers, and the number 4 appears twice and has a rank of 3 in ascending order. Then, if 6 is the next number, it would have a rank of 5. No number would have a rank of 4.
For some purposes, you might want a definition of rank that averages ties. In the previous example, you might want a rank of 3.5 for the two numbers 4. In this case, use the new function RANK.AVG (below) instead. The next number, 6, would still have a rank of 5.
Besides the RANK function, there are two new functions the names of which are clearer. Although RANK is still available, it may be better to use the new ones. RANK itself may not be available in future versions of Excel.
2. The New Functions RANK.EQ and RANK.AVG
RANK.EQ(number, ref, [order]) returns the rank of a number in a list of numbers in descending or ascending order using an integer rank for duplicates. This is exactly what RANK (above) does, but RANK.EQ may be more accurate in some circumstances. Its arguments are exactly the same as for RANK above.
RANK.AVG(number, ref, [order]) returns the rank of a number in a list of numbers in descending or ascending order using an average rank for duplicates. Its arguments are exactly the same as for RANK above.
3. Examples
In this example, the reference list of numbers is A2:A7, also named “Ref”. The number 4 occurs twice. The list does not need to be sorted: RANK will treat it as if it is sorted.
In cells C2:C4 (formulas documented in D2:D4), we show the formulas RANK, RANK.EQ and RANK.AVG. We do not use the optional “order” argument; hence the formulas look at the list in descending order, i.e., 7, 6, 4, 4, 2, 1. RANK and RANK.EQ give the same result of 3. RANK.AVG averages 3 and 4 to give 3.5.
In cells C5:C7 we demonstrate the formulas RANK, RANK.EQ and RANK.AVG using the optional “order” argument (=1). Hence the formulas look at the list in ascending order. All three formulas give the same result of 5. This comes directly after ranks 3 or 3.5. Because of the tie, there is no rank of 4 for this data.
4. Practical Applications
RANK and its siblings are particularly useful in that you do not need to sort the list. This makes it easy to find the rank of any given number that is in the list.
There are many applications. One example is finding where a given expenditure ranks in a list of expenditures. Another is showing where the mark of a given student ranks in those of the whole class.
In contrast, you may want to find which score, expenditure, etc. ranks highest (number 1), or in a given position (2, 3, etc.). Here, use the formula LARGE(array, position) instead.