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

How to Calculate Weighted Average With Sumproduct

how to calculate weighted average with sumproduct

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.

The weighted mean is similar to an ordinary arithmetic mean (the most common type of average), except that instead of each of the data points contributing equally to the final average, some data points contribute more than others. Wikipedia

Table of Content

  1. Difference Between Normal and Moving Average
  2. Calculate Weighted Average
  3. Calculate Weighted Moving Average
Instant Access: Download this quick PDF guide to learn more about weight average.

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 above calculation in two points.

different between normal weighted average and how to calculate weighted average with sumproduct

  • If you multiply 1658 with 68 you will get 112742 and if you multiply 1445 with 68 you will get 98289 which equals to the total amount.
  • Product-1 has the lowest price and highest quantity whereas Product-4 has the highest price and lowest quantity.
    1. 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.
    2. And, in the weighted average, you can take quantity as a weight.

Now, let’s learn the steps to calculate the weighted average with sumproduct.

Step To Calculate Weighted Average Using 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.

data table to use to calculate weighted average with sumproduct

Just follow these two simple steps.

  • In the C7 cell, enter following formula.
    • =SUMPRODUCT(B2:B6,C2:C6)/SUM(B2:B6)

enter formulas in data table to use to calculate weighted average with sumproduct

  • Click OK.

click ok in data table to use to calculate weighted average with sumproduct

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.

How Does This Formula Works?

Let me break down the entire formula for your better understanding.

formulas break down to calculate weighted average with sumproduct

  1. In the first part, sumproduct will calculate the product of price and quantity for the all the products return the sum of all those.
  2. In the second part, sum function will give you the sum of quantity.
  3. And, in the end, you will get the weighted average by dividing values from both of the function.

Calculate Moving Weighted Average

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.

with moving average calculate weighted average with sumproduct

Follow these simple steps.

  • Enter the below formula in D4 “=SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4)” and drag down to the end. 

add formulas to calulate moving average and calculate weighted average with sumproduct

  • Once you drag this formula it will calculate moving average on the basis of 3 months for each month.

How Does This Formula Works?

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.

Sample File

download sample file to learn about calculating weighted average with sumproduct

Conclusion

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.

More Tips



  • Eanna Black

    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 !

  • Lisa D

    I have been looking for this technique for a long time! Thank you for explaining this.

    • Puneet Gogia

      Thanks for your words Lisa.

  • Great post ! Thank you!

    • Puneet Gogia

      Thanks for your words Kapil.

  • GraH

    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.

    • Puneet Gogia

      Yup