How is DPMO calculated?

DPMO is defects divided by opportunities, times one million, where opportunities are units inspected times opportunities per unit. With 12 defects found in 100 boards of 400 opportunities each (40,000 opportunities), DPMO is 12 / 40,000 x 1,000,000 = 300, the same as the Defects per Million Opportunities calculator.

What counts as an opportunity?

One way a unit can fail that your inspection checks, counted once per product with one written rule. For printed board assemblies, IPC-7912A describes counting component, placement and termination (solder joint) opportunities. Counting things you never inspect inflates the denominator and makes DPMO look better than it is, so keep the rule consistent across products.

Why does the sigma level include a 1.5 shift?

It is the Six Sigma reporting convention: the long-term defect rate is converted to Z and 1.5 is added, so 3.4 DPMO reads as 6.0 sigma. The shift is a convention, not a measurement of your process. The workbook lets you set it on Setup (0 for no shift) and always shows the unshifted Z. For 18 defects in 48,000 opportunities (375 DPMO), Z is 3.37 and the reported sigma level with the shift is 4.87.

What is the difference between DPU and DPMO?

DPU is defects per unit; DPMO divides by opportunities instead. A complex board with 2,000 opportunities and 0.5 DPU (250 DPMO) can be better built than a simple board with 500 opportunities and 0.2 DPU (400 DPMO). DPMO lets you compare them on one scale; DPU tells you how much rework each unit carries.

Why is the sigma level blank for some lots?

When a lot has no defects the defect rate is zero and the normal-equivalent Z is unbounded, so the workbook leaves the sigma level blank instead of showing a made-up number. The weekly, line and product roll-ups still include those lots' opportunities, so they lower DPMO as they should.

Quality and Inspection

DPMO and Sigma Level Tracker Excel Template

Log every inspected lot and its defects, and see DPU, DPMO and the sigma level by week, line and product against your DPMO target, with the reporting shift stated, not hidden.

Free, and a plain .xlsx with no macros to enable. A CSV with live formulas and a printed how-to guide come with it.

Overview

This DPMO tracker is a working Excel workbook for quality engineers and line leaders who count defects against defined opportunities, typically on SMT and electronics assembly lines. It ships with six weeks of example inspection results for a fictional contract manufacturer with three SMT lines and five board types, so the tiles, charts and tables are populated when you open it; replace the example rows with your own and everything recalculates.

Each lot row holds the units inspected and the defects found. Opportunities per unit are counted once per product on Setup, with one written rule such as parts plus solder joints, so the workbook can calculate each lot's opportunities, defects per unit, DPMO and sigma level. The sigma level is the standard-normal Z with the same tail area as the defect rate, plus the reporting shift you declare: 1.5 by the Six Sigma convention, which makes 3.4 DPMO read as 6 sigma, or 0 if you prefer the unshifted value. The Dashboard always shows the unshifted Z next to it.

In the example, the plant runs at about 240 DPMO against a 300 target, but week 2 jumps above 400 because of a feeder problem on SMT 2, the sigma-by-week chart shows the dip and the recovery, and the line chart shows SMT 2 and SMT 3 above target over the six weeks. Every roll-up sums defects and opportunities before dividing, so a busy week or a complex board weighs exactly as much as its opportunities, never as an average of lot percentages.

What this template includes

Inspection Log table: date, line, product, units inspected and defects found, one row per inspected lot
Products list with opportunities per unit, so every row calculates its opportunities, DPU, DPMO and sigma level automatically
Sigma level from the inverse normal of the defect rate plus a reporting shift you declare on Setup (1.5 by convention, 0 for unshifted), with the unshifted Z shown beside it
DPMO status against your own target in words, and a Check column that names problems: product not on the list, defects above opportunities, possible duplicate lot
Dashboard with line and date filters and eight tiles: DPMO, sigma level, DPU, opportunities inspected, the highest-DPMO line and product, lots inspected and the share of lots above target
Four native charts: DPMO by week, by line and by product against the target line, and the reported sigma level by week
Weekly, by-line and by-product tables, each forming DPMO from summed defects and summed opportunities
Method sheet with every formula, and a Verify sheet that re-checks the formulas against an independent reference (Self-check: PASS)
CSV with live formulas for the inspection log; the dashboard, charts, dropdowns and self-check are in the .xlsx

Suggested use case

Use it to track AOI and ICT results on SMT lines, to compare lines or products of different complexity on one scale, or to report DPMO and sigma level in a monthly quality review.

How to use it

On Setup, enter your plant name, your DPMO target and the reporting shift, then replace the example lines and products with your own and each product's opportunities per unit.
On Inspection Log, add one row per inspected lot with the date, line, product, units inspected and defects found.
Fix anything the Check column flags; a product missing from the Setup list has no opportunities.
Open the Dashboard and read DPMO and sigma level against your target, by week and by line.
Filter by line or date range to review one line, or the week a problem started.

Frequently Asked Questions

How is DPMO calculated?: DPMO is defects divided by opportunities, times one million, where opportunities are units inspected times opportunities per unit. With 12 defects found in 100 boards of 400 opportunities each (40,000 opportunities), DPMO is 12 / 40,000 x 1,000,000 = 300, the same as the Defects per Million Opportunities calculator.
What counts as an opportunity?: One way a unit can fail that your inspection checks, counted once per product with one written rule. For printed board assemblies, IPC-7912A describes counting component, placement and termination (solder joint) opportunities. Counting things you never inspect inflates the denominator and makes DPMO look better than it is, so keep the rule consistent across products.
Why does the sigma level include a 1.5 shift?: It is the Six Sigma reporting convention: the long-term defect rate is converted to Z and 1.5 is added, so 3.4 DPMO reads as 6.0 sigma. The shift is a convention, not a measurement of your process. The workbook lets you set it on Setup (0 for no shift) and always shows the unshifted Z. For 18 defects in 48,000 opportunities (375 DPMO), Z is 3.37 and the reported sigma level with the shift is 4.87.
What is the difference between DPU and DPMO?: DPU is defects per unit; DPMO divides by opportunities instead. A complex board with 2,000 opportunities and 0.5 DPU (250 DPMO) can be better built than a simple board with 500 opportunities and 0.2 DPU (400 DPMO). DPMO lets you compare them on one scale; DPU tells you how much rework each unit carries.
Why is the sigma level blank for some lots?: When a lot has no defects the defect rate is zero and the normal-equivalent Z is unbounded, so the workbook leaves the sigma level blank instead of showing a made-up number. The weekly, line and product roll-ups still include those lots' opportunities, so they lower DPMO as they should.