ElyxAI
finance

How to Create Cost Per Unit Production Calculator in Excel

Cost per unit equals total cost divided by the number of units. Put the total cost in B2 and the units in C2, then enter =B2/C2 in D2.

Shortcut:Ctrl+Shift+U (for currency formatting)
Excel 2016Excel 2019Excel 365Excel Online

If the units cell is 0, the division returns #DIV/0!. Guard it with IF.

Why This Matters

The unit cost is the total cost divided by the number of units. The cell format does not change that result.

Prerequisites

  • •Basic Excel skills including cell selection and data entry
  • •Understanding of basic mathematical operations (division, addition, multiplication)
  • •Familiarity with production or cost accounting terminology

Step-by-Step Instructions

1

Label the columns

In row 1, enter the item in A, the total cost in B, the units in C, and cost per unit in D.

2

Enter your figures

Put each total cost in column B and the units in column C. Do not type the unit cost by hand.

3

Divide

In D2 enter =B2/C2. That is total cost divided by units.

4

Fill the column

Select D2 and drag the fill handle down the other rows.

5

Watch a zero

If C2 is 0, Excel returns #DIV/0!. Use =IF(C2=0,"",B2/C2) when a row can have no units.

Alternative Methods

Several cost columns

Sum the cost columns in B, for example =E2+F2, and divide that cell by the units. Do not divide each cost column on its own unless you want a cost per unit for that column only.

Tips & Tricks

  • ✓Copy D2 by the fill handle so each row divides its own cost by its own units.

Pro Tips

  • ★A currency format and a lower decimal count change the display. =ROUND(B2/C2,2) changes the stored value. Those are different steps.

Troubleshooting

#DIV/0!

The units cell is 0 or blank. Enter the units, or use =IF(C2=0,"",B2/C2).

The result looks like text

The cell was formatted as Text before you typed the formula. Set General, press F2, then Enter.

Related Excel Formulas

Frequently Asked Questions

What if the units are zero?
=B2/C2 returns #DIV/0!. Use =IF(C2=0,"",B2/C2).
Where do fixed and variable costs go?
Add them in the total-cost cell first. Then divide that total by the units. The formula stays =B2/C2.
Does a currency format change the result?
No. Home > Number > Currency changes the display. The stored value is still the division.

Don’t want to do it by hand? Ask Elyx

Example request

“Create cost per unit production calculator in my workbook”

Elyx does it directly in your Excel file.

7 days free · No credit card required