How to use RANK function in Excel 2013

The RANK function returns the rank of a number, i.e. its position within a list of numbers in Excel 2013. We will also look into RANK.EQ and RANK.AVG functions which are introduced in Excel 2013.

Syntax: RANK (number, ref, [order])

• Number: the number whose rank needs to be found
• Ref: list of numbers from which the rank is intended to be found
• Order: (Optional) specifies whether to rank in ascending or descending order

If order = 0 (or omitted) >>> Excel ranks number as if the list was sorted in descending order

If order is any nonzero value >>> Excel ranks number as if the list was sorted in ascending order

Note: For "Ref", any non-numerical value or reference would be automatically ignored by Excel 

Caution: Make sure to freeze the "Ref" if you want to drag the cells to Rank multiple values

Important note: In case of ties (i.e. in case more than one value has same rank), this function returns same rank and skips subsequent rank.

E.g. If two values have rank 2, then the next entry will have rank as 4 (rank 3 will be skipped and two entries will be given rank 2)

Excel 2013 has two separate functions for RANK

1. RANK.EQ – This returns same rank and skips subsequent rank as mentioned above.
2. RANK.AVG – This takes average of ranks and subsequently gives average rank to all ties (e.g. ties at rank 2 and 3 will give rank as 2.5)