How to Calculate a Weighted Average in Excel, and Why AVERAGE Gets It Wrong

Calculate a weighted average in Excel with SUMPRODUCT and SUM, add a condition, show it in a pivot table, and see why a plain AVERAGE of prices misleads.

Founder, Glow Reports. , 3 min read
  • Excel
  • Formulas
  • Reporting Workflows
A balance scale with stacks of blocks of different heights, the bigger stack tipping the beam, beside a small table
On this page
  1. What is a weighted average?
  2. How do you calculate a weighted average in Excel?
  3. Why does a plain average give the wrong answer?
  4. How do you calculate a weighted average with a condition?
  5. How do you show a weighted average in a pivot table?
  6. How do you calculate a weighted average in Google Sheets?
  7. Where does Glow Reports fit?
  8. Common questions

You want the average price your customers paid, so you select the unit price column and type AVERAGE. The answer is wrong whenever some rows sold far more units than others. A weighted average fixes it: each value counts in proportion to its weight, here the units sold. In Excel, does the job in one cell. Below: the formula, why the plain average misleads, the version with a condition, the pivot table route and Google Sheets.

What is a weighted average?

A weighted average multiplies each value by its weight, adds the results, and divides by the sum of the weights. Values with a large weight pull the result toward them. When every weight is equal, it gives the same answer as a plain average.

As a formula: weighted average = (value 1 × weight 1 + value 2 × weight 2 + ...) / (weight 1 + weight 2 + ...).

The weight is whatever makes one row count more than another. Units sold for an average price. Revenue for an average margin rate. Number of customers for an average churn rate across segments. Hours for an average hourly cost.

The example below uses six rows of sales, with the region in column A, the product in B, units sold in C and the unit price in D.

RegionProductUnitsUnit price
NorthBasic9004.50
NorthPlus20012.00
NorthPro2030.00
SouthBasic3004.50
SouthPlus10012.00
SouthPro3030.00

How do you calculate a weighted average in Excel?

Use SUMPRODUCT for the top of the fraction and SUM for the bottom. With units in C2:C7 and prices in D2:D7, type . The example returns 6.77, the average price paid per unit.

Weighted average price, 6.77
=SUMPRODUCT(C2:C7,D2:D7)/SUM(C2:C7)

SUMPRODUCT "returns the sum of the products of corresponding ranges or arrays" Microsoft Support. Here it multiplies units by price on each row and adds the six results: 10,500 of revenue. SUM adds the units: 1,550. Revenue divided by units gives 6.77.

Three rules keep the formula honest:

  1. Both ranges cover the same rows. Microsoft says the arrays "must have the same dimensions", or SUMPRODUCT returns #VALUE!.
  2. The bottom of the fraction sums the weights, never the values. there gives a meaningless figure.
  3. Numbers stored as text count as zero, because SUMPRODUCT "treats non-numeric array entries as if they were zeros". A price pasted from an export as text drops out without any error. Check with , which should return the number of rows.

Why does a plain average give the wrong answer?

AVERAGE treats every row as equally important. A product line that sold 20 units weighs as much as one that sold 900. In the example, returns 15.50, more than twice the 6.77 customers paid.

15.50 is the plain average of the six prices. Customers paid 6.77 per unit on average.

The gap comes from the Pro line. Its price of 30.00 counts for a third of the plain average, while it sold 50 of the 1,550 units. Any report that shows 15.50 as an average selling price overstates it.

The same trap shows up in three common places:

  • Averaging rates. Two regions with margins of 25% and 45% do not average 35% if one brings 80,000 of revenue and the other 20,000. Weighted by revenue, the margin is 29%.
  • Averaging averages. The North region's average price is 6.29 and the South's is 8.02. Their plain average is 7.16, not the 6.77 of all sales together. A yearly figure built from monthly averages hits the same problem, as shown in how to calculate average order value.
  • Averaging percentage changes. Growth rates from months of different sizes need the same care. The base matters, as explained in how to calculate percentage change in Excel.

How do you calculate a weighted average with a condition?

Multiply the condition into SUMPRODUCT, and use SUMIFS for the weights. For the North region, returns 6.29.

