Super Sale WeekClaude Skills — 20% OFF
Tips

How to Calculate Weighted Average in Excel: 3 Easy Ways in 2026

Powerdrill Bloom·
How to Calculate Weighted Average in Excel: 3 Easy Ways in 2026

To calculate a weighted average in Excel, multiply each value by its weight, add the results, and divide by the sum of the weights. The quickest formula is =SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4), where column B holds the values and column C holds the weights. If your weights already add up to 100%, SUMPRODUCT alone gives the answer.

This guide explains what a weighted average is and when to use it. It then covers three ways to calculate a weighted average in Excel: SUMPRODUCT with SUM, a helper column, and SUMPRODUCT with criteria. Each method includes a worked example. Function details come from Microsoft's support pages, read on November 17, 2026.

What a weighted average is

A normal average treats every value the same. Microsoft describes the AVERAGE function as returning "the average (arithmetic mean) of the arguments." Each number counts once.

A weighted average gives some values more influence than others. Each value has a weight, and values with bigger weights pull the result toward them.

The formula is simple:

Weighted average = (value 1 × weight 1 + value 2 × weight 2 + …) ÷ (weight 1 + weight 2 + …)

Here is a quick example. A course grade comes from homework, a midterm, and a final exam. The final counts for half the grade, so a plain average would give the wrong result.

Component Score Weight
Homework 85 20%
Midterm 78 30%
Final exam 92 50%

The plain average of the three scores is 85. The weighted result is 86.4, because the high final score carries the most weight. That difference is why a weighted average matters.

When to use a weighted average

Use a weighted average whenever some items should count more than others. Common cases include:

  • Grades. Exams, projects, and homework carry different weights.
  • Prices. The average price paid across orders of different sizes.
  • Portfolios. The average return across holdings of different values.
  • Surveys. Responses weighted by group size or sample weights.
  • Inventory. The weighted average cost of stock bought at different prices.
  • KPIs. A score built from several metrics with different importance.

Weights can come from different places. Sometimes they are set by a rule, such as a syllabus that says the final exam is worth 50%. Sometimes they come from the data itself, such as units sold or the value of each holding. In both cases the math is the same.

If every item matters equally, a plain average is fine. If one item clearly deserves more say in the result, you need weights.

Set up your data

Put your data in a simple table before you write any formula. Use one row per item and these columns:

Column What goes in it
A Item name, such as Homework
B Value, such as the score
C Weight, as a number or a percentage

Weights can be percentages, such as 20%, or plain numbers, such as units sold. Both work with the formulas below, as long as you divide by the sum of the weights.

Two checks prevent most errors. First, make sure every value has a weight in the same row. Second, keep the value and weight ranges the same size, or the formula returns an error.

It also helps to turn the range into an Excel table. Select it, then select Home > Format as Table. New rows then extend the table, and formulas that use table references pick them up.

Method 1: SUMPRODUCT and SUM

This is the standard way to calculate a weighted average in Excel. It uses two functions in one formula.

Microsoft says SUMPRODUCT "returns the sum of the products of corresponding ranges or arrays." In plain terms, it multiplies each value by its weight and adds up the results.

The syntax is =SUMPRODUCT(array1, [array2], [array3], ...). For our grade example, the values sit in B2:B4 and the weights in C2:C4. Type:

=SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4)

Here is what Excel does:

  1. SUMPRODUCT multiplies 85 by 20%, 78 by 30%, and 92 by 50%.
  2. It adds the three products: 17 + 23.4 + 46 = 86.4.
  3. SUM adds the weights: 20% + 30% + 50% = 100%.
  4. The formula divides 86.4 by 100%, which gives 86.4.

When the weights add up to 100%, the division changes nothing. Keep it anyway. If someone later changes a weight and the total becomes 90%, the formula still gives a correct result.

Microsoft lists two rules for SUMPRODUCT that matter here. The ranges "must have the same dimensions," or the function returns a #VALUE! error. And SUMPRODUCT "treats non-numeric array entries as if they were zeros." A blank or text weight silently counts as zero, so check your data.

Microsoft also warns against full-column references. It says SUMPRODUCT "should not be used with full column references" for best performance. Use B2:B500, not B:B.

Method 2: A helper column

Some people prefer to see each step. A helper column shows every product, which makes the weighted average easy to check and explain.

  1. In column D, type a header such as Value × Weight.
  2. In D2, type =B2*C2 and copy it down.
  3. Under the last row, total column D with =SUM(D2:D4).
  4. Total the weights with =SUM(C2:C4).
  5. Divide the first total by the second.

For the grade example, column D shows 17, 23.4, and 46. They add up to 86.4. The weights add up to 100%, so the weighted average is 86.4.

This is the same math SUMPRODUCT does in one step. Microsoft's own page shows that a longer formula such as =C2*D2+C3*D3+C4*D4+C5*D5 gives "the same result" as SUMPRODUCT.

Use the helper column for teaching, auditing, or any sheet where others need to follow the logic. Use Method 1 when you want a compact sheet.

Method 3: SUMPRODUCT with criteria

Sometimes you need the calculation for only part of the data. For example, you might want the average price paid in one region, weighted by units.

SUMPRODUCT can test conditions inside the formula. Microsoft's example counts cherries sold in one region with =SUMPRODUCT((B2:B9=B12)*(C2:C9=C12)*D2:D9). The comparisons return TRUE or FALSE, which act as 1 or 0 when multiplied.

Apply the same idea here. Say column A holds the region, column B the price, and column C the units. To get the weighted average price for the East region:

=SUMPRODUCT((A2:A100="East")*B2:B100*C2:C100)/SUMIF(A2:A100,"East",C2:C100)

