An average is the best way to get an insight of the data as a whole.
But sometimes average gives you biased value.
In those [almost every time] situations...
...the best way is to calculate the weighted average.
And I have found the best way is to use a formula by combining SUMPRODUCT and SUM functions.
This method is simple and easy to apply.
So today, in this post, I’d like to share with you how to calculate a weighted average in Excel using SUMPRODUCT and SUM.
Let’s get started.
Difference between Normal and Weighted Average
In the below example, we have 1658 as a normal average and 1445 as a weighted average.
Let me clarify this difference with two points.
First:
If you multiply 1658 (average) with 68 (quantity) you will get 112742 which not equals to the totals amount.
But, if you multiply 1445 (weighted average) with 68 (quantity) you will get 98289 which equals to the total amount.
Second:
Product-1 has the lowest price and highest quantity whereas Product-4 has the highest price and lowest quantity.
While calculating normal average you are not considering quantity and if there is a change in quantity there will be no effect on the average price.
And, in the weighted average, you can take quantity as a weight.
Step to Calculate Weighted Average in Excel
To calculate weighted average we are using the same data table which I have shown you in the above example.
You can download it from here to follow along.
Now, follow these two simple steps.
- In the C7 cell, enter following formula.
=SUMPRODUCT(B2:B6,C2:C6)/SUM(B2:B6)
- Click OK.
Now, you have 1445 as a weighted average of price and product.
As I said, sumproduct is an easy way to calculate the weighted average or weighted mean in Excel.
The best part about sumproduct is it can multiple and sum array in a single cell.
How does this work
We need to break down this formula to understand it.
First of all, sumproduct will calculate the product of price and quantity for the all the products return the sum of all those.
After that, sum function will give you the sum of quantity.
And, in the end, you will get the weighted average by dividing values from both of the function.
Check this out: SUMPRODUCTIF
Calculate Moving Weighted Average
Let’s come one step further. Let’s move in data analysis part.
By using the same formula you can calculate weighted moving average as well. Download this data table from here to follow along.
Here are the steps:
- Enter the below formula in the cell D4 and drag down to the end.
=SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4)
- Once you drag this formula it will calculate moving average on the basis of 3 months for each month.
How does this work
If you check above snapshot in cell D4 you have the moving average for Jan, Feb, and Mar.
And, in cell D5 you have the moving average for Feb, Mar, and Apr.
So, the thing is every time when you move to a new month you will get the moving average including current month and last two months.
You just need to make your cell references are relative when you drag down your formula.
Sample File
Download sample file from here to follow along.
You’ve got conflicting formulas, the first step needs changing –
In the C7 cell, enter following formula.
=SUMPRODUCT(B2:B6,C2:C6)/SUM(B2:B6)
it should read
In the C7 cell, enter following formula.
=SUMPRODUCT(B2:B6,C2:C6)/SUM(C2:C6)
Nice to meet you, Puneet. Others have said it already, indeed a great post: short, clear but message comes across. Ordinary average is yuk, weighted average on the other hand… Something more useful. Versatile SUMPRODUCT to the rescue.
Yup
Great post ! Thank you!
Thanks for your words Kapil.
I have been looking for this technique for a long time! Thank you for explaining this.
Thanks for your words Lisa.
Hi, great post. Could you discuss using the sumproduct function for looking up 3 or 4 pieces of data and bringing back the sum of what matches these conditions. Thanks for your great work !
Hello Enna,
I hope this will help you.
https://excelchamps.com/blog/excel-sumproduct-function/