The formula
Average entry priceExcelGoogle Sheets
=SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4)In E2 → $56,000.00
Total costExcelGoogle Sheets
=SUMPRODUCT(B2:B4,C2:C4)In E3 → $16,800.00
Average of prices (wrong for DCA)ExcelGoogle Sheets
=AVERAGE(B2:B4)In E4 → $58,000.00
✓ 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 | Buy | Price | Qty | ||
| 2 | 1 | 64,000 | 0.05 | $56,000.00 | |
| 3 | 2 | 58,000 | 0.1 | $16,800.00 | |
| 4 | 3 | 52,000 | 0.15 | $58,000.00 |
How it works
- SUMPRODUCT(B2:B4,C2:C4)
- Total spent: price × qty for each buy, added up.
- SUM(C2:C4)
- Total quantity.
Common errors and fixes
Average looks too high?
You averaged the prices. Larger buys at lower prices pull the true average down.
Why does the formula return #DIV/0!?
Quantity column is empty.
Does this work in Google Sheets?
Yes. The formulas on this page work the same way in Google Sheets.