Weighted AVERAGE
A weighted average is not a function you have failed to find; it is a two-part expression, and both parts have to be there. SUMPRODUCT multiplies each value by its weight and totals the products, and dividing by the total of the weights is what makes the answer independent of whether those weights are counts, percentages or scores out of anything. Skip that division and the result scales with the weights rather than the values. Plain AVERAGE belongs only where every observation genuinely counts the same, which is precisely the case this hub is not about.
The formula
=SUMPRODUCT(A2:A10,B2:B10)/SUM(B2:B10)weighted average: values in A2:A10, weights in B2:B10=SUMPRODUCT(A2:A10,B2:B10)/SUM(B2:B10)*100same result shown as a percentage, e.g. a weighted score out of 100=(E2/(E2+D2))*F2+(D2/(E2+D2))*G2*(1-H2)WACC: E2/D2 equity & debt value, F2/G2 their cost, H2 the tax rateA worked example
You bought 50 shares at $90, 30 shares at $80, and 20 shares at $70; prices sit in A2:A4 and share counts in B2:B4.
=SUMPRODUCT(A2:A4,B2:B4)/SUM(B2:B4)
$83.00 — the price weighted by how many shares you bought at each level. Plain AVERAGE(A2:A4) would say $80.00 and ignore that more shares were bought at the higher price.
Which one do I need?
| If you want to… | Use |
|---|---|
| Weights are counts or amounts (units bought, votes cast) | =SUMPRODUCT(values,weights)/SUM(weights) — works whether the weights sum to 100 or not |
| Weights are already percentages that add up to 100% | Same formula — dividing by SUM(weights) normalizes it automatically, so 50/30/20 and 50%/30%/20% give an identical result |
| Computing a company's WACC specifically | =(E2/(E2+D2))*F2+(D2/(E2+D2))*G2*(1-H2) — E2/D2 hold the market value of equity and debt, F2/G2 their cost, H2 the tax rate |
| Some weight cells are blank | SUMPRODUCT treats a blank weight as 0, which undercounts that row instead of excluding it — fill in a real weight or delete the row |
| You need it inside a PivotTable | Add a helper column of value×weight, summarize both by sum, then divide via a calculated field — PivotTables cannot average by weight directly |
Frequently asked questions
Is there a WEIGHTEDAVERAGE function in Excel?
No. Neither Excel nor Google Sheets has a built-in WEIGHTEDAVERAGE function. =SUMPRODUCT(values,weights)/SUM(weights) is the standard formula and the correct, complete answer for any weighted average.
Why does AVERAGE give a different number than the weighted average?
AVERAGE treats every value equally regardless of its weight, so it understates high-weight values and overstates low-weight ones. Whenever some observations should count more than others, AVERAGE is the wrong function — use the SUMPRODUCT formula instead.
Does the weighted average formula work the same in Google Sheets?
Yes. =SUMPRODUCT(values,weights)/SUM(weights) is identical in Google Sheets — no function names or arguments change.
What's the difference between a weighted average and WACC?
WACC is a specific weighted average: it blends the cost of equity and the after-tax cost of debt, weighted by each one's share of total capital value. The underlying SUMPRODUCT-style weighting is the same, just applied to two components instead of a column of values.
New guides and tools, once a month
DE + EN · double opt-in · no spam