Calculate Weighted Average in ExcelLearn how to calculate a dataset’s weighted average in Excel

There are a lot of mathematical equations one can perform in Excel.

For example, you can perform basic mathematical operations (addition, subtraction, multiplication, division) in Excel via formulas.

You can also calculate averages in Excel via the AVERAGE function.

And by average, I mean the simple average where it is assumed that each value in the dataset carries the same weight.

This means that not one value is more important than the other.

That said, not everything is simple in real life and that includes averages.

Sometimes, one value in a dataset may carry more weight than the other.

For example, as an accounting student, your marks in certain subjects carry more weight compared to others (minor subjects).

In such a case, just calculating the simple average won’t do. You need to calculate the weighted average instead.

Thankfully, you can still calculate the weighted average of a dataset in Excel.

It may not be as simple as using the AVERAGE function, but it’s really not that complicated either.

In this article, I’ll be showing you how to calculate a dataset’s weighted average in Excel.

I’ll be showing you what Excel functions you can use to perform such a task.

By the end of the article, you should be able to calculate any dataset’s weighted average whenever you have to.

But before we go discussing how you can calculate the weighted average in Excel, let’s familiarize ourselves first with what it is.

Weighted Average: What is it?

When someone talks about averages, they usually mean the simple average where you just calculate the average of a dataset without minding the weight of each value in it.

For example, let’s say a data set has the values: 2, 4, 6, 8, 10. Now if someone asks for the average value of this dataset, you’d probably answer 6.

When calculating a simple average, you assume that each value in the dataset carries the same weight.

That’s not the case with the weighted average though.

Whenever you calculate a weighted average, you have to mind the weight that each value in the dataset carries.

In other words, you need to take into account the importance of each value.

Now, the weight of a value will depend on what’s assigned to it.

If no weight is assigned to each value, then it’s assumed that all of them carry the same weight (which will result in the weighted average being equal to the simple average).

For example, let’s say that a store sells three types of products: product A, product B, and product C.

Here’s what the sales data for the most recent month looks like:

Now, if we want to get the average sales, do we simply divide the total sales by the number of products (which is 3)?

Or do we take into account the units sold for each product?

If you said yes to the first question, then what you’re calculating is the simple average.

If you said yes to the second question instead, then what you’re calculating is the weighted average.

How to calculate the weighted average of a dataset?

To calculate the weighted average of a dataset, you’ll need to multiply each value with its assigned weight.

This assigned weight could be a percentage or a number (e.g. frequency, number of units, etc.).

If the assigned weight is a number, the general formula for calculating the weighted average is as follows:

You need to multiply each value by its assigned weight and then divide the sum of these multiplied values by the total weight.

As an example, let’s apply this formula to our illustration above (sales data of a store) to get the weighted average sales (we’re using the number of units sold as the weight):

Note that the above formula also applies if the total weight is a percentage that isn’t equal to 100%.

However, if the assigned weights are percentages and the total weight is 100%, then all you need to do is multiply each value by its assigned weight, then add up all of the resulting values:

Weighted Average = (value1 x weight1) + (value2 x weight2) + (value3 x weight3)…

Now that we have a deeper understanding of the weighted average, let’s discuss how we can calculate it in Excel.

Use SUMPRODUCT to Calculate the Weighted Average

In Excel, there are mainly two functions we can use to calculate a dataset’s weighted average: SUMPRODUCT and SUM.

We’ll first discuss how we can use SUMPRODCT to calculate a dataset’s weighted average.

Calculate the weighted average where the total weight doesn’t add up to 100%

We’ll use the following formula to calculate the weighted average in cases where the total weight isn’t a percentage or it is but it doesn’t add up to 100%:

=SUMPRODUCT(rangeX,rangeY)/SUM(rangeY)

Where

rangeX – refers to the range of cells that contain the values

rangeY – refers to the range of cells that contain the weight of each value

Suppose we have the following dataset:

We want to calculate the weighted average sales of this dataset with the number of units sold as the weight of each product.

To do so, we’ll be using the SUMPRODUCT function:

• Select an empty cell. We’ll be entering the formula in this cell. In our illustration, this will be cell D7.
• In the selected cell, enter the formula for calculating the weighted average. For our illustration, this formula will be =SUMPRODUCT(D2:D4,C2:C4)/SUM(C2:C4).