The top part adds price times units, but only for East rows. The bottom part uses SUMIF to add units for East rows only. Microsoft explains that SUMIF sums "the values in a range that meet criteria that you specify."

Put the region name in a cell instead of typing "East." Then you can change the cell to see each region without editing the formula.

For two conditions, such as region and month, add a second comparison to the top part. Use SUMIFS instead of SUMIF in the bottom part.

Worked example: weighted average price

Grades are the classic example. Prices are just as common at work. Here is a purchasing example.

A store bought the same product three times at different prices. Prices are per unit.

Order Units Price per unit Units × price
Order 1 10 4.00 40.00
Order 2 25 3.20 80.00
Order 3 15 5.00 75.00
Total 50 195.00

The plain average of the three prices is 4.07. That number is misleading, because most units came from the cheapest order.

The weighted average uses units as the weight:

=SUMPRODUCT(B2:B4,C2:C4)/SUM(B2:B4)

The top part is 195. The bottom part is 50. The weighted price is 3.90 per unit. This is the true average cost of the stock, and it is the figure to use for margins or reorder planning.

Notice the weights here are units, not percentages. That is fine. Dividing by the sum of the weights handles any kind of weight.

Weighted average in a PivotTable

PivotTables summarize large tables fast, but their built-in options do not include a weighted mean. The Average option in a PivotTable is a plain average. There is a simple workaround.

  1. In your source table, add a helper column that multiplies value by weight, such as =B2*C2. Name it Product.
  2. Refresh the PivotTable so it sees the new column.
  3. Select the PivotTable, then select PivotTable Analyze > Fields, Items & Sets > Calculated Field.
  4. Name the field, such as Weighted price, and enter the formula =Product/Units.
  5. Add the new field to the Values area.

This works because a calculated field divides the totals, not the single rows. For each group, it divides the sum of the products by the sum of the weights. That is exactly the right math.

Format the new field with the number format you need, such as two decimal places. The PivotTable now shows the correct figure for every region, product, or month you drag into the rows.

Weighted average with percentages

Many readers ask how to handle weights given as percentages. There are two cases.

Weights add up to 100%. You can use =SUMPRODUCT(B2:B4,C2:C4) without dividing. The result is already correct. Still, dividing by SUM(C2:C4) costs nothing and protects you from rounding or typing errors.

Weights do not add up to 100%. Always divide by the sum of the weights. Otherwise the result is too high or too low.

Format the weight cells as percentages. Select them, then choose Percentage from the number format list on the Home tab. Type 20 into a cell formatted as a percentage, and Excel stores it as 0.2.

A common mistake is mixing styles, such as 20% in one row and 30 in the next. Excel then treats the second weight as 3,000%. Keep every weight in the same format.

Calculate a weighted average in Excel with AI

The formulas above work well for one table. When your data is spread across many rows or several files, setting up the ranges takes time. An AI tool can read the file and do the math for you.

Here is the method in three steps, using Powerdrill Bloom.

Step 1: Upload your Excel file

Upload the workbook or CSV that holds your values and weights. The Excel Calculations page asks you to make sure your data "is correctly formatted to enable smooth calculations."

You can upload several files at once. The page says you can upload "up to 10 files at once into a dataset."

Uploading an Excel file to calculate a weighted average with Powerdrill Bloom

Step 2: Ask for the weighted average in natural language

Type the calculation you need. For example: "Calculate the average price per unit by region, weighted by units sold."

The page says the tool "will recognize and apply the correct formula." Ask it to show the formula it used, so you can rebuild it in your sheet.

Step 3: Check the result and reuse the formula

Compare the result with a quick manual check on one group. Then copy the formula into your workbook, or export the summary table.

The page says the tool will "quickly generate accurate results based on your text input." Keep the formula in your sheet so the result updates when the data changes.

Checking the weighted average result before reusing the formula

Common mistakes to avoid

These are the problems that come up most often when you calculate a weighted average in Excel.

  • Using AVERAGE. AVERAGE gives every value equal weight. Use SUMPRODUCT and SUM instead.
  • Not dividing by the weights. Unless the weights add up to exactly 100%, divide by their sum.
  • Mismatched ranges. SUMPRODUCT needs ranges of the same size, or it returns #VALUE!.
  • Blank weights. A blank weight counts as zero, which drops that row without warning.
  • Mixed percentage formats. Keep every weight as a percentage or every weight as a number.
  • Full-column references. Use bounded ranges, such as B2:B500, for speed.
  • Hard-coded criteria. Put criteria in a cell so the formula is easy to reuse.

For other common calculations, see our guides on how to make a pivot table in Excel and how to do ABC analysis in Excel.

When your values and weights live in a large export, try Powerdrill Bloom and ask for the weighted average in one sentence.

Frequently asked questions

What is the formula for a weighted average in Excel?

Use =SUMPRODUCT(values,weights)/SUM(weights). For example, =SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4) multiplies each value by its weight, adds the results, and divides by the total weight.

Can I calculate a weighted average without SUMPRODUCT?

Yes. Add a helper column that multiplies each value by its weight. Then divide the sum of that column by the sum of the weights.

How do I calculate a weighted average with percentages in Excel?

If the percentages add up to 100%, SUMPRODUCT of the values and weights gives the answer. If they do not, divide by the sum of the weights.

How do I calculate a weighted average with criteria?

Multiply a condition inside SUMPRODUCT, such as (A2:A100="East"), by the values and weights. Then divide by SUMIF of the weights for the same condition.

What is the difference between average and weighted average?

A plain average counts every value equally. A weighted average gives each value a weight, so more important values have more influence on the result.

Sources: Microsoft Support, SUMPRODUCT function · Microsoft Support, AVERAGE function · Microsoft Support, SUMIF function.