Let’s see how to calculate a weighted average in excel.
Weighted average is an average in which each quantity to be averaged is assigned a weight. These weightings determine the relative importance of each quantity on the average. Weightings are the equivalent of having that many like items with the same value involved in the average.
In excel we frequently come across the situation where we need weighted average or weighted mean to have an insight about the overall average.
In the data given below we see the average amount of shopping done by the number of households that are categorised or clustered in buckets based on certain characteristics.
And we are interested to find the overall weighted average across segments.
Enter the below weighted average formula in the cell B12 to calculate the weighted mean.
=SUMPRODUCT(A2:A10,B2:B10)/SUM(B2:B10)
It gives us the weighted average of the amounts in overall segment given the relative weight of the number of households.
Modify the weighted mean formula above to adjust your requirement.
Hope this helped.
