Cp and Cpk formula in Excel, with a worked example

The Cp and Cpk formulas

Cp = (USL − LSL) ÷ (6 × σ)

Cpk = MIN(USL − mean, mean − LSL) ÷ (3 × σ)

USL and LSL are the upper and lower specification limits, the tolerance the customer allows. About 99.73% of a normal process lies within ±3σ of its mean, so 6σ is the natural width of the process. Cp asks how many times that width fits into the tolerance, as if the process were perfectly centred. Cpk measures the distance from the mean to the nearer limit in units of 3σ, so it punishes an off-centre process. Cpk can never be higher than Cp, and the two are equal only when the mean sits exactly in the middle of the tolerance.

Step 1: which σ? Within versus overall

Cp and Cpk use the within-subgroup standard deviation, the short-term noise of the process. With data in rational subgroups, small samples taken close together, estimate it from the average range: σ within = R-bar ÷ d2. The constant d2 depends on the subgroup size: 1.128 for 2, 1.693 for 3, 2.059 for 4 and 2.326 for 5.

The example measures three shafts every hour for eight hours. The ranges average 0.0170 mm, so σ within = 0.0170 ÷ 1.693 = 0.0100 mm. The overall standard deviation of all 24 values, =STDEV.S(B2:D9), is slightly larger at 0.0112 mm because it also contains the drift between the hours. That one is used for Pp and Ppk, the long-term performance indices.

Step 2: Cp, Cpk, Pp and Ppk in Excel

With the limits in B11 and B12, the mean in B13 and the two sigmas in B16 and B17:

• =(B12-B11)/(6*B16) gives Cp = 1.66
• =(B12-B13)/(3*B16) and =(B13-B11)/(3*B16) give the upper side Cpu = 1.05 and the lower side Cpl = 2.27
• =MIN(B19,B20) gives Cpk = 1.05
• the same formulas with the overall σ give Pp = 1.49 and Ppk = 0.94

The mean is 25.0183 mm, 0.018 mm above the nominal 25.000. The process is precise enough (Cp 1.66), but it sits too close to the upper limit, so Cpk is far below Cp. Re-centring the process on 25.000 mm would bring Cpk up to the Cp of 1.66 without any other improvement. That is usually the cheapest fix in quality work: an offset adjustment, not a new machine.

Worked example: capability of a machined shaft diameter

Eight subgroups of three shafts, one subgroup per hour. Specification 25.000 ± 0.050 mm. Measurements and limits are yellow inputs, every result is a formula.

Worked example: capability of a machined shaft diameter

	A	B	C	D	E
1	Subgroup	x1 (mm)	x2 (mm)	x3 (mm)	Range (mm)
2	Hour 1	25.024	25.007	25.017	0.017
3	Hour 2	25.011	25.029	25.020	0.018
4	Hour 3	25.036	25.019	25.030	0.017
5	Hour 4	25.002	25.014	24.998	0.016
6	Hour 5	25.021	25.035	25.016	0.019
7	Hour 6	25.015	25.003	25.020	0.017
8	Hour 7	25.027	25.039	25.024	0.015
9	Hour 8	25.012	25.001	25.018	0.017
10
11	LSL (mm)	24.950
12	USL (mm)	25.050
13	Mean of all 24 values	25.0182
14	R-bar (average range)	0.0170
15	d2 for subgroups of 3	1.693
16	σ within = R-bar ÷ d2	0.0100
17	σ overall = STDEV.S	0.0112
18	Cp	1.66
19	Cpu (upper side)	1.05
20	Cpl (lower side)	2.27
21	Cpk	1.05
22	Pp	1.49
23	Ppk	0.94
24	Expected ppm above USL (within σ)	784

Fictional data for teaching. Real capability studies need a stable process, a capable gauge and enough data, often 100 or more values.

What is a good Cpk?

A Cpk of 1.00 means the nearer limit is exactly 3σ from the mean, about 1,350 parts per million outside on that side. A Cpk of 1.33 means 4σ, about 32 ppm. Common targets are Cpk ≥ 1.33 for an existing process and 1.67 for new or safety-critical characteristics, and a Six Sigma process has Cp = 2.0. Customers often write their own minimum into the supplier agreement, so check that first.

For the shaft, the upper side at Cpu = 1.05 implies about 784 ppm above the USL, =(1-NORM.S.DIST(3*B19,TRUE))*1000000. The lower side contributes practically nothing.

Check stability and normality first

Capability indices only mean something for a stable process. If the process shifts from hour to hour, a capability number describes a moment, not a process. Plot an X-bar and R control chart before you report Cpk, and look at the shape of the data with a histogram in Excel: the ppm estimates assume a roughly normal distribution. A large gap between Cpk and Ppk, like 1.05 against 0.94 here, is itself a signal that the process drifts over time.

Sample size matters too. Twenty-four values are enough to learn the method, but a capability study for a customer usually needs more, often 100 or more measurements in at least 20 to 25 subgroups, to keep the estimate of σ reasonably stable.

Common mistakes

• Using STDEV.S of all data for Cpk. That is the overall σ and gives Ppk. Cp and Cpk use the within-subgroup σ, such as R-bar ÷ d2.
• Dividing by 6σ in Cpk. Cpk looks at one side of the distribution, so it divides by 3σ. Cp divides the whole tolerance by 6σ.
• Taking MAX instead of MIN. Cpk is the worse side. Using the better side hides an off-centre process.
• Reporting Cpk for an unstable process. Check a control chart first. Capability is a property of a stable process.
• Using the wrong d2. d2 depends on the subgroup size: 1.693 for subgroups of 3, 2.326 for 5.
• Trusting a capability study on a poor gauge. If the measurement system eats up much of the tolerance, run a gage R&R before believing any Cpk.