Excel Productivity Guide
Useful Excel Tips | Keyboard Shortcuts | VBA Codes
Download this Free E-Book to take your excel skills to a whole next level using some smart Excel tactics.
10000+ Copies Already Downloaded
The Weighted Average is One the Most Important Calculations and Sumproduct is One of the Easiest Methods to Calculate it.
This method is super easy and simple to apply.
So today, in this post, you will learn how to use the sumproduct function to calculate the weighted average in excel in simple steps.
But before I detail you about using it, let me tell you why a weighted average is a best practice to follow.
Table of Content
In the below example, we have 1658 as a normal average and 1445 as a weighted average. Let me clarify above calculation in two points.
Now, let’s learn the steps to calculate the weighted average with sumproduct.
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.
Just follow these two simple steps.
Now, we 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. And the best part is it will do all the calculation in a simple cell.
Let me break down the entire formula for your better understanding.
Let’s come one step further. Let’s move in data analysis part.
Now here I will detail you how to calculate the weighted moving average in excel. And, idea is simple, you can use the same method to calculate moving average that you have used above.
Download this data table from here to follow along.
Follow these simple steps.
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 have to make your cell references is relative when you drag down your formula.
The best part of sumproduct is you don’t have to add any helper column in your data table. Everything is in a single cell.
And, if you are still relying on normal average, make sure to move to weighted average if possible.
So now, give me some quick word. Have you ever calculated weighted average before? What method are you using for that? Please share your views in the comment box.