Excel has no built-in Cpk function, but four formulas get you there. Put your readings in subgroups, one row each. Find the mean with =AVERAGE, the subgroup ranges with =MAX−MIN, and the average range R̄. Divide R̄ by the d₂ constant (2.326 for subgroups of 5) to estimate within-subgroup sigma. Then Cpk = MIN(USL − mean, mean − LSL) ÷ (3 × sigma). Swap in =STDEV.S of all readings and the same formula gives Ppk. The steps below give the exact cells, and the Example works one through.
Cpk asks how many three-sigma widths fit between the process mean and the nearer specification limit. A Cpk of 1.0 means the nearer limit sits exactly three sigma from the mean. A Cpk of 1.33 means four sigma, and 1.67 means five. Because it uses the nearer limit, Cpk drops when the process drifts off center even if the spread stays the same.
The only judgement call is which sigma to use. The range method in the steps estimates within-subgroup sigma, which is what Cpk is meant to use. Running =STDEV.S over every reading gives the overall sigma, which belongs in Ppk. Cpk vs Ppk explains why the two numbers drift apart and what the gap tells you.
The R̄/d₂ method is fine for subgroups up to about 8. For larger subgroups, use the average subgroup standard deviation instead: put =STDEV.S(B2:K2) in each row, average the column, and divide by the c₄ constant for your subgroup size (0.9400 for 5, 0.9727 for 10). Both constants are tabulated in the AIAG SPC manual and in Montgomery’s textbook.
If you only have individual readings with no natural subgroups, use the moving range. Put =ABS(B3-B2) next to each reading from the second one on, average those moving ranges, and divide by 1.128.
For a stable, roughly normal process you can turn the result into an expected reject rate. In Excel: =(1-NORM.DIST(J2,J3,J4,TRUE))+NORM.DIST(J1,J3,J4,TRUE) returns the fraction outside both limits. Multiply by 1,000,000 for ppm. The DPMO and sigma calculator does the same conversion. Treat the number as an estimate: the tails of real data rarely follow the normal curve exactly.
Automotive customers expect capability results in the PPAP submission and on the control plan for special characteristics. The PPAP guide shows where the study fits, and the IATF 16949 control plan template has columns for the characteristic, the method and the reaction plan. Read your customer’s supplier manual for the minimum index they require. 1.33 for ongoing production is common, and many initial studies require 1.67.
Before the capability study, check the gauge. Measurement noise adds to both sigmas and pulls Cpk down. How to run a gauge R&R study walks through that check.
Example: A turned pin with diameter 25.00 ± 0.10 mm, so LSL = 24.90 and USL = 25.10 (fictional data). Five subgroups of 5 parts were measured, so the Excel ranges end at row 6 instead of 26. Subgroup 1: 25.03, 25.01, 25.03, 25.02, 25.00. Subgroup 2: 25.02, 25.05, 25.03, 25.01, 25.04. Subgroup 3: 25.00, 25.02, 24.99, 25.04, 25.01. Subgroup 4: 25.04, 25.06, 25.03, 25.05, 25.03. Subgroup 5: 25.01, 25.00, 25.02, 25.00, 25.03.
Result: Cpk is 1.66 and Ppk is 1.42. Cp of 2.15 shows the spread would fit comfortably if the mean were centered at 25.00, so re-centering the process by about 0.02 mm would raise Cpk more than any reduction in variation. Subgroup 4 runs higher than the others, which explains part of the gap to Ppk. Five subgroups are too few for a real study; collect 25 or more before reporting.
No. Excel has no CPK or PPK function. Build it from AVERAGE, STDEV.S, MAX and MIN as shown in the steps, or use an add-in. The formulas are simple enough that a sheet you build yourself is easy to audit.
d₂ is a constant that converts an average range into a standard deviation estimate for a normal process. It depends on subgroup size. 2.326 is the value for subgroups of 5, and the full table is in SPC references such as the AIAG SPC manual.
Use only the side that exists: Cpk = (USL − mean) ÷ (3 × within sigma). In Excel that is =(J2-J3)/(3*J6). Cp is not defined for a one-sided tolerance.
Within-subgroup sigma only sees short-term variation, while the overall sigma also includes shifts between subgroups. A Cpk above Ppk is normal. A large gap means something moves the process between subgroups.
The ToolPak’s Descriptive Statistics gives the mean and the sample standard deviation, which is enough for Ppk. It does not compute a within-subgroup sigma, so you still need the range or s̄/c₄ step for Cpk.
COPLAIN AI PLATFORM
Coplain turns your control plan and inspection procedures into clear, numbered work instructions, so the people collecting capability data measure the same way every time.
Generate yours automatically with Coplain →Related Tools & Templates