The Excel Rank function returns the numeric value when compared to a list of numeric values. You can rank lists in ascending or descending order. Let’s first look at the syntax then look at the following example.
=RANK (number, array, [order])
- number – The number to rank.
- array – An array that contains the numbers to rank against.
- order – [optional field] Rank in ascending or descending order.
- Order = “0” Descending order (Largest to smallest)
- Order = “1” Ascending order (Smallest to largest)
- Order omitted = Default order is descending “0
In the above formula, we identify the number as cell B3, “135000”. We want to find the rank of this cell against the array cells, $B$3-$B$8. Finally, we want to use Descending order, so we input “0”. When entered, Excel will return the rank for each sales person as shown.