BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
The SUMPRODUCT function is one of the hardest ones to understand in Excel, but it's also pretty useful. This blog gives clear examples of how to use SUMPRODUCT for conditional sums, weighted averages and the like.
- The SUMPRODUCT function in Excel
- Uses of SUMPRODUCT: conditional summing
- Weighted averages using the SUMPRODUCT function (this blog)
Posted by Andy Brown on 01 November 2013
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Weighted averages using the SUMPRODUCT function
The SUMPRODUCT function provides by far the best way to calculated weighted averages in Excel. Here's how!
Our example
We want to calculate the average value of sales for the following highly authentic figures:
The sales figures for 5 must-have Christmas presents.
The average price is 503.80 pounds, but this doesn't reflect the fact that we've sold only one Ducati, but 82 fur mittens:
This average price isn't realistic.
The elegant solution!
Instead, we want to calculate the weighted average price:
The average price is 503.80 pounds, but weighted by the quantities bought it's 134.55 pounds.
The statistical formula for the weighted average is:
There's really no other easy way to do this in Excel!
Hope you've enjoyed learning (or refreshing your knowledge) about SUMPRODUCT - maybe I'll be brave and write a blog about the INDIRECT function next!
- The SUMPRODUCT function in Excel
- Uses of SUMPRODUCT: conditional summing
- Weighted averages using the SUMPRODUCT function (this blog)