The formula
Weighted average costExcelGoogle Sheets
=SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4)In E2 → $4.54
Simple average (for comparison)ExcelGoogle Sheets
=AVERAGE(B2:B4)In E3 → $4.30
Weighted average where qty ≥ 100ExcelGoogle Sheets
=SUMPRODUCT(B2:B4,C2:C4,--(C2:C4>=100))/SUMIFS(C2:C4,C2:C4,">=100")In E4 → $4.63
✓ Tested. All 3 Excel results in this example were recalculated in LibreOffice Calc 25.2 and matched the values shown.
Worked example
Paste the data below into a blank sheet starting at cell A1 (or download the cheat sheet, where every example is set up). Highlighted cells hold the formulas and show the expected results.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Item | Unit cost | Qty | ||
| 2 | Batch 1 | 4.2 | 100 | $4.54 | |
| 3 | Batch 2 | 4.8 | 250 | $4.30 | |
| 4 | Batch 3 | 3.9 | 50 | $4.63 |
How it works
- SUMPRODUCT(B2:B4,C2:C4)
- Sum of cost × quantity.
- SUM(C2:C4)
- Total quantity (the weights).
- --(C2:C4>=100)
- A 1/0 filter inside SUMPRODUCT.
Common errors and fixes
Why does the formula return #VALUE!?
The two ranges have different sizes.
Why does the formula return #DIV/0!?
All weights are zero.
Does this work in Google Sheets?
Yes. The formulas on this page work the same way in Google Sheets.