A weighted average is a type of average where some values are given more importance (weight) than others. Instead of treating all values equally, the weighted average multiplies each value by its assigned weight and then calculates the total average based on these weighted values.
For example, if you have different exam scores where some exams are more important (have higher weight), the weighted average gives more significance to those exams when calculating your overall score.
Simplified Example
Let’s say you have two exam scores:
- Exam 1: 80 (weight 40%)
- Exam 2: 90 (weight 60%)
In a simple average, you would just add them up and divide by 2. But in a weighted average, you multiply each score by its weight:
Weighted Average = (80 * 0.4) + (90 * 0.6)
= 32 + 54
= 86Syntax
The syntax for calculating the weighted average in Excel using the SUMPRODUCT and SUM functions are as follows:
| Operation | Formula |
|---|---|
| Weighted Average | =SUMPRODUCT(values, weights) / SUM(weights) |
values: The data points for which you want to calculate the weighted average.weights: The weights applied to each value.
Example
Let’s consider a scenario where a student receives different grades, and each grade has a different weight. The following table calculates the weighted average of grades.
| Assignment | Grade | Weight | Weighted Calculation |
|---|---|---|---|
| Assignment 1 | 85 | 0.3 | =B2*C2 |
| Assignment 2 | 90 | 0.5 | =B3*C3 |
| Assignment 3 | 78 | 0.2 | =B4*C4 |
| Weighted Average | =SUMPRODUCT(B2:B4, C2:C4) / SUM(C2:C4) | ||
In this example, the weighted average of the grades is calculated using SUMPRODUCT(B2:B4, C2:C4) / SUM(C2:C4), where B2:B4 are the grades and C2:C4 are the corresponding weights.
Practice Exercise 1
Task: You have a list of products with prices and the number of units sold. Calculate the weighted average price based on the units sold for each product.
Hint: Multiply the price by the units sold for each product to calculate the weighted contribution, then use SUMPRODUCT and SUM to calculate the final weighted average price.
Practice Exercise 2
Task: You need to calculate the weighted average score of employees based on their performance scores and the weight assigned to different criteria.
Hint: Use the weight of each criterion to multiply the scores and then apply SUMPRODUCT and SUM to calculate the weighted average score.
Solution to Exercise 1
Weighted Average Price:
=SUMPRODUCT(B2:B4, C2:C4) / SUM(C2:C4)Solution to Exercise 2
Weighted Average Score:
=SUMPRODUCT(B2:B4, C2:C4) / SUM(C2:C4)This approach calculates the weighted average for both exercises using the SUMPRODUCT and SUM functions in Excel.