**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**.

**Table of Contents**hide

## Download Practice Workbook

## 4 Ways to Calculate Weighted Ranking in Excel

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, 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 **SUMPRODUCT function** here.

**Steps:**

- First, type the following formula in cell
**G5**.

`=SUMPRODUCT(C5:F5,$C$11:$F$11)`

- Now, press the
**ENTER**key.

- Finally, drag down to
**AutoFill**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.

- So, drag down to
**AutoFill**rest of the series.

- 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**.

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

**Read More: How to Stack Rank Employees in Excel (3 Methods)**

### 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)`

- After that, press the
**ENTER**key.

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

**Read More: ****How to Create a Ranking Graph in Excel (5 Methods)**

**Similar Readings**

**How to Calculate Rank Percentile in Excel (7 Suitable Examples)****Rank IF Formula in Excel (5 Examples)****How to Calculate the Top 10 Percent in Excel (4 Ways)**

### Method 3: Calculate Weighted Ranking for Weighted Numbers

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

**Steps:**

- First, type the following formula in cell
**G5**.

`=SUMPRODUCT(C5:F5,$C$11:$F$11)/SUM($C$11:$F$11)`

- Now, press the
**ENTER**key.

- Finally, drag down to
**AutoFill**rest of the series.

**Read More: ****How to Rank Within Group in Excel (3 Methods)**

### 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.

**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**rest of the series.

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

**Read More: How to Rank with Ties in Excel (5 Simple Ways)**

## 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.

## 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**

**Ranking Based on Multiple Criteria in Excel (4 Cases)****How to Create an Auto Ranking Table in Excel (with Quick Steps)****Excel Formula to Rank with Duplicates (3 Examples)****How to Rank Average in Excel (4 Common Scenarios)****Excel Percentile Rank Inc vs Exc****Ranking Data in Excel with Sorting (3 Quick Methods)**