By following this article, you will learn to calculate weighted average in Excel. We’ll use a simple formula, SUM function, and the combination of SUMPRODUCT and SUM functions whether or not the weights add up to 100.
Weighted averages are useful in various fields like finance, grade calculation, inventory valuation, quality control, project management, and market research.
Whether determining the cost of capital, calculating grades, valuing inventory, or analyzing customer satisfaction, weighted averages provide a versatile tool to reflect the relative importance of data points in diverse applications.
Download Practice Book
You can download the free Excel workbook here and practice on your own.
What Is Weighted Average?
Weighted Average is a statistical measure that takes into account the importance or weight assigned to each data point when calculating the average. It gives more significance to certain data points based on their assigned weights.
This results in a more accurate representation of the overall average. The weight reflects each data point’s relative importance or relevance in the calculation.
The key distinction between Average and Weighted Average is that the former treats all data points equally, while the latter incorporates data point weights to give more significance to certain values.
How to Calculate Weighted Average in Excel (When Weights Add Upto 100%)
Method 1: Using Simple Formula
First, inside cell B12, type the following formula:
=C5*D5+C6*D6+C7*D7+C8*D8+C9*D9Press Enter. The weighted average is calculated.
Method 2: Using SUMPRODUCT Function
Inside cell B12, write the following formula:
=SUMPRODUCT(C5:C9,D5:D9)Press Enter. This method is more convenient than the previous one.
How to Calculate Weighted Average in Excel (When Weights Do Not Add upto 100%)
Method 1: Using SUM Function
In cell E5 you can see that the weights add up to 120%. So, in this case, you need to divide the result obtained using the simple formula by summation of the weights which can be calculated using SUM function.
In cell B12, put the following formula:
=(C5*D5+C6*D6+C7*D7+C8*D8+C9*D9)/SUM(D5:D9)Press Enter.
Method 2: Combining SUMPRODUCT and SUM Functions
Again, in this case you need to divide the result obtained using SUMPRODUCT function by summation of the weights which can be calculated using SUM function.
In cell B12, write the following formula:
=SUMPRODUCT(C5:C9,D5:D9)/SUM(D5:D9)Press Enter. The average is calculated in accordance with their assigned weights.
Solution to Data and Weights Having Different Array Types
The SUMPRODUCT function in Excel can take multiple arrays as input. But all the arrays need to be in the same dimension. Otherwise, the formula will result in an #Value! Error.
We can use the TRANSPOSE function to change the array type. Inside cell D5 put the following formula:
=TRANSPOSE(C13:G13)Press Enter.
The Weight Percentage array in now turned into a vertical array from a horizontal one.
Now, inside cell F5, type the following formula:
=SUMPRODUCT(C5:C9,D5:D9)/SUM(D5:D9)Press Enter.
Things to Remember
- Before starting any calculation, check if the assigned weight percentages add upto 100 or not.
- Ensure that your data is properly organized with two columns: one for the values and another for the corresponding weights
- Make sure there are no missing or erroneous weights that could affect the accuracy of the weighted average.
- If you have zero or blank values in your data set, consider excluding them from the calculation of the weighted average. Including them might distort the result and produce inaccurate averages.
Frequently Asked Questions
- Is SUMPRODUCT a weighted average?
Not quite. We use the SUMPRODUCT and SUM functions together to determine the Weighted Average.
- What is weighted vs regular average?
Weighted averages incorporate weights to give more significance to specific values, while regular averages treat all values equally in the calculation.
Conclusion
By going through this article, I hope you can now effectively work with weighted average in Excel and obtain accurate results based on the assigned weights.
To further expand your knowledge and expertise in Excel, I encourage you to visit Exceldemy, a valuable resource for in-depth information on various uses of Microsoft Excel. There, you can explore a wide range of tutorials, tips, and tricks that will empower you to leverage the full potential of this powerful spreadsheet software.
Weighted Average Excel: Knowledge Hub
<<Go Back to How to Calculate Average in Excel | How to Calculate in Excel | Learn Excel