• Press the Enter key to run the formula. It should now return the weighted average (weighted average sales in this case).

Calculate the weighted average where the total weight adds up to 100%

Calculating the weighted average is simpler if the total weight is 100%.

We only need to multiply each value with its assigned weight, then add them all up.

Thus, the formula for such a case will be:

=SUMPRODUCT(rangeX,rangeY)

Where

rangeX – refers to the range of cells that contain the values

rangeY – refers to the range of cells that contain the weight of each value

The SUMPRODUCT function will automatically multiply the cells in the same row (e.g. A1*B1, A2*B2).

To illustrate, we’ll be using the following dataset:

We’ll be calculating the weighted average score using the SUMPRODUCT function.

• Select an empty cell. We’ll be entering the formula in this cell. In our illustration, this will be cell C7.
• In the selected cell, enter the formula for calculating the weighted average. For our illustration, this formula will be =SUMPRODUCT(B2:B5,C2:C5).

• Press the Enter key to run the formula. It should now return the weighted average (weighted average score in this case).

Use SUM to Calculate the Weighted Average

If you’re unsure about using SUMPRODUCT to calculate the weighted average of a dataset, you can always use the SUM function instead.

By default, SUMPRODUCT is just the sum of resulting values that are derived from multiplying the cells of two or more arrays that are in the same row.

Such calculation can be done with the SUM function.

Calculate the weighted average where the total weight doesn’t add up to 100%

In cases where the total weight isn’t a percentage or it is but it doesn’t add up to 100%, we’ll use the following formula to calculate the weighted average:

=SUM(value1*weight1,value2*weight2,value3*weight3…)/SUM(range)

Where

value – refers to the value or cell that contains the value

weight – refers to the weight of each value or cell that contains the weight

range – refers to the range of cells that contain the weight of each value

Suppose we have the following dataset:

We want to calculate the weighted average sales of this dataset with the number of units sold as the weight of each product.

To do so, we’ll be using the SUM function:

• Select an empty cell. We’ll be entering the formula in this cell. In our illustration, this will be cell D7.
• In the selected cell, enter the formula for calculating the weighted average. For our illustration, this formula will be =SUM(D2*C2,D3*C3,D4*C4)/SUM(C2:C4).

• Press the Enter key to run the formula. It should now return the weighted average (weighted average sales in this case).

Calculate the weighted average where the total weight adds up to 100%

Just like with the SUMPRODUCT, calculating the weighted average is simpler if the total weight is 100%.

 

We only need to multiply each value with its assigned weight, then add them all up.

Thus, the formula for such a case will be:

=SUM((value1*weight1,value2*weight2,value3*weight3…)

Where

value – refers to the value or cell that contains the value

weight – refers to the weight of each value or cell that contains the weight

Let’s apply this formula to this dataset to get the weighted average score:

We’ll be calculating the weighted average score using the SUM function.

• Select an empty cell. We’ll be entering the formula in this cell. In our illustration, this will be cell C7.
• In the selected cell, enter the formula for calculating the weighted average. For our illustration, this formula will be =SUM(B2*C2,B3*C3,B4*C4,B5*C5).

• Press the Enter key to run the formula. It should now return the weighted average (weighted average score in this case).

A trick with the SUM function

As you can see from the above formulas, you’d have to write out each value-weight combination when using the SUM function. This is fine if the dataset is only small. But if there are hundreds or thousands of values, it could get tedious and very time-consuming. Not to mention that there is more room for human error.

So instead of using the above formulas, we’ll using these instead:

If the total weight doesn’t add up to 100%

=SUM(rangeX*rangeY)/SUM(rangeY)

If the total weight adds up to 100%

=SUM(rangeX*rangeY)

rangeX – refers to the range of cells that contain the values

rangeY – refers to the range of cells that contain the weight of each value

The trick here is to press the key combination Ctrl + Shift + Enter instead of just the Enter key when running the formula. Here’s a sample where I entered the formula before running it:

And here’s what it looked like after I press the key combination Ctrl + Shift + Enter to run it:

The formula is now encased in curly brackets.

You may use this trick on large datasets if you don’t want to use the SUMPRODUCT function to calculate the weighted average.

Conclusion

And those are the different formulas you can use to calculate a dataset’s weighted average in Excel. We were also able to discuss what a weighted average is and how it is different from the usual average. I hope that you’ll be able to use your learnings here in your future endeavors.