Weighted average price for North, 6.29
=SUMPRODUCT((A2:A7="North")*C2:C7*D2:D7)/SUMIFS(C2:C7,A2:A7,"North")

The test gives TRUE or FALSE on each row. Multiplied by the other ranges, TRUE counts as 1 and FALSE as 0, so only the North rows add to the total. Microsoft's SUMPRODUCT page uses the same pattern to total sales for one region and one item Microsoft Support.

SUMIFS takes the sum range first, then each criteria range and its criterion Microsoft Support. For a second condition, add a second test inside SUMPRODUCT and a second pair inside SUMIFS. Keep the region in a cell, for example F2, and write and , so one formula serves every region.

North comes out at 6.29 and South at 8.02. South sold a larger share of Pro units, 30 of 430, which lifts its average price.

How do you show a weighted average in a pivot table?

Add a helper column to the source and divide two sums with a calculated field. Excel's Average summary gives the plain average, so it can't do this on its own. With a Revenue column of , a calculated field of returns the weighted average for each row of the pivot table.

  1. Add a column named Revenue to the source table, with on each row.
  2. Refresh the pivot table so the new field appears.
  3. Select PivotTable AnalyzeFields, Items, & SetsCalculated Field.
  4. Name it "Average price" and type as the formula.

The question comes up on Excel forums, where users find that "Average of" in a pivot table returns a flat average of the averages Chandoo forum.

It works because "formulas for calculated fields operate on the sum of the underlying data for any fields in the formula" Microsoft Support. For North, the field divides 7,050 of revenue by 1,120 units and shows 6.29. The grand total shows 6.77.

The same rule breaks the shortcut. A calculated field of multiplies total units by the total of the prices, 1,550 × 93.00, and returns 144,150. Always build the product in the source, then divide sums in the pivot table. Pivot tables that ignore new rows have their own fixes in how to create a pivot table and group it by month.

How do you calculate a weighted average in Google Sheets?

Use AVERAGE.WEIGHTED, with the values first and the weights second. In the example, returns 6.77, the same as the SUMPRODUCT formula, which also works in Sheets.

Google's help page states that weights "cannot be negative, though they can be zero", that at least one must be positive, and that the weight range must match the value range in size Google Docs Editors Help. Excel has no such function, so SUMPRODUCT stays the formula to know.

Where does Glow Reports fit?

The formulas above are all you need for one table. The work grows when the weighted average is one figure among twenty in a monthly report, on a fresh export each time, and someone has to check every range again.

Glow Reports starts from that export. You upload the CSV or Excel file and ask, in plain language, for the average price by region weighted by units. The AI writes the SQL, which divides the sum of revenue by the sum of units for each group, builds the chart and drafts the text. The SQL sits under each figure, so you can confirm the weighting and edit it if the wrong column was used. Reports export to PDF. The free plan gives 300 credits once, and a report costs 100 credits.

Common questions

What is the difference between an average and a weighted average?

A plain average gives every value the same importance. A weighted average gives each value an importance set by its weight, such as units sold or revenue. The two match only when every weight is equal.

Do the weights have to add up to 100%?

No. The formula divides by the sum of the weights, so raw counts such as units work as well as percentages. Weights of 900, 200 and 20 give the same result as the same weights turned into shares of 1,120.

Is there a WEIGHTED.AVERAGE function in Excel?

No. Excel has no built-in weighted average function. does the job. Google Sheets has AVERAGE.WEIGHTED.

How do I calculate a weighted average percentage?

Weight each rate by its base. For margins, use revenue as the weight: . The result equals total margin divided by total revenue.

El Mehdi Benane
Founder, Glow Reports

I am a product engineer and the founder of Glow Reports. I spent more than 6 years building products in tech, data and cybersecurity, for large groups, startups and SMEs. In most organizations I saw data treated as a technical topic, when it drives business decisions. I built the reporting tool I was looking for, so the people who run small and mid-sized organizations can read their own numbers without a data team.

Upload your export to Glow Reports and get weighted averages by group, with the SQL behind each figure.

Keep reading