Cellwise Start free

Excel guide · 4 min read

How to calculate Cp and Cpk in Excel

Quick answerCp = (USL - LSL) / (6 x sigma) and Cpk = MIN(USL - mean, mean - LSL) / (3 x sigma). With spec limits in F1:F2, the mean in F5 (=AVERAGE(B2:B13)) and sigma in F6, use =(F2-F1)/(6*F6) and =MIN(F2-F5,F5-F1)/(3*F6). For individual readings, estimate sigma as the average moving range / 1.128; STDEV.S gives the overall sigma used for Pp and Ppk.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

RowABCDEF
1SampleDiameter (mm)Moving rangeLSL24.975
2125.003USL25.025
3225.0080.005Target25
4325.0050.003
5425.0110.006Mean25.012583333
6525.0090.002Sigma (within)0.004029658285
7625.0140.005Sigma (overall)0.0059917873086
8725.010.004
9825.0170.007
10925.0150.002
111025.020.005
121125.0160.004
131225.0230.007

Download this sheet as an Excel practice file (.xlsx)

Set up measurements and spec limits

List the measurements in the order they were made: here 12 shaft diameters in mm. Put the specification limits in their own cells: LSL (lower spec limit) 24.975 in F1, USL (upper spec limit) 25.025 in F2, so the spec is 25.000 +/- 0.025 mm. Spec limits come from the drawing or the customer, not from your data.

Then add three summary cells: the mean in F5 (=AVERAGE(B2:B13), 25.0126), and two estimates of sigma, which the next section explains.

=F5-F3

Result: 0.012583333333

0.01258: the process runs 0.0126 mm above the 25.000 target. Hold on to that; it's why Cpk will be far below Cp.

Which sigma? Moving range for Cp/Cpk, STDEV.S for Pp/Ppk

Capability indices formally use the short-term, within-process sigma: how much parts vary from one to the next. For individual readings (one part at a time, no subgroups), estimate it from the moving range. C3 is =ABS(B3-B2), filled down, and F6 is =AVERAGE(C3:C13)/1.128. The constant 1.128 converts an average range of two consecutive readings into a standard deviation.

STDEV.S on all readings, in F7, gives the overall (long-term) sigma, which also includes any drift over the run. Indices built from it are called Pp and Ppk. Many spreadsheets online plug STDEV.S into the Cpk formula; that's really Ppk. Neither is wrong, but label which one you report.

Here the overall sigma (0.0060) is half again as big as the within sigma (0.0040). The first six parts average 25.0083 and the last six 25.0168: the diameter is creeping up as the run goes on.

=AVERAGE(C3:C13)/1.128

Result: 0.004029658285

0.00403: the within-process sigma, from an average moving range of 0.00455 mm.

Cp: could the process fit the spec?

Cp compares the width of the spec (USL - LSL) with the natural spread of the process (6 sigma): =(F2-F1)/(6*F6). It ignores where the process is centred. A Cp of 2.07 says the spread would fit inside the tolerance about twice over, if the process were centred.

Cp = 1 means the process spread exactly fills the tolerance, with no room for any shift. Customers commonly ask for at least 1.33, and often more for critical features; use whatever your customer or quality manual sets.

=(F2-F1)/(6*F6)

Result: 2.068 shows as 2.07 with Number format

2.068: the spread is narrow enough to fit the spec about twice over.

Cpk: does it actually fit, given where it's centred?

Cpk measures the distance from the mean to the nearer spec limit, in units of 3 sigma: =MIN(F2-F5,F5-F1)/(3*F6). Here the mean is 0.0124 mm below the USL but 0.0376 mm above the LSL, so the upper side sets Cpk.

Cp of 2.07 but Cpk of 1.03 tells you something specific: the process isn't too variable, it's off-centre. Moving the mean back to 25.000 (a tool offset, not a new machine) would bring Cpk up to Cp. When Cpk equals Cp, the process is perfectly centred.

Pp and Ppk use the overall sigma in F7 instead: =(F2-F1)/(6*F7) gives 1.39 and =MIN(F2-F5,F5-F1)/(3*F7) gives 0.69. Ppk far below Cpk is another warning that the process moved during the run.

=MIN(F2-F5,F5-F1)/(3*F6)

Result: 1.0271066667 shows as 1.03 with Number format

1.027: Cpk is only about 1, half of Cp, because the mean sits close to the upper limit.

One-sided specs and the caveats that matter

Some features only have one limit: a maximum burr height, a minimum strength. Then Cp can't be calculated, and Cpk is the one side you have: =(F2-F5)/(3*F6) for an upper limit only, =(F5-F1)/(3*F6) for a lower limit only.

Capability numbers assume a stable process. This sample isn't: it drifts upward, and a drifting process will go out of spec however good its Cpk was last week. Check stability first with a control chart (an individuals chart uses the same moving range), and fix the drift before quoting capability.

Sample size matters too. Twelve readings are enough to show the method, but a Cpk from 12 points can easily move by 0.3 with the next batch. Most quality guides ask for at least 30 readings, often 50 to 100, from a run that represents normal production. The formulas also assume roughly normal data; strongly skewed measurements need other methods.

=(F2-F5)/(3*F6)

Result: 1.0271066667 shows as 1.03 with Number format

1.027: with an upper limit only, Cpk is the upper side, the same 1.03 here because the upper side was already the nearer one.

Common mistakes

Calling STDEV.S-based results Cpk

With overall sigma the indices are Pp and Ppk. For Cp and Cpk on individual readings, use average moving range / 1.128.

Measurements not in production order

The moving range compares each reading with the one before it; sorted data makes sigma (and Cpk) look far better than it is.

Quoting Cpk for an unstable process

Check a control chart first. A drift or shift means the number won't predict next week's parts.

Spec limits typed into every formula

Keep LSL, USL and target in input cells so a drawing change updates every result.

FAQ

What is the formula for Cpk in Excel?

=MIN(USL-mean,mean-LSL)/(3*sigma). With cells: =MIN(F2-F5,F5-F1)/(3*F6), where F1 is LSL, F2 is USL, F5 the mean and F6 the within-process sigma.

What is the difference between Cp and Cpk?

Cp compares the spec width with the process spread and ignores centring. Cpk also accounts for where the mean sits, so it's always less than or equal to Cp. A big gap means the process is off-centre.

What is the difference between Cpk and Ppk?

Both use the same formula. Cpk uses short-term, within-process sigma (from the moving range or subgroups); Ppk uses the overall standard deviation of all readings (STDEV.S), so it also includes drift and shifts over time.

Is there a Cpk function in Excel?

No. Excel has no built-in Cp or Cpk function, so you build it from AVERAGE, STDEV.S or the moving range, and MIN, as shown here.

Functions used

Related guides

Updated