How to Rank Average in Excel (4 Common Scenarios)

Get FREE Advanced Excel Exercises with Solutions!

Rank average is one of the methods of ranking data where the same values get an average rank. In Excel, there is an inbuilt statistical function- to rank data from a list and assign the same rank for duplicate values. The function is called Excel RANK.AVG function. In this article, I’ll introduce you to the function and show you how to deal with rank average in Excel.

Let’s say you have a dataset where the obtained number of different students in a test is given. You want to rank them based on their number.

dataset


Rank & Average in Excel

Before diving into a discussion about RANK.AVG function let’s recap the basics first. In RANK.AVG, the principle of two other functions- the RANK function and the AVERAGE function is used. The RANK function is used to determine the rank or order of a number in a list. So, using this function we can rank the numbers of a list. But if there are two or more of the same values, the RANK function will display the same rank (the rank if the value is unique) for all the values.

 the rank function

Here, the idea of incorporating the AVERAGE function in the RANK function comes. The AVERAGE function gives the average value of some numbers.

the average funcion

The RANK.AVG function works in the same manner as the RANK function, but it gives an average rank if there are two or more of the same values. From the next sections of the article, you will get a more clear idea about the function.

Read More: Excel Percentile Rank Inc vs Exc


Rank Average in Excel Simultaneously

As I’ve mentioned earlier there is a function that provides the rank with average, first, let’s get to know about the function a bit. The RANK.AVG function returns the rank of a number in a list of numbers: its size relative to other values in the list; if more than one value has the same rank, the average rank is returned. A numeric value will be the output that denominates the rank of the number in a list.

The syntax of this function is,

RANK.AVG(number, ref, [order])

syntax

Argument Required/Optional Explanation
number Required The numerical value whose rank will be determined in a list
ref Required An array or list that contains the numbers to rank against. The non-numerical entry of the list is ignored.
Order Optional The order of ranking, If empty or 0, the order will be descending. If 1, the order will be ascending.

This function is first available in Excel 2010. In Excel 2007 or any other earlier version, the Excel RANK function is available. The RANK.AVG function is an upgrade of the RANK function.


1. Using Excel RANK-AVERAGE to Rank a List Based on Value

You can rank the numbers of a list by using the RANK.AVG function. Suppose, you have a dataset where the obtained number of different students in a test is given.

➤ Type the following formula in cell D5,

=RANK.AVG(C5,$C$5:$C$11)

The function will determine the rank of the number in cell C5 in the list $C$5:$C$11.

Don’t forget to lock the cell of the list. It will allow you to drag cell D5 to determine the rank of other numbers in the list.

rank average in Excel

➤ Press ENTER.

As a result, you will get the rank of the number in cell C5.

result

Finally,

➤ Drag cell D5 to the end of your dataset.

As a result, you will get the ranks for all the numbers on the list.

output


2. Ranking Duplicates with Excel Rank-Average Function

Now, let’s see what happens if there are duplicate values in the list. Let’s say, you have the following dataset where the number 84 appears three times.

dataset

To determine the ranks of these numbers,

➤ Type the following formula in cell D5,

=RANK.AVG(C5,$C$5:$C$11)

The function will determine the rank of the number in cell C5 in the list $C$5:$C$11.

Don’t forget to lock the cell of the list. It will allow you to drag cell D5 to determine the rank of other numbers in the list.

rank average in Excel

➤ Press ENTER.

As a result, you will get the rank of the number in cell C5.

result

Finally,

➤ Drag cell D5 to the end of your dataset.

As a result, you will get the ranks for all the numbers on the list.

output

If you observe the result you will see the formula gives the rank of the number 84 as 5. The number 84 appears three times. The previous number in the descending order is 87 whose rank is 3 and the next number in the descending order is 69 whose rank is 7. So, the three 84 occupy the 4th, 5th, and 6th positions. The average of these positions is 5th. So, the RANK.AVG function assigns the rank 5 for all the three 84.


3. Ranking in Ascending Order Using RANK-AVERAGE

With the RANK.AVG function you can get the rank of the numbers of a list in ascending order.

➤ Type the following formula in cell D5,

=RANK.AVG(C5,$C$5:$C$11,1)

The function will determine the rank of the number in cell C5 in the list $C$5:$C$11. Here the optional argument 1 indicates that the rank will be assigned in ascending order.

rank average in Excel

➤ Press ENTER.

As a result, you will get the rank of the number in cell C5 in ascending order.

result

Finally,

➤ Drag cell D5 to the end of your dataset.

As a result, you will get the ranks for all the numbers of the list in ascending order.

result


4. Ranking in Descending Order with RANK-AVERAGE

If you input the number 0 as the optional argument of the RANK.AVG function, you will get the rank in descending order. The RANK.AVG function ranks numbers in descending order by default. So, if you leave the optional argument empty, you will also get the rank in descending order.

➤ Type the following formula in cell D5,

=RANK.AVG(C5,$C$5:$C$11,0)

The function will determine the rank of the number in cell C5 in the list $C$5:$C$11. Here the optional argument 0 indicates that the rank will be assigned in descending order.

rank average in Excel

After that,

➤ Press ENTER.

As a result, you will get the rank of the number in cell C5 in descending order.

result

Finally,

➤ Drag cell D5 to the end of your dataset.

As a result, you will get the ranks for all the numbers on the list in descending order.

rank average in Excel


💡 Things to Remember

📌 If the number is not in the range assigned as the ref, the function will return #N/A! Error.

📌 If there is any non-numeric data in the list, it will be ignored by the RANK.AVG function.


📂 Download Practice Workbook


Conclusion

That’s for the article. I’ve tried to introduce you to the methods of getting rank average in Excel. I hope now you know how to rank average in Excel. If you have any confusion, please feel free to leave a comment.


Related Articles


<< Go Back to Excel RANK Function | Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Prantick Bala
Prantick Bala

PRANTICK BALA is a marine engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, Excel, and VBA and goes beyond the basics. Armed with a B.Sc in Naval Architecture & Marine Engineering from BUET, he has transitioned into the role of a content developer. Beyond his work, find him immersed in books, exploring new... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo