RANK function

The rank function in Excel is a useful tool that allows you to determine the relative position or ranking of a specific value in a list of values. It assigns a rank to each value based on its position in the list, helping you analyze and compare data easily.

Syntax

RANK(number, ref, [order])

Arguments

number	The number you want to rank within the list.
ref	The list of values among which you want to rank the ‘number’.
[order]	(Optional) An optional argument that specifies the ranking order. If omitted, it defaults to descending order (the largest value gets a rank of 1).

How to use

The RANK function is straightforward to use. Let’s go through some examples to understand it better.

Suppose you have a list of test scores in cell range A1:A5, and you want to find the rank of a score, say 85, within that list. Here’s how you can use the RANK function:

This formula will return the rank of the value 85 within the list A1:A5.

Now, if you want to specify the ranking order as ascending (the smallest value gets a rank of 1), you can do so like this:

Adding “1” as the third argument indicates ascending order.

Additionally, if you have duplicate values in the list and you want to handle ties, you can use the RANK.EQ and RANK.AVG functions. RANK.EQ assigns the same rank to identical values, while RANK.AVG assigns an average rank to them. Here’s an example:

Or for RANK.AVG:

These functions provide different ways to handle tied values within the list.

Examples

Let’s say you have the following data:

Student	Test Score
John	90
Mary	85
Lisa	90
Mike	78
Susan	95

To find the rank of Mary’s score (85) in descending order:

This will return a rank of 3 for Mary’s score.

For ascending order:

This formula will return a rank of 2 for Mary’s score in ascending order.

Using RANK.EQ:

This formula will return a rank of 1 for the highest score (90), and:

Will also return 1 in ascending order.

Using RANK.AVG:

With RANK.AVG, if two values share the same rank (as with the two scores of 90), it will return an average rank, so it will return 1.5 in this case.

Additional Information

The RANK function in Excel is a handy tool for comparing and analyzing data. It’s important to understand how to use it in different scenarios, especially when dealing with duplicate values and specifying the ranking order. Remember to adjust the function to your specific needs, whether you want to rank in ascending or descending order, and whether you prefer to handle tied values with RANK.EQ or RANK.AVG.