Cpk vs Ppk in Excel: the exact formulas, and what the gap means

Worked with an X-bar & R chart, subgroup size n = 5.

Cp and Cpk use within-subgroup variation. Pp and Ppk use the overall standard deviation of every reading. That single difference is why the two pairs disagree — and the size of the disagreement is itself a diagnostic.

The two sigmas

Everything follows from which standard deviation you put in the denominator.

Name	Sigma used	How it is estimated	What it describes
Cp, Cpk	σ_within	R̄ ÷ d₂	Short-term spread — how tight the process is inside a subgroup
Pp, Ppk	s	Sample standard deviation of all readings	Long-term spread — everything, including drift between subgroups

Step 1 — the inputs

Assume 25 subgroups of 5 readings in B2:F26, with the spec limits in named cells USL and LSL.

Subgroup average   G2:  =AVERAGE(B2:F2)
Subgroup range     H2:  =MAX(B2:F2)-MIN(B2:F2)

Grand average      Xbb: =AVERAGE(B2:F26)
Average range      Rbar:=AVERAGE(H2:H26)
Overall std dev    s:   =STDEV.S(B2:F26)

Use STDEV.S, not STDEV.P. Ppk is defined on the sample standard deviation (divisor n−1). STDEV.P will quietly inflate your Ppk.

Step 2 — within-subgroup sigma

d₂ depends only on the subgroup size:

n	2	3	4	5	6	7	8	9	10
d₂	1.128	1.693	2.059	2.326	2.534	2.704	2.847	2.970	3.078

sigma_within (n=5):  =Rbar/2.326

Step 3 — the four indices

Cp   =(USL-LSL)/(6*sigma_within)
Cpk  =MIN((USL-Xbb)/(3*sigma_within),(Xbb-LSL)/(3*sigma_within))

Pp   =(USL-LSL)/(6*s)
Ppk  =MIN((USL-Xbb)/(3*s),(Xbb-LSL)/(3*s))

Cp and Pp ignore where the process is centred; Cpk and Ppk do not. That is why Cp can look healthy while Cpk is poor — the spread is fine, the aim is off.

While you are here: the control limits

The same R̄ drives the X-bar and R chart limits, through a different pair of constants.

n	A₂	D₃	D₄
2	1.880	0	3.267
3	1.023	0	2.574
4	0.729	0	2.282
5	0.577	0	2.114
6	0.483	0	2.004
7	0.419	0.076	1.924

X-bar UCL  =Xbb + 0.577*Rbar      X-bar LCL  =Xbb - 0.577*Rbar
R UCL      =2.114*Rbar            R LCL      =0*Rbar        (n=5)

Reading the gap

What you see	What it usually means	Where to look
Cpk ≈ Ppk	The process is stable over the study period	Nothing — this is the healthy case
Cpk ≫ Ppk	Tight within subgroups, but the mean moved between them	Setup changes, tool wear, shift handover, material lots
Cpk ≈ Ppk, both low	Genuinely wide common-cause variation	The process itself, not a special event
Ppk > Cpk	Unusual. Often a subgrouping mistake	Are your subgroups rational? Consecutive parts, one stream?

The gap is the point. A capability number on its own tells you whether today's output fits the spec; the gap between Cpk and Ppk tells you whether that number will survive next week. This is also why you should not report capability from an out-of-control chart — if the chart shows special causes, σ_within is estimating a process that does not exist as a single stable population.

A worked case

The sample data shipped in our SPC template is 25 subgroups of 5, with a deliberate excursion inserted at subgroups 18 and 19. Those two points flag as out of control on the X-bar chart, and the capability sheet returns Cpk 2.59 against Ppk 1.58.

Nothing is wrong with the arithmetic. Within any single subgroup the process is extremely tight, so σ_within is small and Cpk is flattering. The overall standard deviation has to absorb the excursion as well, so Ppk drops. Reporting the 2.59 alone would describe a process that was, in fact, disturbed twice during the study.

Template

All of the above is wired up in the SPC Control Chart Excel template — X-bar & R charts with automatic limits, out-of-control flagging, and the capability sheet. Plain .xlsx worksheet formulas, so every constant above is visible in the cells and you can change the subgroup size without touching any code.