The Cpk formula in Excel

Type "cpk formula excel" into a search box and you get this:

=MIN((USL-AVERAGE(A2:A101))/(3*STDEV.S(A2:A101)),
     (AVERAGE(A2:A101)-LSL)/(3*STDEV.S(A2:A101)))

It is arithmetically correct and it is not Cpk. It is Ppk. If you have been reporting that cell as Cpk, you have been quoting a different index than the one the customer asked for, and — this is the part that matters — it is the pessimistic one, so nobody ever noticed.

Here is what to put in the spreadsheet instead.

Why STDEV.S gives you Ppk

Both indices use the same formula:

Cpk = min( USL − mean , mean − LSL ) / (3 × σ)
Ppk = min( USL − mean , mean − LSL ) / (3 × σ)

The difference is entirely in which sigma you feed them:

• Ppk uses σ_overall — the standard deviation of every reading in one pot. That is exactly what STDEV.S computes.
• Cpk uses σ_within — variation inside subgroups only, deliberately blind to how much the process moved between them.

Excel has a function for the first and no function for the second. That single gap is the whole reason this page exists. Full treatment of what the two indices mean in Cpk vs Ppk.

Ppk in Excel — do this one first

Lay out the sheet with named cells so the formulas stay readable. Put USL in E1, LSL in E2, readings in A2:A101:

Mean       =AVERAGE(A2:A101)
Sigma_ov   =STDEV.S(A2:A101)
Ppu        =($E$1-$B$1)/(3*$B$2)
Ppl        =($B$1-$E$2)/(3*$B$2)
Ppk        =MIN($B$3,$B$4)
Pp         =($E$1-$E$2)/(6*$B$2)

STDEV.S, not STDEV.P — you have a sample, not the population. STDEV.P divides by n instead of n−1 and quietly flatters you on small datasets.

That is Ppk, correctly labelled. Report it as Ppk.

Cpk in Excel — the part with no built-in function

σ_within is estimated from the average subgroup range and a constant:

σ_within = R̄ / d₂

R̄ is the mean of the subgroup ranges. d₂ depends on subgroup size and comes from a table — it is a bias-correction constant that turns an average range into an unbiased standard deviation estimate.

More in the control chart constants table.

Subgrouped data

Readings in A2:A101, subgroups of 5 running across B:F or stacked — either works, the range calculation just moves. With subgroups in rows, five columns B2:F2 down to B21:F21:

Range per subgroup   G2:  =MAX(B2:F2)-MIN(B2:F2)      ' fill down to G21
R-bar                J1:  =AVERAGE(G2:G21)
d2 for n=5           J2:  2.326
Sigma_within         J3:  =J1/J2
Grand mean           J4:  =AVERAGE(B2:F21)
Cpu                  J5:  =($E$1-$J$4)/(3*$J$3)
Cpl                  J6:  =($J$4-$E$2)/(3*$J$3)
Cpk                  J7:  =MIN(J5,J6)
Cp                   J8:  =($E$1-$E$2)/(6*$J$3)

If your data is stacked in one column, compute each subgroup's range with OFFSET — for subgroups of 5 starting at A2, in G2 filled down:

=MAX(OFFSET($A$2,(ROW()-2)*5,0,5,1))-MIN(OFFSET($A$2,(ROW()-2)*5,0,5,1))

OFFSET is volatile and recalculates constantly. On a hundred rows nobody notices; on ten thousand the workbook crawls.

Individual readings — one at a time

No subgroups, so σ_within comes from the moving range between consecutive readings, with d₂ = 1.128 (it is a subgroup of 2):

Moving range   B3:  =ABS(A3-A2)          ' fill down; B2 stays empty
MR-bar         D1:  =AVERAGE(B3:B101)
Sigma_within   D2:  =D1/1.128
Mean           D3:  =AVERAGE(A2:A101)
Cpk            D4:  =MIN(($E$1-$D$3)/(3*$D$2),($D$3-$E$2)/(3*$D$2))

This is the version most people actually need, because most processes outside high-volume machining get measured one part at a time.

Three things the spreadsheet will not tell you

1. Whether you were entitled to quote Cpk at all. Cpk is a claim about a process with one stable behaviour. If the process shifted during the study, σ_within excludes exactly the movement that hurt you, and Cpk flatters. The control chart is the evidence, and the spreadsheet does not know whether you drew one. Run the data through the control chart generator before you trust the number.

2. How uncertain the number is. A Cpk of 1.35 from 30 readings has a 95% confidence interval of roughly 1.0 to 1.7. That interval straddles the pass mark. Reporting 1.35 as though it were a measured constant is the most common way a capability study misleads everyone including its author. The Cpk confidence calculator gives you the interval.

3. Whether the data is normal. Cpk assumes it. A skewed or bimodal distribution makes the index meaningless while the formula returns a perfectly confident-looking number. Plot it first — histogram, or the normal distribution curve guide.

Also worth knowing: process capability analysis excel gets about ten searches a month, while the arithmetic above gets asked constantly. People do not want an analysis. They want the cell to be right.

Skip the spreadsheet

Paste your readings into the Cp/Cpk calculator and it returns Cp, Cpk, Pp and Ppk together, with both sigmas shown so you can see the gap, plus the histogram against your spec limits. Free, no signup, nothing stored.

Both indices side by side is the correct output, and it is what the spreadsheet version above takes twenty cells to produce.

Where Excel stops being the right tool

The formulas on this page are fine for a one-off study, and that is what they are for. The failure comes when a workbook becomes the quality system:

• Ranges that grow. A2:A101 becomes A2:A5000, someone pastes new data below the range, and the number silently stops updating.
• The d₂ constant hardcoded for a subgroup size that changed six months ago.
• STDEV.P where STDEV.S belonged, in one of four copies of the workbook.
• Nothing watching. Cpk in a spreadsheet is a number somebody computed once. It does not degrade visibly.

SPC charts without Excel covers what replaces it — and, more usefully, when it is not worth replacing.