# Cp and Cpk from Raw Measurements Worksheet

> Source: https://netray.co/tools/cpk-from-raw-measurements-worksheet
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Enter individual measurements and spec limits to get mean, standard deviation, Cp, Cpk, observed out-of-spec count and estimated PPM.

## Category

quality

## Audience

- Quality engineer
- Process engineer
- Supplier quality

## Tool

**Kind:** worksheet

**Mode:** editable

**Columns:**

#### Sample

**Key:** sample

**Type:** text

#### Measured value

**Key:** x

**Type:** number

**Unit:** mm

#### USL

**Key:** usl

**Type:** number

**Unit:** mm

**Help:** Enter the same USL on every row.

#### LSL

**Key:** lsl

**Type:** number

**Unit:** mm

**Help:** Enter the same LSL on every row.

#### Value squared

**Key:** x2

**Type:** number

**Expr:** x * x

**Format:** decimal

#### In spec (1 = yes)

**Key:** in_spec

**Type:** number

**Expr:** if(and(gte(x, lsl), lte(x, usl)), 1, 0)

**Format:** integer

**Rows:**

**Sample:** 1

**X:** 12.004

**Usl:** 12.025

**Lsl:** 11.975

**Sample:** 2

**X:** 11.998

**Usl:** 12.025

**Lsl:** 11.975

**Sample:** 3

**X:** 12.006

**Usl:** 12.025

**Lsl:** 11.975

**Sample:** 4

**X:** 12.001

**Usl:** 12.025

**Lsl:** 11.975

**Sample:** 5

**X:** 11.995

**Usl:** 12.025

**Lsl:** 11.975

**Sample:** 6

**X:** 12.009

**Usl:** 12.025

**Lsl:** 11.975

**Sample:** 7

**X:** 12.003

**Usl:** 12.025

**Lsl:** 11.975

**Sample:** 8

**X:** 11.999

**Usl:** 12.025

**Lsl:** 11.975

**Sample:** 9

**X:** 12.007

**Usl:** 12.025

**Lsl:** 11.975

**Sample:** 10

**X:** 12.002

**Usl:** 12.025

**Lsl:** 11.975

**Sample:** 11

**X:** 11.996

**Usl:** 12.025

**Lsl:** 11.975

**Sample:** 12

**X:** 12.005

**Usl:** 12.025

**Lsl:** 11.975

**Sample:** 13

**X:** 12

**Usl:** 12.025

**Lsl:** 11.975

**Sample:** 14

**X:** 12.01

**Usl:** 12.025

**Lsl:** 11.975

**Sample:** 15

**X:** 11.997

**Usl:** 12.025

**Lsl:** 11.975

**Sample:** 16

**X:** 12.004

**Usl:** 12.025

**Lsl:** 11.975

**Sample:** 17

**X:** 12.008

**Usl:** 12.025

**Lsl:** 11.975

**Sample:** 18

**X:** 11.994

**Usl:** 12.025

**Lsl:** 11.975

**Sample:** 19

**X:** 12.001

**Usl:** 12.025

**Lsl:** 11.975

**Sample:** 20

**X:** 12.006

**Usl:** 12.025

**Lsl:** 11.975

**Totals:**

#### Sum of measurements

**Key:** x

**Agg:** sum

**Format:** decimal

#### Sum of squares

**Key:** x2

**Agg:** sum

**Format:** decimal

#### Measurements within spec

**Key:** in_spec

**Agg:** sum

**Format:** integer

#### USL used

**Key:** usl

**Agg:** avg

**Format:** decimal

#### LSL used

**Key:** lsl

**Agg:** avg

**Format:** decimal

**Summary:**

#### Sample size

**Key:** n

**Expr:** count

**Format:** integer

#### Mean

**Key:** mean

**Expr:** sum_x / count

**Format:** decimal

#### Sample standard deviation (n-1)

**Key:** sd

**Expr:** if(gt(count, 1), sqrt(max((sum_x2 - sum_x * sum_x / count) / (count - 1), 0)), 0)

**Format:** decimal

#### Cp

**Key:** cp

**Expr:** (avg_usl - avg_lsl) / (6 * sd)

**Format:** decimal

#### Cpu

**Key:** cpu

**Expr:** (avg_usl - mean) / (3 * sd)

**Format:** decimal

#### Cpl

**Key:** cpl

**Expr:** (mean - avg_lsl) / (3 * sd)

**Format:** decimal

#### Cpk

**Key:** cpk

**Expr:** min(cpu, cpl)

**Format:** decimal

**Primary:** true

#### Observed out-of-spec readings

**Key:** oos

