📎 This article includes 1 downloadable practice file ↓
If you buy 10 keyboards at ₹1,299 and 2 monitors at ₹11,499, the average price per item is not the average of 1,299 and 11,499. Ten keyboards should count ten times. That’s a weighted average, and plain AVERAGE gets it wrong every time the weights differ.
In this article
The formula
=SUMPRODUCT(H2:H201, G2:G201) / SUM(G2:G201)Price × quantity for every row, added up, divided by total quantity. In words: total value ÷ total units.
| Item | Qty | Price |
|---|---|---|
| Keyboard | 10 | 1,299 |
| Monitor | 2 | 11,499 |
AVERAGE(price) = 6,399. Weighted average = (10×1,299 + 2×11,499) ÷ 12 = 3,000.50 — the real average price per unit.
Weighted average with a condition
=SUMPRODUCT((B2:B201="North") * H2:H201 * G2:G201) / SUMIFS(G2:G201, B2:B201, "North")Multiply in the condition so other regions contribute zero, and divide by the quantity of North only.
Weighted marks
Assignment 20%, mid-term 30%, final 50%, with weights in B1:D1 and a student’s marks in B2:D2:
=SUMPRODUCT(B2:D2, $B$1:$D$1) ' weights add to 100%
=SUMPRODUCT(B2:D2, $B$1:$D$1) / SUM($B$1:$D$1) ' weights in any unitsBlended interest rate
Three loans of ₹5 lakh at 8.5%, ₹2 lakh at 11% and ₹1 lakh at 14%:
=SUMPRODUCT(Amounts, Rates) / SUM(Amounts) ' 9.81%Where people go wrong
- Using AVERAGE on prices or rates that apply to different quantities — always ask “weighted by what?”.
- Mismatched ranges — SUMPRODUCT ranges must be the same size, or you get #VALUE!.
- Text numbers in the weights are treated as 0 by SUMPRODUCT. Convert them first.
- Dividing by the wrong total — with a condition, divide by the conditional total (SUMIFS), not the grand total.
Practice
Download the combo practice workbook below: a Sales sheet of 200 orders plus tasks for the formulas on this page, each with an automatic ✓ check and an Answers sheet.
More: all formula combos · Excel function course.
📎 Practice files for this article
- 📗Formula combinations practice workbook50 tasks: lookups, running totals, ranks, top-N, unique counts, FY, age, text extraction, list comparison, weighted averages, ageing, rolling totals, duplicates and working days — with automatic checks.⬇ XLSX · 39 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
Stuck on a step? Ask a question and the AI answers using this article.