How to calculate a weighted average price when quantities differ
Find what you paid per unit when purchase quantities and prices differ. Calculate each product's total cost and divide it by the quantity purchased.
To find the average price paid for a product bought in different quantities, divide total purchase cost by total quantity. One unit at 10,000 and nine at 20,000 cost an average of 19,000 per unit. In JOIN SHEET, calculate each purchase's cost, summarize cost and quantity by product, then divide the totals.
Why averaging the two prices is different
A simple average gives each transaction price the same weight. That is unsuitable when the question is “How much did we spend per unit?” and transaction quantities differ.
These fictional purchases use a consistent amount unit and are calculated separately for each product.
| Product | Quantity | Unit price |
|---|---|---|
| P01 | 1 | 10,000 |
| P01 | 9 | 20,000 |
| P02 | 2 | 5,000 |
| P02 | 2 | 7,000 |
Calculate the purchase cost in JOIN SHEET, without adding a helper column to the source file. The result is a new table of product totals and weighted prices; it does not modify an existing Excel PivotTable.
Multiply, summarize, then divide
- Load and select the sheet. In Refine results, add Calculate. Choose Multiply · A × B, set A to Quantity and B to Unit price, and name the field Purchase cost.
- Add Summarize below it, grouping by Product. Change Summary method to Sum, select Purchase cost, and enter Total cost in Output field name.
- In that same summary, use Add summary to sum Quantity into Total quantity.
- Add Calculate after the summary. Choose Divide · A ÷ B, with Total cost as A and Total quantity as B. Name it Average unit price.
- Check the step order and choose Apply.
Check both totals behind each average
| Product | Total cost | Total quantity | Average unit price |
|---|---|---|---|
| P01 | 190,000 | 10 | 19,000 |
| P02 | 24,000 | 4 | 6,000 |
P02's equal purchase quantities make its simple and weighted averages equal. P01's unequal quantities do not. Retaining both totals makes the calculation easier to verify.
Why can missing prices make the average too low?
Check for missing prices before summarizing. A purchase with no price contributes no Purchase cost, but its Quantity may still be added to Total quantity. That can pull the average down. Correct the missing price or exclude the affected purchase from both totals, according to the report you need.
Choose Divide for a price per unit; Ratio (%) multiplies the answer by 100. If returns bring total quantity to zero, the result will be blank because there is no quantity to divide by. These figures use purchase prices only, so include any agreed allocation of tax or shipping in the source costs if your report needs them.
Try it with your own files
Start with JOIN SHEET ↗Use a desktop computer for Excel and CSV work.
Opens in a new tab without replacing your current work.
Example last verified:
Sources
Discussion about calculating a weighted average with complex helper columns — Reddit r/excel
The purchase quantities and prices are fictional examples of the weighted-average problem.
