The most obvious way to do this is to simply sort the data in ascending or descending order. However, sorting has one major drawback. If we sorted the student data, then it would sort the student's name as well. The student's name would move according to its position in the list. Ranking allows us to assign a rank value in a separate column without changing the original data.
There are numerous RANK functions available in Excel: RANK, RANK.EQ and RANK.AVG. The RANK function shows a warning symbol. This is because it is an older Excel function that has since been replaced by newer, better functions. It still shows in the list for people who are using a much older version of Excel.
In this article, we will focus on RANK.EQ and RANK.AVG to rank a list of movies based on the number of 5* reviews.
The RANK.EQ function is the replacement for the old RANK function. It has three arguments:
=RANK.EQ(number,ref,[order])
number
the number to rank
ref
what we are ranking it against
order
descending or ascending order
We can use RANK.EQ to rank our list of movies according to the number of 5* reviews.
The number we are ranking is contained in cell B4. Our reference is what we are ranking this number against, in this case, we are ranking the value in cell B4 against all other values in column B.
Press CTRL+Enter to stay in the same cell.
Double-click to copy the formula down.
A rank is now assigned to each movie. By default, items are ranked in descending order. This means the movie that is ranked #1 is the movie with the highest number of 5* reviews.
To sort in ascending order, we need to edit the formula and complete the order argument.
Now, the movie that is ranked #1 is the movie with the lowest number of 5* reviews.
Edit the formula again and change it back to sort in descending order.
A fairly common scenario is that we need to rank a list of values, but the list contains duplicates. Let's take a look at how RANK.EQ handles this. First, let's sort the list so the movies are ranked from largest to smallest.
Click in the 5* Reviews column.
From the Data tab, in the Sort & Filter group, click Z-A (Largest to Smallest).
The movies are now listed sequentially in descending order.
If we scroll down to rows 17 and 18, notice we have duplicate values. The RANK.EQ function assigns the same rank to duplicates. Also, notice that we no longer have a movie ranked in the 15th position as we have two movies occupying the 14th position.
The RANK.AVG function is very similar to RANK.EQ but it handles duplicate values differently. Instead of assigning the same rank to duplicates, it takes an average of the positions the duplicates occupy.
In this example, we have some call center data. We have already assigned the rank to each value and sorted the list in descending order. We can see that the data contains duplicate values.
Let's use the RANK.AVG function this time to see how it differs from RANK.EQ.
The result of RANK.AVG is the same as the result of RANK.EQ except where there are duplicate values. When a duplicate occurs, RANK.AVG will add together the ranks and divide them by the total number of items.
Take a look at the results below. There were 197 calls logged on both 1/4/2022 and 2/3/2022. The RANK.EQ function has assigned both dates with a rank of #1, whereas the RANK.AVG function has assigned a rank average of #1.5.
This is calculated based on the position. The first value is ranked in position 1 and the second value is ranked in position 2. Add both of those positions together and we get 3. The function then divides the total of the positions by the number of items, in this case 2 to get a result of 1.5.
RANK.EQ and RANK.AVG are useful functions on their own but can also be combined with other functions to complete practical tasks.
Check out more on this topic and all the MMC courses for Excel in our library here: Excel courses.