By default, Excel will weigh each value in a dataset the same when calculating an average using the AVERAGE() function. This assumes that each value in a dataset carries an equal weight or has the same level of importance. However, this may not always be what you want. While Excel doesn’t provide a built-in way to calculate weighted averages, we can use other built-in functions to calculate this.
In this tutorial, you’ll learn how to calculate a weighted average in Excel. A weighted average is the average value resulting from multiplying each component by a value representing its importance.
By the end of this tutorial, you’ll have learned the following:
- What a weighted average is and how it’s calculated
- How to calculate a weighted average in Excel
Understanding the Weighted Average
A weighted average is calculated by taking into account the differing importance of some values. In some cases, a weighted average can be more accurate than a simple average. This is because, in most cases, some values will be more important than others.
A simple example is being able to calculate a student’s grade, based on the relative weight of different assignments. In many cases, some assignments will represent a smaller part of a student’s final grade. Meanwhile, tests like exams may represent a significant part of their final grade. Because of this, it would be incorrect to weigh these values equally.
Let’s see how a weighted average is calculated mathematically:
- Multiply each value by its weight
- Add up the weight values
- Divide the sum of weighted values by the sum of weights
In the case of our example using student grades, this would be quite intuitive. We multiply the weight by the student’s grade for each assignment. We then add up these weighted values. We divide this value by the sum of the weights. This quotient is our weighted average.
On the surface, this may seem a little complicated. But don’t worry, we’ll make easy work of calculating the weighted average in Excel by using the SUMPRODUCT() and SUM() functions.
How to Calculate a Weighted Average in Excel
How can you calculate a weighted average in Excel?
To calculate a weighted average in Excel you can combine the SUMPRODUCT() and SUM() functions. The SUMPRODUCT() function can be used to calculate the sum of values multiplied by their weights. This allows you to easily calculate the weighted average in a single formula.
Recall our earlier example: being able to calculate the final grade for a student based on different assignments. In the steps below, you’ll learn how to calculate the weighted average in Excel. We’ll begin by loading a dataset that contains the grades for different assignments.
Let’s dive in!
How to Calculate a Weighted Average in Excel
Load a dataset that contains weights and values in different columns
In the sample dataset that we loaded above, we have three columns: the assignment, the weight of the assignment, and the grade received. We want to make sure that the weight of each assignment is correctly calculated into the overall average.
Use the SUMPRODUCT() Function to Calculate a Weighted Sum
We can use the SUMPRODUCT() function which calculates the sum of multiplying iterative records. For example, the first record’s weight is multiplied by its grade. This occurs for every record. The products are then added together.
Use the SUM() Function to Calculate the Sum of Weights
The sum of the weights is calculated to give us a denominator to use afterward. Rather than dividing by the number of items, as we might when we’re calculating a normal average, we need to divide by the sum of weights.
Divide the Weighted Sum by the Sum of Weights
Finally, we can divide the weighted sum by the sum of weights to calculate the weighted average. In Excel, this means using the following formula: =B9/B10.
Calculating the weighted average in Excel is easily done using a combination of formulas!
Conclusion
In this tutorial, you learned how to calculate a weighted average in Excel by using the SUMPRODUCT() and SUM() functions. While Excel doesn’t provide a built-in function for calculating a weighted average, it’s easy to do with other functions. The weighted average is often a more accurate metric that allows you to better understand the average value of a dataset. This is because, in many cases, not every value is of the same importance as other values. You first learned why the weighted average matters and how it’s used. Then, you learned how to use Excel to calculate the weighted average using the SUMPRODUCT() and SUM() functions.
Additional Resources
To learn more about related topics, check out the tutorials below: