How to Calculate Cost per Unit in Excel

To calculate cost per unit in Excel, divide the total cost by the number of units. The formula is one line, =B2/C2, but real costs add a few twists: shipping and fees, rounding to cents, zero quantities, and averages across several purchases. This guide covers each one with a small product table, so you can price stock, compare suppliers or answer a test question with confidence.

The basic cost per unit formula#

Cost per unit is the total cost divided by the number of units:

=B2/C2

If you paid 2,380 for 720 mugs, =2380/720 gives about 3.31 per mug. Use cell references instead of typing the numbers, so the result updates when the data changes. Then fill the formula down the column (Windows: Ctrl+D; Mac: ⌘D) and each row divides its own cost by its own units.

Every formula starts with =, and Excel's division sign is the forward slash /. If those are new to you, the first formulas lesson is the place to start.

How to include shipping and other costs#

The true cost of a product, often called the landed cost, includes everything you paid to get it: purchase price, shipping, import duty and fees. Add them together before dividing:

In E2:

=(B2+C2)/D2

For the mugs, (2,380 + 215) / 720 = 2,595 / 720 = 3.604... The brackets matter. Without them, =B2+C2/D2 divides only the shipping by the units and then adds the full purchase cost, giving 2,380.30, which is clearly wrong. Excel does multiplication and division before addition and subtraction, so use brackets whenever you add costs first.

If you have many cost columns, use SUM: =SUM(B2:C2)/D2.

How to round cost per unit to cents#

Use ROUND to store the result to two decimal places:

=ROUND((B2+C2)/D2,2)

The 2 means two decimal places, so 3.604... becomes 3.60. For the tote bags, 1,700 / 450 = 3.777... rounds to 3.78.

Rounding is not the same as formatting. A cell formatted to show two decimals still holds 3.604166..., and later calculations use that full value. That's fine for reports, but if you're setting prices or matching an invoice, ROUND makes the stored value match what you see. If you must never under-price, use ROUNDUP instead: =ROUNDUP((B2+C2)/D2,2) gives 3.61. The ROUND function guide and the guide to ROUND, ROUNDUP and ROUNDDOWN explain the differences.

How to avoid #DIV/0! when units are zero#

If the units cell is 0 or empty, the formula returns #DIV/0!, because nothing can be divided by zero. This happens with new products, cancelled orders or rows waiting for data. Two fixes:

=IFERROR(ROUND((B2+C2)/D2,2),"")
=IF(D2=0,"",ROUND((B2+C2)/D2,2))

The first shows a blank for any error. The second checks the units directly, so other mistakes, such as text in a cost cell, still show up. When you're cleaning up someone else's sheet, the IF version is safer because it doesn't hide problems. The IFERROR function guide and how to fix #DIV/0! cover more cases.

How to find the average cost per unit across purchases#

When you buy the same item several times at different prices, the average cost per unit is total cost divided by total units. Don't average the unit costs.

With units in B2:B4 and costs in C2:C4:

=SUM(C2:C4)/SUM(B2:B4)

This gives 2,500 / 1,000 = 2.50. If you used =AVERAGE(D2:D4) instead, you'd get 2.77, because the small April batch counts as much as the big May batch. That's a weighted average problem; the guide to weighted averages in Excel shows the SUMPRODUCT method too.

How to include fixed costs#

Some costs don't change with volume, such as rent or a machine lease. Spread the fixed cost across the units, then add the variable cost of each unit:

=Fixed/Units+Variable

With a fixed cost of 3,000 a month and a variable cost of 4.20 per unit:

Put the fixed and variable costs in their own cells and lock them with $ signs, such as =$G$1/A2+$G$2, so you can fill the formula down a list of unit counts. The more you make, the lower the cost per unit, which is why break-even and pricing models start here.

Using cost per unit in other calculations#

Cost per unit feeds straight into the numbers managers ask for:

• Profit per unit: price minus cost per unit, such as =F2-E2.
• Margin: profit per unit divided by price, =(F2-E2)/F2. The guide to the profit margin formula covers margin vs markup.
• Other per-unit measures: price per square foot, cost per hire or cost per click all use the same division. The guide to calculating ratios shows how to present them.

The real estate case study practises exactly this idea with price per square foot, a common task in analyst tests.

Common cost per unit mistakes#

Most wrong answers come from the order of the maths or the data, not the division itself:

• Dividing the wrong way round. Units divided by cost gives units per dollar, not cost per unit. The cost always goes on top.
• Missing brackets. =B2+C2/D2 divides only the shipping. Write =(B2+C2)/D2.
• Averaging unit costs across batches. Divide total cost by total units instead.
• Units stored as text. Imported quantities can be text, which gives #VALUE! or wrong totals. Convert them to numbers first.
• Rounding too early. Round the final cost per unit, not each cost before you add them, or small errors add up.

Practise it#

Try the exercise on this page: work out a landed cost per unit and round it to cents. Then practise ROUND, ROUNDUP and ROUNDDOWN in a live spreadsheet, and put per-unit maths to work in the real estate case study, where you calculate price per square foot like an analyst. Each step is checked as you type.