30 Advanced Excel Tips for 2026

Excel Champs / Excel Formulas List / How to Calculate Weighted Average in Excel (Formula)
By Puneet Gogia (Microsoft MVP)

How to Calculate Weighted Average in Excel (Formula)

30+ Advanced Excel Tips for 2026

No spam. Only Excel Stuff. God Promise. 😊

To calculate a weighted average, multiply each value by its weight, add those up, then divide by the total weight. SUMPRODUCT does the first part in one step: =SUMPRODUCT(B2:B6,C2:C6)/SUM(C2:C6), where B holds the values (price) and C holds the weights (quantity).

The key is that the divisor must be the sum of the weights, not the values — divide by the wrong column and you get a number that looks plausible but is wrong. A plain =AVERAGE() treats every row equally, which is why it can report an average price of 1658 when the actual average across all units sold is 1445.

If your weights are percentages that already total 100%, you can skip the division entirely: =SUMPRODUCT(B2:B6,C2:C6). And if any weight might be blank or zero, guard against a divide-by-zero with =IFERROR(SUMPRODUCT(B2:B6,C2:C6)/SUM(C2:C6),0).

An average is the best way to get an insight into the data. But sometimes, the average gives you a biased value. In those [every time] situations, the best way is to calculate the weighted average.

I have found that the best formula for it is a combination of the SUMPRODUCT and SUM functions. This method is simple and easy to apply. In this post, I’d like to share how to calculate a weighted average in Excel using SUMPRODUCT and SUM.

Weighted Average / Excel Formula

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 by using 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 about 112742, which does not equal the total amount. But if you multiply 1445 (weighted average) by 68 (quantity), you will get about 98289, which equals the total amount (the small differences come from rounding the averages).
  • 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, so if there is a change in quantity, there will be no effect on the average price. And in the weighted average, you can take the quantity as a weight.

Steps to Calculate Weighted Average in Excel

To calculate the weighted average, we are using the same data table that I have shown you in the above example.

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

Now, follow these two simple steps.

  • In cell C7, enter the following formula (formula below this list), where B2:B6 has the quantities (the weights) and C2:C6 has the prices: =SUMPRODUCT(B2:B6,C2:C6)/SUM(B2:B6).
=SUMPRODUCT(B2:B6,C2:C6)/SUM(B2:B6)
enter formulas in data table to use to calculate weighted average in excel with sumproduct
  • Press Enter.
click ok in data table to use to calculate weighted average in excel with sumproduct

Now, you have 1445 as the weighted average price. As I said, SUMPRODUCT is an effortless way to calculate the weighted average or mean in Excel. The best part about SUMPRODUCT is that it can multiply and sum arrays in a single cell, without entering it as an array formula.

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, the SUM function will give you the sum of the quantity. In the end, you will get the weighted average by dividing the values from both functions.

Check this out: SUMPRODUCTIF

Calculate the Moving Weighted Average

Let’s go one step further and move to the data analysis part. Using the same formula, you can also calculate the weighted moving average.

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 to the end (here, the weights are in column C): =SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4)
=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, you will see that in cell D4, you have the moving average for January, February, and March, and in cell D5, you have the moving average for February, March, and April.

So, every time you move to a new month, you will get the moving average, including the current month and the last two months. When you drag down your formula, you just need to make your cell references relative.

Weighted Average with Percentage Weights

Sometimes, the weights are percentages that add up to 100%, like marks where a test counts 20%, an assignment 30% and the final exam 50%. In that case, you don’t need to divide by the sum, as it’s 1. With the marks in F2:F4 and the weights in E2:E4, this returns 82 for 80, 70 and 90:

=SUMPRODUCT(F2:F4,E2:E4)

If you aren’t sure that the weights add up to exactly 100%, keep the /SUM(E2:E4) part. It gives the same result when they do and the right result when they don’t.

Weighted Average with a Condition

To calculate the weighted average for one group only, like the products sold in the North region (regions in A2:A6), add the condition inside SUMPRODUCT and use SUMIF for the total weight:

=SUMPRODUCT((A2:A6="North")*B2:B6*C2:C6)/SUMIF(A2:A6,"North",B2:B6)

The condition returns TRUE or FALSE for each row, and multiplying by it keeps only the North rows in the calculation.

Common Mistakes

  • Dividing by the wrong total: divide by the sum of the weights, not by the number of rows or the sum of the values.
  • Ranges of different sizes: both ranges in SUMPRODUCT must have the same size, or it returns #VALUE!.
  • All weights blank or zero: the formula divides by zero and returns #DIV/0!. Wrap it in IFERROR if that can happen (formula below this list).
  • Numbers stored as text: SUMPRODUCT treats them as zero, so the result is too low. Convert them to numbers first.
=IFERROR(SUMPRODUCT(B2:B6,C2:C6)/SUM(B2:B6),0)

Frequently Asked Questions

Is there a weighted average function in Excel?

No. Excel doesn’t have a WEIGHTEDAVERAGE function, which is why SUMPRODUCT divided by SUM is the standard way to calculate it.

Can I calculate a weighted average in a pivot table?

Not with the built-in summary options. Add a helper column with price × quantity to your data, then in the pivot table divide the sum of that column by the sum of the quantity with a calculated field.

What is the difference between weighted average and average?

A normal average gives every value the same importance. A weighted average gives more importance to the values with bigger weights, like a product that sells in larger quantities.

Puneet Gogia

Written by

Puneet Gogia

Microsoft MVP — Excel · Founder, Excel Champs

Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.

3M+ readers helped
1,000+ tutorials written
Since 2015 teaching Excel

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. I have been looking for this technique for a long time! Thank you for explaining this.

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