Try the example, then check the result.
Download the fictional material and follow the steps below. The video uses English illustrative diagrams; it does not present a live app recording. Product controls may vary by version.
Check the answer before applying the method to your own data. How we review guides ↗
Watch the complete lesson.
The video uses English illustrative diagrams; it does not present a live app recording. English captions. Product menus may differ in your account.
Quick answer. For quantity in B2:B4 and price per unit in C2:C4, enter =SUMPRODUCT(B2:B4,C2:C4)/SUM(B2:B4). This practice totals $162 over 15 units, so the average price paid is $10.80, not the simple three-price average of $10.
Use the fictional practice file: purchase-batches-practice.csv, plus the independent answer check. Work in a copy. This guide stands on its own without the video.
Scope and limits. Units must be numeric and positive for this price-per-unit interpretation. SUMPRODUCT ranges must have the same dimensions. This fixed three-row formula must be extended when new batches are added; verify both total units and total cost.
1. Check the three fictional batches
Open purchase-batches-practice.csv in an Excel workbook copy. Read B2:B4 as 2, 8, 5 units and C2:C4 as $8, $12, $10 per unit. Pair each quantity with the price on the same row.
Checkpoint: Batch costs: $16, $96, $50.
2. See why ordinary AVERAGE misleads
An optional =AVERAGE(C2:C4) yields $10. That answers a different question: the mean of three batch price labels. Do not use it as the per-unit cost across all 15 units.
Checkpoint: Simple price-label average is $10.
3. Divide total cost by total units
In an empty result cell enter =SUMPRODUCT(B2:B4,C2:C4)/SUM(B2:B4). The two SUMPRODUCT ranges must have matching row counts. Format the result as currency with two decimals, but retain the numeric underlying value.
Checkpoint: Formula result: $10.80.
4. Reconcile numerator and denominator
Independently add the row costs to get $162 and the units to get 15. $162 divided by 15 is $10.80. If either subtotal differs, inspect the original batch rows before changing the formula.
Checkpoint: $162 total cost / 15 units = $10.80.
Verify the result
Batch costs are A: 2×$8=$16, B: 8×$12=$96, C: 5×$10=$50. Total cost is $162 and units total 15. Weighted average price = $162/15 = $10.80 per unit. The unweighted AVERAGE of 8, 12, 10 is $10 and does not describe all 15 units equally.
If it still does not work
If #VALUE! appears, check that B2:B4 and C2:C4 have the same length and contain numeric entries. If #DIV/0! appears, total units are zero or missing. Do not replace the denominator with the count of batches.
Official sources
- Calculate an average (checked 26 October 2026).
- SUMPRODUCT function (checked 26 October 2026).
Any instructional panel without a marked actual-app capture is an illustrative diagram of the fictional exercise.
Practice downloads.
The exercise files contain invented information. Save a local copy before editing.