A blog showing how to use the SUMPRODUCT function
Part three of a three-part series of blogs

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.

  1. The SUMPRODUCT function in Excel
  2. Uses of SUMPRODUCT: conditional summing
  3. 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:

Sales and prices for products

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:

Average price calculation

This average price isn't realistic.

 

The elegant solution!

Instead, we want to calculate the weighted average price:

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:

=SUMPRODUCT(Sales*Prices)/SUM(Sales)

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!

  1. The SUMPRODUCT function in Excel
  2. Uses of SUMPRODUCT: conditional summing
  3. Weighted averages using the SUMPRODUCT function (this blog)
This blog has 0 threads Add post