Excel Tips – RANK Function

The RANK function returns the rank of a number within a given cell rage. RANK function is useful when you want to display a rank for numeric values in a list. It is not necessary to sort the values in the list before using RANK.

 The syntax is very simple: 

= RANK (number, array, [order])

number – the number to rank

array – the range of cells that contains the numbers to rank against

order – This field is optional. Whether to rank in descending or ascending order

If order is 0 (zero) or omitted, number is ranked in descending order.

If order is 1 (one) value, the number is ranked in ascending order.

In the event of a tie (for example, the list contains duplicates), RANK gives duplicate numbers the same rank using the lower rank value. 

In this example, the total spend with Supplier 3 and Supplier 5 are the same ($16.4 Million). Thus, both suppliers are ranked #2. No supplier is ranked #3. The next supplier in the list is ranked #4. 

Jiazhi Ma

Information, Analytics and Systems, UCOP