Average values weighted by quantity, score, or any weight column.
Copy this formula
=SUMPRODUCT(B2:B10, C2:C10)/SUM(C2:C10)SUMPRODUCT multiplies each value by its weight and sums the results; dividing by the total weight gives the weighted average. It's the standard way to average grades, prices, or ratings by volume.
=SUMPRODUCT(Price, Qty)/SUM(Qty)→12.40Average price weighted by quantity sold.
=SUMPRODUCT({80,90,70}, {0.5,0.3,0.2})→81Grades of 80, 90 and 70 weighted 50%, 30% and 20%.
=SUMPRODUCT({10,20}, {1,3})/SUM({1,3})→17.520 counts three times as much as 10, so the average moves toward 20.
AVERAGE treats every row equally. A weighted average lets larger quantities count more, which is usually what you want for prices or grades.
No. Dividing by the SUM of the weights normalizes them, so any positive weights work. If they already total 1, you can skip the division.
No. Excel has no weighted-average function, so SUMPRODUCT divided by SUM is the standard way. Google Sheets has AVERAGE.WEIGHTED.
The two ranges are different sizes. SUMPRODUCT needs both ranges to have the same number of rows and columns.
Need a formula for your own data?
Describe what you want in plain language and let ExcelGPT write, explain, and verify the formula — free to start, no plugin.