How to Calculate Weighted Ranking in Excel (4 Ways)

Get FREE Advanced Excel Exercises with Solutions!

Calculating a simple average and a weighted average are two different things. For example, if you take Quizzes and all of them have the same weight, the average of your total marks will also be the weighted average of your scores. In reality, however, this is far from the truth. Some chores will always be more vital than others. Some examinations are more crucial than others. In this article, we will learn how to calculate weighted ranking in Excel. We will use a sample dataset that contains Quiz marks and their Weight.

how to calculate weighted ranking in excel


How to Calculate Weighted Ranking in Excel: 4 Effective Methods

In this blog post, we will see the use of SUMPRODUCT, SUM, and RANK functions and preferred methods for calculating the weighted average for different scenarios.


Method 1: Calculate Weighted Ranking with SUMPRODUCT Function

Suppose, I have a dataset containing Quiz marks of Students and weight for each quiz, which is in percentage, and the weight totals 100%. Let’s see, how to calculate and rank in this case. We will use the SUMPRODUCT function here.

how to calculate weighted ranking in excel

Steps:

  • First, type the following formula in cell G5.
=SUMPRODUCT(C5:F5,$C$11:$F$11)

how to calculate weighted ranking in excel using SUMPRODUCT

  • Now, press the ENTER key.

  • Finally, drag down to AutoFill the rest of the series.

Here, the SUMPRODUCT function works like C5*C11+D5*D11+E5*E11+F5*F11.

  • After that, type the following formula in cell H5. We will use the RANK function here.

  • Press the ENTER key.

how to calculate weighted ranking in excel using SUMPRODUCT and RANK Function

  • So, drag down to AutoFill rest of the series.

how to calculate weighted ranking in excel using SUMPRODUCT and RANK Function

  • At this point, select the Rank column, go to the Data tab, and click the Sort shortcut.

  • As a result, a dialogue box will pop up and we will select Expand the selection and click Sort.

how to calculate weighted ranking in excel using SUMPRODUCT and RANK Function

  • Finally, our dataset will look like the image below.

how to calculate weighted ranking in excel using SUMPRODUCT and RANK Function

Read More: How to Stack Rank Employees in Excel


Method 2: SUM Function to Calculate Weighted Ranking

Instead of the SUMPRODUCT function, we can also use the SUM function if we are given weights in a total of 100%.

Steps:

  • First, type the following formula in cell G5.
=SUM(C5*$C$11,D5*$D$11,E5*$E$11,F5*$F$11)

how to calculate weighted ranking in excel using SUM and RANK Function

  • After that, press the ENTER key.

how to calculate weighted ranking in excel using SUM and RANK Function

  • Finally, drag down to AutoFill rest of the series.

  • From here, we can follow Method 1 and then rank and sort the dataset which will give us the final output like the image below.

That’s it.


Method 3: Calculate Weighted Ranking for Weighted Numbers

Now, we have a scenario, where, weights total more than 100%.

how to calculate weighted ranking in excel using SUMproduct and RANK Function

Steps:

  • First, type the following formula in cell G5.
=SUMPRODUCT(C5:F5,$C$11:$F$11)/SUM($C$11:$F$11)

how to calculate weighted ranking in excel using SUMproduct, sum and RANK Function

  • Now, press the ENTER key.

how to calculate weighted ranking in excel using SUMproduct, sum and RANK Function

  • Finally, drag down to AutoFill the rest of the series.

  • Lastly, follow Method 1 and the first rank then sort the dataset. We will get the result as follows.

Read More: How to Rank Average in Excel


Method 4: Combined Formula to Calculate Weighted Ranking

In our last method, we will learn what steps to follow in case our weight is given as numbers.

how to calculate weighted ranking in excel using sum and RANK Function

Steps:

  • First, type the following formula in cell G5.
=SUM(C5*$C$11,D5*$D$11,E5*$E$11,F5*$F$11)/SUM($C$11:$F$11)

  • Now, press the ENTER key.

  • Finally, drag down to AutoFill the rest of the series.

how to calculate weighted ranking in excel using sum and RANK Function

  • You already know, what to do from here. In case you cannot recall the steps to follow Method 1.

Read More: How to Create an Auto Ranking Table in Excel


Practice Section

The single most crucial aspect in becoming accustomed to these quick approaches is practice. As a result, we’ve attached a practice workbook where you may practice these methods.

how to calculate weighted ranking in excel


Download Practice Workbook


Conclusion

That’s all for the article. These are 4 different methods for how to calculate weighted ranking in Excel. Based on your preferences, you may choose the best alternative. Please leave them in the comments area if you have any questions or feedback.


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.
Mahbubur Rahman
Mahbubur Rahman

MAHBUBUR RAHMAN is a leather engineer with a talent for Excel and a passion for VBA programming. To him, programming is like a time-saving wizard, making data manipulation, file handling, and internet interactions a breeze. His skill set extends to SPSS, STATA, Deep Neural Networks, and Machine Learning. Holding a B.Sc in Leather Engineering from Khulna University of Engineering & Technology, he's shifted to become a content developer. In this role, he crafts technical content centred around Excel... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo