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.

Syntax:

 =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
 =RANK(B3,$B$3:$B$8,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.

Rank Function