In this article, we’ll learn how to rank in Excel with the RANK() function. “Ranking” is used to determine the relative position of a number in a list of numbers relative to the others in the list.
There are three functions in Excel to rank data:
- RANK
- RANK.EQ
- RANK.AVG.
In this article, we will learn how to use these three functions to rank our data.
Recommended read: How to merge cells in Excel?
Setting up the dataset
Let us consider the marks of 10 students in a class to be used for ranking:
1. Using the RANK() function to Rank in Excel
The RANK function determines a number’s position in a list of numbers. A number’s rank is its size in comparison to other values in a list. Duplicate numbers are assigned the same rank.
Syntax: RANK(number,ref,[order])
- number: Required. The number whose rank you’re looking for.
- ref: Required. A list of numbers in an array or as a reference.
- order: Optional. A number that specifies how a number should be ranked. The list is sorted in descending order if the order is 0 (zero) or omitted. The list is sorted in ascending order if the order is any nonzero number.
Follow these steps to use the RANK function on our dataset:
- Go to cell C2, type
=RANK(B2,$B$2:$B$11)and press Enter to generate the rank.
- Copy the formula for the entire list by dragging down the fill handle.
2. Using RANK.EQ function
The RANK.EQ function was introduced in Excel 2010 as an upgraded version of the RANK function. The rank of a number in a list of numbers is returned by this function. A number’s rank is its size in comparison to other values in a list. EQ stands for “equal,” as it gives duplicate numbers the same rank.
Syntax: RANK.EQ(number,ref,[order])
The RANK.EQ function has the following arguments:
- number: Required. The number whose rank you’re looking for.
- ref: Required. A list of numbers in an array or as a reference.
- order: Optional. A number that specifies how a number should be ranked. The list is sorted in descending order if the order is 0 (zero) or omitted. The list is sorted in ascending order if the order is any nonzero number.
Follow these steps to use the RANK.EQ function on the dataset:
- Go to cell C2, type =RANK.EQ(B2,$B$2:$B$11) and press Enter to generate the rank.
- Copy the formula for the entire list by dragging down the fill handle.
3. Using RANK.AVG function
The RANK.AVG returns a number’s rank in a list of numbers, or its size in comparison to other values in the list. It assigns the duplicate numbers an average rank. Syntax: RANK.AVG(number,ref,[order])
The RANK.AVG function has the following arguments:
- number: Required. The number whose rank you’re looking for.
- ref: Required. A list of numbers in an array or as a reference.
- order: Optional. A number that specifies how a number should be ranked. The list is sorted in descending order if the order is 0 (zero) or omitted. The list is sorted in ascending order if the order is any nonzero number.
Follow these steps to use the RANK.AVG function on the dataset:
- Go to cell C2, type =RANK.AVG(B2,$B$2:$B$11) and press Enter to generate the rank.
- Copy the formula for the entire list by dragging down the fill handle.
Conclusion
In this article, we learned how to use RANK, RANK.EQ and RANK.AVG function to rank data in Excel.