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.

Purchase sheet showing unit prices and quantities on the left, with G2 and G3 currently empty on the right.
First identify the unit price, quantity, and result columns.

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.

Formula bar showing the SUMPRODUCT divided by SUM formula in G2, with G2 displaying 12 and G3 displaying 18.
After reopening the workbook, the weighted average in G2 is 12, and the simple average in G3 is 18.

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.

P02 quantity changes to 100, G2 updates to 13, and G3 remains 18.
Adjusting P02's quantity to match P01 weights both purchased batches equally.

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.

The Cue AI button on the Home tab and the opened Cue AI sidebar.
The Cue AI panel opens beside the purchase sheet.
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.

The reopened workbook shows the weighted-average formula in G2, a result of 12, and a blank G3.
Reopen the copy, select G2 to view the formula bar, and confirm G3 remains blank.

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.

Download Cue Office for Free