In many cases, the exams in a school, or products in a company carry equal weight or significance. In that case, the average is very easy to calculate. But, in some practical scenarios, the weights aren’t equivalent to each other. For that purpose, the **Weighted Average** is necessary to find out the average. But before that, we need to assign the weights to the variables. In this article, we’ll show you the practical examples of **Assigning Weights **to **Variables **in **Excel** and thus **calculate the Weighted Average**.

**Table of Contents**hide

## Download Practice Workbook

Download the following workbook to practice by yourself.

## 3 Useful Examples of Assigning Weights to Variables in Excel

### 1. Assigning Weights to Variables in Excel When Total Weight Is 100%

We know that various assessment systems prevail in educational institutions to evaluate the students’ performance. In our first example, we’ll assign the Weights to different types of exams present in a school. Here, in the following dataset, the **Weightages **add up to **100%** in total. Therefore, follow the steps below to figure out the weighted average.

**STEPS:**

- First, select cell
**C11**. - Then, type the formula:

`=SUMPRODUCT(C5:C9,D5:D9)`

- After that, press
**Enter**. - Lastly, it’ll return the accurate value.

**NOTE: ****The SUMPRODUCT function** multiplies the **C5:C9** array with the **D5:D9** array at first. Next, it sums the product outputs.

**Read More: How to calculate weighted average in Excel with percentages (2 ways)**

### 2. Allot Weights Whereas Total Weight Differs from 100%

However, there are some events where the **Total Weight **doesn’t add up to **100%**. See the below dataset for **Assigning Weights **to **Variables **in **Excel** and there, the total **Weight **is **200%**. So, learn the following steps to compute the average.

**STEPS:**

- Firstly, select cell
**C11**. - Subsequently, type the formula:

`=SUMPRODUCT(C5:C9,D5:D9)/SUM(D5:D9)`

- At last, press
**Enter**and it’ll return the average.

**NOTE:** At first, the **SUMPRODUCT **function multiplies the **C5:C9 **and **D5:D9** array and then, sums the product outputs. After that, the result is divided by the output of **the SUM function** which finds the total of the** D5:D9** array.

**Read More:** **Calculate Conditional Weighted Average with Multiple Conditions in Excel**

### 3. Use SUM Function to Assign Weights in Excel

In our last method, we’ll use the **SUM **function for **Assigning Weights **to **Variables **in **Excel **and determine the weighted average. This process is a kind of a manual process. So, with a smaller dataset, we can apply this method without any difficulties. But, if we have to deal with a large number of data values, this process may not be a suitable option. Now, learn the steps given below to carry out the operation.

To illustrate, we’ll use the following dataset which is the same as the first **2** examples.

**STEPS:**

- Select cell
**C11**at first. Type the formula:

`=SUM(C5*D5,C6*D6,C7*D7,C8*D8,C9*D9)`

- Then, press
**Enter**. It’ll return the precise result.

**NOTE:** The **SUM **function adds the products of the cells specified in the argument. Each cell is applied manually here.

Moreover, we can apply the function in another way to get the same output. For that, follow the below steps.

**STEPS:**

- Select cell
**C11**to type the formula:

`=SUM(C5:C9*D5:D9)`

- Eventually, press
**Enter**.

Here, inside the **SUM **function argument, we are multiplying the arrays **C5:C9** and **D5:D9** at first.

**Read More: How to Calculate Weighted Average in Excel Pivot Table**

## Conclusion

Henceforth, you will be able to carry out the operation for **Assigning Weights **to **Variables **in **Excel **following the above-described examples. Keep using them and let us know if you have any more ways to do the task. Follow **the ExcelDemy** website for more articles like this. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.