This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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. The SUMPRODUCT function in Excel Uses of SUMPRODUCT: conditional summing Weighted averages using the SUMPRODUCT function (this blog)

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.

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

In this blog

Our example
The elegant solution!

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:

=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!