Weighted Average
Free Weighted Average: calculate a weighted average with =SUMPRODUCT(A2:A10,B2:B10)/SUM(B2:B10), where column A holds the values and column B their...
3 value/weight pairs · Unweighted mean: 80.0000
How it works
In Excel or Google Sheets, calculate a weighted average with =SUMPRODUCT(A2:A10,B2:B10)/SUM(B2:B10), where column A holds the values and column B their weights — plain AVERAGE ignores the weights entirely.
Put values in one column and their weights in the next, row-aligned.
Compute =SUMPRODUCT(values,weights)/SUM(weights).
Check: with all weights equal, the result must match plain AVERAGE.
Keep the ranges the same height, or SUMPRODUCT returns #VALUE!.
What this does
A weighted average counts some values more than others: each value is multiplied by its weight, the products are summed, and the total is divided by the sum of the weights. SUMPRODUCT does the multiply-and-sum in one step. Dividing by SUM(weights) makes the formula correct even when the weights do not add up to 1 or 100%.
A worked example
Grades 90, 80, 70 carry weights 50%, 30%, 20%. =SUMPRODUCT({90;80;70},{0.5;0.3;0.2})/SUM({0.5;0.3;0.2}) returns 83. The unweighted AVERAGE would say 80 — the heavier weight on the 90 pulls the true result up by three points. Course grades, portfolio returns, average purchase prices, survey scores — whenever observations matter unequally, the weighted average is the honest summary number.
Common mistakes
- Using
AVERAGEand silently ignoring the weights. - Skipping the division by
SUM(weights)when weights do not sum to 1. - Misaligned ranges (
A2:A10againstB2:B9) causing#VALUE!. - Blank weight cells counting as zero without anyone noticing the row dropped out.
FAQ
What is the weighted average formula in Excel?
=SUMPRODUCT(values,weights)/SUM(weights). There is no built-in WEIGHTEDAVERAGE function.
Do the weights have to add up to 100%?
No — the division by SUM(weights) normalizes any positive weights.
How do I do a weighted average in a pivot table?
Add a helper column value×weight, sum both in the pivot, and divide via a calculated field.
Built in your browser when you click. Nothing is uploaded, no signup, free for commercial use.