Excel: RANK, RANK.EQ & RANK.AVG Functions

RANK / RANK.EQ

RANK function in Excel returns the rank of a numeric value when compared to a list of other numeric values. RANK can rank values from largest to smallest as well as smallest to largest. Use RANK 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. RANK function will assign duplicate values the same rank. For example, if a certain value has a rank of 5, and there are two instances of the value in the data, the RANK function will assign both instances a rank of 5. The next rank assigned will be 7, and no value will be assigned a rank of 6. If tied ranks are a problem, one workaround is to employ a tie-breaking strategy.

Arguments of RANK / RANK.EQ Function are:

number - The number to rank.

ref - An array that contains the numbers to rank against.

order - [optional] Rank ascending or descending. Default is zero means ascending.

RANK.AVG

RANK.AVG function in Excel returns the rank of a number against a list of other other numeric values. When values contain duplicates, the RANK.AVG function will assign an average rank to each set of duplicates. It is not necessary to sort the values in the list before using RANK.AVG. RANK.AVG has same arguments like RANK / RANK.EQ function.