**Expr:** count - sum_in_spec

**Format:** integer

#### Estimated PPM out of spec (normal model)

**Key:** ppm

**Expr:** (1 - normsdist((avg_usl - mean) / sd) + normsdist((avg_lsl - mean) / sd)) * 1000000

**Format:** integer

**Primary:** true

**Notes:** Cp = (USL - LSL) / (6 s). Cpk = min((USL - mean) / (3 s), (mean - LSL) / (3 s)). The standard deviation here is the overall sample standard deviation with n-1, so strictly this is a Ppk-style estimate; for Cpk proper, sigma should come from within-subgroup variation (R-bar / d2) on a stable process. Enter USL and LSL on every row, the summary uses the column average. The PPM estimate assumes normal data. Use at least 30 readings, 100 or more for customer submissions.

## Content

**Intro:** Cp and Cpk compare how wide your process spreads to how wide your tolerance is. This worksheet takes the raw gauge or CMM readings, computes the mean and sample standard deviation from running sums, and reports the capability indices and an estimated PPM.

**Sections:**

#### How to use it

**Body:** Replace the example rows with your readings, one per row, taken in production order from a single process stream. Type the USL and LSL on every row, or fill down. The totals row sums the readings and their squares, and the summary derives the mean, the sample standard deviation, Cp, Cpu, Cpl, Cpk, the number of readings outside the limits and an estimated PPM. For a one-sided tolerance, enter an extreme but realistic value for the missing limit and ignore the corresponding index, or use only Cpu or Cpl.

**Bullets:**

- Take readings from consecutive parts in a stable run, not a mixed bin.
- Use a gauge with resolution at most one tenth of the tolerance.
- Remove no data points unless you can document an assignable cause.
- Check normality before trusting the PPM estimate.

#### The formulas

**Body:** Cp = (USL - LSL) / (6 s) measures potential capability if the process were centered. Cpu = (USL - mean) / (3 s) and Cpl = (mean - LSL) / (3 s) measure each side. Cpk is the smaller of the two, so it penalizes an off-center process. The sample standard deviation s uses n-1 in the denominator, computed here as the square root of (sum of squares minus sum squared over n) divided by n-1. The estimated PPM uses the normal distribution: the area beyond USL plus the area below LSL, multiplied by one million.

#### Reading the example

**Body:** The 20 readings on a 12.000 mm shaft, with limits 11.975 and 12.025, have a mean near 12.002 and a standard deviation near 0.0045. Cp is about 1.8 and Cpk about 1.7, comfortably above the common 1.33 customer minimum. Cpk is lower than Cp because the mean sits slightly above nominal, shortening the distance to the USL. With only 20 readings the confidence interval on Cpk is wide, roughly plus or minus 0.4, so a larger sample is needed for a formal capability claim.

#### Cpk versus Ppk and sample size

**Body:** Cpk is meant to use short-term, within-subgroup sigma on a process in statistical control, while Ppk uses the overall standard deviation and describes performance including drift. The sheet uses overall sigma, so if your readings include shift-to-shift drift, it is effectively Ppk. Automotive PPAP typically asks for Ppk of at least 1.67 on the initial study and Cpk of at least 1.33 on ongoing production. A small sample inflates uncertainty, so quote an interval or sample size alongside any index.

**Cta:** If your measurement data already sits in an ERP inspection module or MES, Netray can build a private agent that computes capability on live data and shows the query behind each number.

**Faq:**

#### How many measurements do I need?

**Answer:** At least 30 for a rough view and 100 or more for a customer submission. With 20 readings the 95 percent confidence interval on Cpk is roughly plus or minus 0.4 at a value near 1.7, which is too wide for a pass/fail judgment near 1.33.

#### Is this Cpk or Ppk?

**Answer:** It uses the overall sample standard deviation, which is the Ppk definition. If your data comes from a stable process with little between-subgroup variation, Cpk and Ppk will be close. For a formal Cpk, estimate sigma from subgroup ranges instead.

#### What if my data is not normal?

**Answer:** The Cp and Cpk numbers still compute, but the PPM estimate can be wrong, especially for bounded features such as flatness or position that are skewed against zero. Use a non-normal method such as Clements percentiles or a fitted distribution for those characteristics.

#### Why do I enter the limits on every row?

**Answer:** The worksheet engine computes the summary from column aggregates, so the limits live in columns. The summary takes the average of the column, so as long as every row has the same limit the result is correct.

## Search terms

cpk calculator from data, calculate cpk from measurements excel, cp cpk worksheet, process capability from raw data, cpk sample standard deviation, cpk template excel, how to calculate cpk step by step

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json