How to Calculate Weighted Average in Excel

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.

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

Now, 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 in excel with sumproduct
  • Click OK.
click ok in data table to use to calculate weighted average in excel with sumproduct

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.

formulas break down to calculate weighted average in excel with sumproduct

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.

with moving average calculate weighted weighted average in excel with sumproduct

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)

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

About the Author

Puneet Gogia

Puneet is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can find him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.

LinkedIn - YouTube

9 thoughts

Leave a Comment

Your email address will not be published.

  1. 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)

  2. 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.

  3. 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 !