Working with averages
Weighted average in Excel: calculate unit cost with SUMPRODUCT
Three deliveries have different quantities and unit prices. Calculate the cost of one average unit, check it against the invoice totals, and handle a list with no units.
Start with the quantity behind each price
A workshop receives the same replacement component in three deliveries. The prices differ, and the largest delivery is also the most expensive. The question is what each component cost on average across all deliveries. Enter these records in A1:C4. All quantities use the same unit, and all prices use the same currency.
| Delivery (A) | Units (B) | Price per unit (C) |
|---|---|---|
| D-18 | 10 | 4 |
| D-19 | 30 | 6 |
| D-20 | 60 | 7 |
Multiply each price by its quantity, then divide
SUMPRODUCT multiplies corresponding cells in B2:B4 and C2:C4, then adds those products. Dividing by the total quantity turns the combined cost into a cost per unit.
=SUMPRODUCT(B2:B4,C2:C4)/SUM(B2:B4)Check the answer without a formula
The deliveries cost 10 × 4 = 40, 30 × 6 = 180, and 60 × 7 = 420. Together that is 640 for 100 units, so the weighted average is 6.4 per unit. Displaying two decimal places would show 6.40.
The unweighted calculation (4 + 6 + 7) / 3 is about 5.67. It gives the ten-unit delivery as much influence as the sixty-unit delivery. That answers a different question: the average of the three quoted prices, rather than the average cost of a component received.
Change one delivery to test the result
Change the first delivery from 10 units to 30. Total cost becomes 120 + 180 + 420 = 720, and quantity becomes 120. The weighted average falls to 6. More of the inexpensive component was received, so a lower average is sensible.
Restore the first quantity to 10, then set the last quantity to zero. Its price of 7 contributes nothing because no units were received. The remaining 40 units cost 220 in total: 5.5 per unit. These two changes test whether the quantities really affect the result.
Show an explicit message when there are no units
If all three quantities are zero, dividing by their sum has no meaningful result. Test the denominator before calculating. This version returns No units for that case. It does not replace every error with a blank, so unrelated problems remain visible.
=IF(SUM(B2:B4)=0,"No units",SUMPRODUCT(B2:B4,C2:C4)/SUM(B2:B4))Keep the inputs comparable
A price per box cannot be mixed directly with a quantity measured in individual components. Convert to a common unit first. Likewise, convert currencies consistently before combining costs.
Use numeric values in both input columns and keep the ranges aligned. In Excel, SUMPRODUCT treats non-numeric entries as zero; a missing price can therefore understate the total. Inspect incomplete rows rather than assuming that an answer means the data is complete.
This example assumes non-negative received quantities and excludes delivery charges. If you need landed cost, decide how those charges belong in the total. Returns, credits and inventory valuation require a defined policy rather than an unexplained negative quantity.
Keep the underlying precision for later calculations. Formatting the final cell to two decimals is different from rounding each delivery price before calculating.
Try the example
Use the original values or change them to test your understanding.