Units Buildable from a Bill of Materials

Excel Formulas › Manufacturing & Operations

All versionsMIN

How many finished units can you build from what’s on hand? Each component limits production to stock ÷ per-unit need; the smallest of those limits — the constraint — is the answer.

Quick formula: max units the most-limiting part allows:
=MIN(ROUNDDOWN(stock_each / qty_per_unit, 0))
For each component, stock divided by per-unit need (rounded down); the minimum is the buildable count.

Functions used (tap for the full reference guide):

MINROUNDDOWN

The example

Limited by the scarcest part.

AB
1PartBuilds
2Screws 400 / 850
3Panels 60 / 160 → min 50

The formula

The formula:

=MIN(ROUNDDOWN(stock / qty_per_unit, 0)) // the limiting component

How it works

How it works:

  1. For each component, stock / qty_per_unit is how many units that part alone supports.
  2. ROUNDDOWN(…, 0) — you can’t build a partial unit.
  3. The MIN across all components is the constraint — the most you can build.
  4. The part hitting that minimum is the one to reorder first.

The constraint names your shortage. Whichever component produces the smallest stock/per-unit is the bottleneck part — building more requires restocking it first, not the parts you have plenty of. An INDEX/MATCH on the MIN identifies the limiting part by name, turning the build calc into a procurement priority.

Try it: interactive demo

Live demo

Stock and per-unit need for two parts.

Buildable:

Variations

Per-component builds

One part:

=ROUNDDOWN(stock / qty_per_unit, 0)

Limiting part

Name it:

=INDEX(parts, MATCH(MIN(builds), builds, 0))

Shortfall to a target

How much to order:

=MAX(target * qty_per_unit - stock, 0)

Pitfalls & errors

Round down. Partial units don’t count — ROUNDDOWN each component.

MIN, not SUM. The scarcest part limits the build, not the total stock.

Match per-unit needs. Use the correct quantity-per-unit from the BOM for each part.

Practice workbook

📊
Download the free Units Buildable from a Bill of Materials practice workbook
A BOM-build sheet with the per-component, limiting-part, and shortfall variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I find how many units I can build from inventory in Excel?
For each part, =ROUNDDOWN(stock / qty_per_unit, 0); the MIN across parts is the buildable count.
Which part should I reorder first?
The one producing the minimum: =INDEX(parts, MATCH(MIN(builds), builds, 0)).
Why MIN and not the total stock?
The scarcest component constrains the build — you can't substitute surplus of one part for shortage of another.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Min if criteria · Reorder point · Economic order quantity

Function references: MINROUNDDOWN