Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to calculate the break-even point in units
Topic:Finance basicsExcel & Google Sheets
=IF(B2<=C2,"No finite break-even",ROUNDUP(A2/(B2-C2),0))✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| Fixed costs | Price per unit | Variable cost per unit |
| 10000 | 50 | 30 |
=IF(B2<=C2,"No finite break-even",ROUNDUP(A2/(B2-C2),0))→500
Both expressions are checked against an independently calculated expected result in HyperFormula. This is a sample execution check, not native Excel or Google Sheets certification.
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Enter nonnegative fixed costs in A2 and comparable unit price and variable cost in B2:C2.
- 2Check that price exceeds variable cost before dividing. The example contribution margin is 50 - 30 = 20.
- 3Place the formula outside A1:C2. Divide 10000 by 20 to obtain 500.
- 4ROUNDUP returns the whole-unit sales target when the quotient is fractional; keep a decimal quotient only when fractional units can actually be sold.
Tips
- Assume a constant price, unit variable cost and fixed-cost amount over the relevant volume range. Sales mix, capacity constraints and tiered costs need a different model.
- For positive fixed costs, a zero or negative contribution margin cannot cover them at any positive volume. The formula reports that condition instead of hiding #DIV/0! as zero.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
Does 500 mean revenue?
No, it is a unit count. At the sample price, 500 units produce 25,000 of revenue, of which 10,000 covers fixed costs after variable costs.
Why round up?
A target of 166.67 units requires 167 whole units to cover the costs if partial units cannot be sold.
More on Finance basics
See all →How-to
How to calculate compound interest
Calculate a compounded closing balance from principal, annual nominal rate, compounding frequency and years, then separate the interest earned.
How-toHow to calculate the effective annual interest rate
Convert a positive nominal annual rate and a whole-number compounding frequency into an effective annual rate with EFFECT.
How-toHow to calculate tiered commission
Apply different commission rates by sales tier using IFS. Works in Excel 2019/365 and Google Sheets.
How-toHow to calculate net pay
Calculate take-home (net) pay by subtracting every deduction from gross salary, with worked examples for fixed amounts, percentage tax, and slab-based tax. Excel and Google Sheets.
How-toHow to calculate gross margin
Compute gross margin percentage as (price − cost) / price. Identical in Excel and Google Sheets.
How-toHow to calculate cost per unit
Divide total cost by total units to find cost per unit using a simple formula in Excel or Google Sheets.
Sources and compatibility
Reviewed 2026-09-16