How to Calculate Weighted Average in Excel (Formula)

Last Updated: March 24, 2024
puneet-gogia-excel-champs

- Written by Puneet

An average is the best way to get an insight into the data. But sometimes average gives you a biased value. In those [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.

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
  • First: If you multiply 1658 (average) by 68 (quantity) you will get 112742 which does not equal the total amount. But if you multiply 1445 (weighted average) by 68 (quantity) you will get 98289 which equals 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 the normal average you are not considering the 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 the weighted average, we are using the same data table which I have shown you in the above example.

Get the Excel File

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 effortless way to calculate the weighted average or weighted mean in Excel. The best part about SUMPRODUCT is it can multiply and sum an array in a single cell.

=SUMPRODUCT(B2:B6,C2:C6)/SUM(C2:C6)

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, SUMPRODUCT will calculate the product of price and quantity for all the products and return the sum of all those. After that, some functions will give you the sum of the quantity. And in the end, you will get the weighted average by dividing values from both functions.

Check this out: SUMPRODUCTIF

Calculate Moving Weighted Average

Let’s come one step further. Let’s move to the data analysis part. By using the same formula, you can calculate the weighted moving average as well.

Get the Excel File

with moving average calculate weighted weighted average in excel with sumproduct

Here are the steps:

  • Enter the formula below in cell D4 and drag it 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
  • Once you drag this formula it will calculate the moving average on the basis of 3 months for each month.

How Does this Work

If you check the 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 you move to a new month you will get the moving average including the current month and the last two months. You just need to make your cell references are relative when you drag down your formula.

Get the Excel File

9 thoughts on “How to Calculate Weighted Average in Excel (Formula)”

  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)

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

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

    Reply

Leave a Comment