How to Calculate Weighted Average Unit Price in Excel
Open your Excel purchase sheet (.xlsx) in Cue Office. Use SUMPRODUCT to multiply each batch’s unit price by its quantity and add the batch costs. Divide that total by the number of units purchased to get the average cost per unit.
Calculate the average when batch quantities differ
In this purchase sheet, B2:B4 holds the unit prices and C2:C4 holds the matching quantities. A 100-unit batch counts twice as much as a 50-unit batch when you calculate the cost per unit.
Select G2 and paste both formulas below at once. The first calculates the weighted average in G2. The second takes the simple average of the three unit prices in G3.
=SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4) =AVERAGE(B2:B4)
In G2, SUMPRODUCT calculates the total cost (10 × 100 + 16 × 50 + 28 × 0 = 1,800). SUM adds the quantities to get 150, so the formula returns 12 per unit. In G3, AVERAGE adds the three unit prices and divides by 3, giving 18. Save and reopen the workbook to check both results.
Check what changes when a price or quantity moves
In a copy of the sheet, change P03's unit price in B4 from 28 to 34. Because this batch has a quantity of 0, it contributes 0 to both cost and unit count, so G2 remains 12. G3 includes 34, rising from 18 to 20.
Restore B4 to 28, then change C3 from 50 to 100. The two purchased batches now have 100 units each, so the prices of 10 and 16 carry equal weight and G2 updates to 13. The three prices stay the same, so G3 remains 18. After checking, restore C3 to 50.
If the total quantity is 0, the average cost per unit is undefined. Check whether any received quantities are missing. If there were no purchases that period, leave the average out of your report.
Have Cue AI write the weighted average in G2
Open a copy of the purchase sheet with G2 and G3 blank. Open Cue AI from the Home toolbar and send the prompt below.
Spread the total purchase cost across the actual number of items bought to give the overall cost per item. Use the unit prices and quantities already listed in B2:C4. Put one live result in G2 that updates if a price or quantity changes. Edit only G2 and keep all source data and labels unchanged.
In this example, Cue AI wrote the formula in G2 using SUMPRODUCT for total cost and SUM for total quantity. Save and reopen the copy. Check that G2 shows 12, G3 is blank, and the headers and source data are intact. To test recalculation, temporarily change C3 to 100 and check that G2 updates to 13. Restore C3 to 50 afterward.
Practice with three batches of purchase data
Show the full example
Create a new spreadsheet in Cue Office and paste the data below into A1. Leave G2 and G3 for the two formula lines above.
Batch Unit price Quantity Metric Result P01 10 100 Weighted unit price P02 16 50 Simple mean P03 28 0
Verify unit cost during month-end reconciliation
If the same item arrives in several shipments, ask Cue AI to write a weighted-average formula in a copy of your month-end purchase sheet using the quantities you actually received. Pick a large shipment and check that the formula pairs its unit price with its quantity. Add up the received quantities separately and check that the formula divides by that total.
If the purchase sheet disagrees with the warehouse record, open the PDF delivery slips (.pdf) in Cue Office for free and confirm the quantities before correcting the Excel sheet (.xlsx). In the Word reconciliation notes (.docx), record which shipment was added or corrected. Use the PowerPoint monthly report (.pptx) to show the revised cost, number of units, and average unit price. Cue Office lets you edit these three Office formats for free.