Quality & InspectionFree generatorPDF, Excel, Word export

Excel Formulas for Cp, Cpk, Pp, Ppk and Sigma Level

Most capability studies still end up in Excel, and most mistakes come from using the wrong standard deviation. This generator writes the formulas for Cp, Cpk, Pp, Ppk, expected PPM and sigma level, wired to your own cell references, so you can paste them straight into a workbook.

Fill in

Data range

All individual readings, in time order

USL cell

LSL cell

Average range (Rbar) cell

Average of the subgroup ranges

d2 constant cell

For example 2.326 for subgroups of 5

Mean cell

Sigma within cell

Sigma overall cell

Generated code

Download:

Summary statistics

F8 (mean):           =AVERAGE(B2:B126)
F10 (sigma overall): =STDEV.S(B2:B126)
F9 (sigma within):   =$F$5/$F$6
Count:               =COUNT(B2:B126)

Short-term capability (uses sigma within)

Cp:   =($F$2-$F$3)/(6*$F$9)
Cpu:  =($F$2-$F$8)/(3*$F$9)
Cpl:  =($F$8-$F$3)/(3*$F$9)
Cpk:  =MIN(($F$2-$F$8)/(3*$F$9),($F$8-$F$3)/(3*$F$9))

Long-term performance (uses sigma overall)

Pp:   =($F$2-$F$3)/(6*$F$10)
Ppk:  =MIN(($F$2-$F$8)/(3*$F$10),($F$8-$F$3)/(3*$F$10))

Expected defects and sigma level

Expected PPM above USL: =(1-NORM.S.DIST(($F$2-$F$8)/$F$10,TRUE))*1000000
Expected PPM below LSL: =NORM.S.DIST(($F$3-$F$8)/$F$10,TRUE)*1000000
Expected PPM total:     =(1-NORM.S.DIST(($F$2-$F$8)/$F$10,TRUE)+NORM.S.DIST(($F$3-$F$8)/$F$10,TRUE))*1000000
Observed PPM:           =(COUNTIF(B2:B126,">"&$F$2)+COUNTIF(B2:B126,"<"&$F$3))/COUNT(B2:B126)*1000000
Sigma level (long-term, with 1.5 shift): =NORM.S.INV(1-(expected PPM total cell)/1000000)+1.5

Control limits for an X-bar chart

Grand mean (Xbarbar): =AVERAGE(range of subgroup means)
UCL X-bar: =Xbarbar+A2*$F$5
LCL X-bar: =Xbarbar-A2*$F$5
UCL R:     =D4*$F$5
LCL R:     =D3*$F$5
(Look up A2, D3, D4 for your subgroup size: for n = 5 they are 0.577, 0 and 2.114.)

Uses NORM.S.DIST, NORM.S.INV and STDEV.S, available in Excel 2010 and later. Replace the placeholder in the sigma level formula with the cell holding the total expected PPM. Capability indices assume a stable, approximately normal process.

Automate capability reports

Optional: a short call about generating capability reports from ERP inspection data without manual copy and paste.

Optional. The tool and every export work without this. No spam.

How to use it

Put your individual readings in one column, in time order. Compute the subgroup ranges and Rbar on a side sheet, then enter the cell addresses in the fields. Copy each block of formulas into the matching cells. Short-term indices Cp and Cpk use sigma within, estimated as Rbar over d2. Long-term indices Pp and Ppk use the overall sample standard deviation from STDEV.S. Format index cells to two or three decimals.

Keep USL and LSL in their own labeled cells, not inside formulas
Use absolute references so formulas can be copied
Label every index as short-term or long-term

The formulas explained

Cp compares the specification width to the six-sigma spread and ignores centering. Cpk is the smaller of Cpu and Cpl, so it penalizes an off-center mean. Pp and Ppk are the same equations but with the overall standard deviation, which includes drift between subgroups. Expected PPM comes from the normal distribution tail areas outside each limit, and observed PPM simply counts the readings outside the limits, which is a useful check when the data are not normal.

Worked example

With USL 25.05, LSL 24.95, mean 25.004 and sigma within 0.0115, Cpu is 1.33 and Cpl is 1.57, so Cpk is 1.33. Cp is 0.10 divided by 0.069, about 1.45. If the overall sigma is 0.0150, Ppk drops to about 1.02, which tells you the process drifts between subgroups. That gap is the improvement opportunity: stabilize the mean before tightening spread.

Common mistakes

Using STDEV.S for Cpk and calling it short-term is the most frequent error, since it gives Ppk. Another is calculating capability on data that are out of statistical control. A third is using too few readings: with fewer than about 30 individual values the indices carry wide confidence intervals, so quote the sample size and treat results as preliminary.

Cpk needs sigma within, Ppk needs sigma overall
Check control charts before quoting indices
Report sample size and confidence limits for small studies

Frequently Asked Questions

How do I calculate Cpk in Excel without a subgroup?

Use the moving range: average the absolute differences between consecutive readings, divide by 1.128 to get sigma within, then apply the Cpk formula. This is the standard approach for individuals data.

Why is my Ppk lower than my Cpk?

Ppk uses the overall standard deviation, which includes drift between subgroups, while Cpk uses only the within-subgroup variation. A large gap means the process mean or spread shifts over time.

Is Cpk of 1.33 enough?

It depends on the customer requirement and the characteristic. Many customers set 1.33 for existing processes and 1.67 for new ones or safety items. Use the requirement stated in your contract or control plan.

Can I use these formulas for one-sided specs?

Yes. For a one-sided specification use only Cpu or only Cpl as the Cpk, and ignore Cp, since there is no spec width. The expected PPM formula for the missing limit is simply left out.

If capability studies are rebuilt in spreadsheets every time, Netray can help set up a read-only feed from your inspection data so the formulas run on current numbers.

Talk to an Expert Explore More Free Tools

Related Tools

Quality & Inspection

More Quality & Inspection tools

See all

Back to All Tools