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.
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
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.
Enter your figures
Put each total cost in column B and the units in column C. Do not type the unit cost by hand.
Divide
In D2 enter =B2/C2. That is total cost divided by units.
Fill the column
Select D2 and drag the fill handle down the other rows.
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
The units cell is 0 or blank. Enter the units, or use =IF(C2=0,"",B2/C2).
The cell was formatted as Text before you typed the formula. Set General, press F2, then Enter.