Control Charts

Statistics
Quality Control
Manufacturing
Shewhart control charts with Western Electric and Nelson run rules, Phase I trial limits, and process capability analysis.

Overview

Control Charts (SPC) provides Shewhart statistical process control charts, standard run-rule evaluations, retrospective Phase I trial-limit screening, and normal-model process capability analysis (\(C_p\), \(C_{pk}\), \(P_p\), \(P_{pk}\), PPM). It serves process engineers, manufacturing supervisors, and quality analysts who need to distinguish common-cause random variability from assignable special causes in production data.

In traditional Excel practice, building control charts requires manual lookups of unbiasing constants (\(A_2, D_3, D_4, c_4, d_2, d_3\)), complex nested IF formulas for run rules, and separate workbooks for capability indices. Specialized statistical packages like Minitab, XLSTAT, and Analyse-it package these tools into expensive standalone modules. This template brings the entire workflow directly into Excel with Python, shipping fully populated with published benchmark data, dual native Excel charts, and automated verification against published NIST/SEMATECH standards.

Before you start

Requirements. Excel with Python for Excel enabled. The template operates entirely within the workbook using Python in Excel (boardflare, numpy, scipy, and pandas) without external add-ins, COM servers, or database connections.

Core statistical quality control concepts. - Shewhart control limits: Statistical boundaries set at \(\pm 3\) standard errors from the process center line. For a stable normally distributed process, approximately 99.73% of subgroup statistics fall within these 3-sigma limits by chance alone. - Variables vs. attribute data: Variables charts monitor continuous dimensional measurements (diameter, tensile strength, fill weight). Attribute charts track discrete counts of defective items or defects per unit. - Statistical stability vs. capability: Stability means the process is subject only to common-cause random variation (in statistical control). Capability measures whether the stable process distribution fits within customer specification limits (LSL and USL). Assessing capability on an unstable process is invalid because the distribution mean and variance are not constant. - Phase I vs. Phase II control limits: Phase I refers to retrospective baseline data collection (typically 20–30 subgroups) used to establish trial limits by identifying and screening out special-cause outliers. Phase II applies these locked baseline limits prospectively to monitor ongoing production.

Assumptions and boundary conditions. 1. Normal distribution: Variables control limits and capability indices (\(C_p, C_{pk}, P_p, P_{pk}\), expected PPM) assume normally distributed individual measurements. 2. Equal subgroup sizes: Variables charts (\(X\text{-bar}/R\) and \(X\text{-bar}/S\)) require equal subgroup sizes across all rows. 3. Independent observations: Consecutive subgroups are assumed statistically independent over time.

Workbook tour

Sheet and range What it holds Kind
Dashboard, A5:B9 Executive plain-language reading summarizing stability, run rules, capability, and distribution cautions. Output
Dashboard, A12:B25 Summary statistics: subgroup count, subgroup size, center lines, estimated \(\sigma\), UCL/LCL, and violation counts. Output
Dashboard, C12:D27 Process capability indices (\(C_p, C_{pl}, C_{pu}, C_{pk}, P_p, P_{pl}, P_{pu}, P_{pk}\), observed PPM, expected PPM). Output
Dashboard, A36:D46 Run-rule violation table listing flagged subgroups, chart, rule name, and plain-language descriptions. Output
Dashboard, H36:I44 Retrospective Phase I screening parameters, screening status, iteration count, and removed subgroup IDs. Output
Dashboard, F4 & F19 Native Excel scatter charts displaying Process Location and Process Dispersion series with control limits. Output
Data, A4:K304 Variables input table: Subgroup ID followed by observation columns Obs 1 to Obs 10 (shipped with Montgomery data). Editable input
Attributes, A4:C104 Attribute input table: Sample ID, Defect Count, and Sample Size (shipped with NIST wafer defect data). Editable input
Settings, A4:C12 User-configurable parameters: Chart type, Sigma multiplier, Run rules, Phase I toggle, LSL, USL, Target, Unit. Editable input
ChartData, A5:J305 Published coordinates (subgroup, location, location limits, dispersion, dispersion limits, flag) plotted by Excel charts. Output
Validation, A5:E59 Published NIST benchmarks and engine regression guards with expected values, tolerances, and sources. Reference
Validation, G5:I59 Automated check execution: actual recomputed values, absolute differences, and PASS/FAIL statuses. Output
Guide, A4:B10 Practitioner modification guide covering chart selection, run-rule logic, and capability prerequisites. Reference

Using the template

  1. Review shipped baseline results: On opening, the Dashboard displays Montgomery’s piston-ring diameter dataset on an \(X\text{-bar}\) and \(R\) chart. All 25 subgroups fall strictly within control limits, and the process is capable (\(C_{pk} \approx 1.63\) against illustrative specification limits 73.950–74.050 mm).
  2. Select your chart type: Navigate to the Settings sheet. In cell B5, choose your chart from the drop-down list (X-bar and R, X-bar and S, Individuals and MR, p, np, c, or u).
  3. Paste your data:
    • For variables data, paste your subgroup measurements into the table on the Data sheet. Ensure each subgroup row has an equal number of measurements.
    • For attribute data, paste sample IDs, defect counts, and inspection sizes into the Attributes sheet.
  4. Configure specification limits: On the Settings sheet, enter your Lower Specification Limit (LSL) in cell B9 and Upper Specification Limit (USL) in cell B10. Either limit may be left blank for one-sided specifications.
  5. Enable Phase I screening (optional): If establishing baseline limits from historical data that contains known special causes, switch cell B8 (Phase I screening) to On.

Inputs and settings

The template reads user parameters from Settings!A4:B12:

  • Chart type (Settings!B5): Drop-down list selecting the Shewhart chart:
    • X-bar and R: Subgroup location and range (recommended for subgroup size \(n=2\dots 9\)).
    • X-bar and S: Subgroup location and sample standard deviation (recommended for \(n \ge 10\)).
    • Individuals and MR: Individual observations with two-point moving range (\(n=1\)).
    • p: Fraction nonconforming / defectives (constant or variable sample size \(n\)).
    • np: Number of nonconforming items (constant sample size \(n\)).
    • c: Poisson defect count per fixed inspection unit.
    • u: Poisson defects per unit for variable or constant inspection sizes.
  • Sigma multiplier (Settings!B6): Control limit distance factor \(k\) (default 3.0 for standard 3-sigma Shewhart limits).
  • Run-rule set (Settings!B7): Drop-down list selecting None, Western Electric (rules WE1–WE4), or Nelson (rules N1–N8).
  • Phase I screening (Settings!B8): Drop-down toggle (Off or On) enabling iterative screening of 3-sigma outliers.
  • Specification limits (Settings!B9:B10): Numerical LSL and USL. Either or both may be blank.
  • Target (Settings!B11): Optional process nominal target.
  • Unit (Settings!B12): Measurement unit label (e.g., mm, psi, count).

Input tables on Data and Attributes are sized for realistic volume: - Data accommodates up to 10 measurement columns (Obs 1 to Obs 10) and at least 300 subgroup rows. - Total input cells (~3,616 cells, ~180 KB) remain well within the 10,000-cell / 512 KB snapshot byte limit (MAX_INPUT_SNAPSHOT_BYTES).

How results are calculated

Unbiasing Constants

Unbiasing factors are derived directly from first-principles numerical integration in the engine: - \(c_4(n) = \sqrt{\frac{2}{n-1}} \frac{\Gamma(n/2)}{\Gamma((n-1)/2)}\) - \(d_2(n) = \int_{-\infty}^{\infty} [1 - (1 - \Phi(x))^n - \Phi(x)^n] dx\) - \(d_3(n) = \sqrt{\text{Var}(W)}\) where \(W\) is the relative range of \(n\) standard normal variables. - Standard Shewhart multipliers: \(A_2 = \frac{3}{d_2 \sqrt{n}}\), \(A_3 = \frac{3}{c_4 \sqrt{n}}\), \(D_3 = \max(0, 1 - 3 d_3/d_2)\), \(D_4 = 1 + 3 d_3/d_2\), \(B_3 = \max(0, 1 - 3 \sqrt{1-c_4^2}/c_4)\), \(B_4 = 1 + 3 \sqrt{1-c_4^2}/c_4\).

Control Limits

  • \(X\text{-bar}\) and \(R\): Center \(\bar{\bar{X}} = \frac{1}{m}\sum \bar{X}_i\); \(\bar{R} = \frac{1}{m}\sum R_i\); \(\hat{\sigma}_{\text{within}} = \bar{R} / d_2(n)\). \[\text{UCL/LCL}_{\bar{X}} = \bar{\bar{X}} \pm 3 \frac{\hat{\sigma}_{\text{within}}}{\sqrt{n}} = \bar{\bar{X}} \pm A_2 \bar{R}; \quad \text{UCL}_R = D_4 \bar{R}, \quad \text{LCL}_R = D_3 \bar{R}\]
  • \(X\text{-bar}\) and \(S\): \(\bar{s} = \frac{1}{m}\sum s_i\); \(\hat{\sigma}_{\text{within}} = \bar{s} / c_4(n)\). \[\text{UCL/LCL}_{\bar{X}} = \bar{\bar{X}} \pm A_3 \bar{s}; \quad \text{UCL}_S = B_4 \bar{s}, \quad \text{LCL}_S = B_3 \bar{s}\]
  • Individuals and MR: \(\overline{MR} = \frac{1}{m-1}\sum \lvert X_i - X_{i-1} \rvert\); \(\hat{\sigma}_{\text{within}} = \overline{MR} / d_2(2)\). \[\text{UCL/LCL}_X = \bar{X} \pm 3 \frac{\overline{MR}}{d_2(2)}; \quad \text{UCL}_{MR} = D_4(2) \overline{MR}, \quad \text{LCL}_{MR} = D_3(2) \overline{MR}\]
  • Attribute charts:
    • \(p\)-chart: \(\bar{p} = \frac{\sum D_i}{\sum n_i}\); \(\text{UCL/LCL} = \bar{p} \pm 3 \sqrt{\frac{\bar{p}(1-\bar{p})}{n_i}}\), clipped to \([0, 1]\).
    • \(np\)-chart: \(\text{Center} = n\bar{p}\); \(\text{UCL/LCL} = n\bar{p} \pm 3 \sqrt{n\bar{p}(1-\bar{p})}\), clipped to \([0, n]\).
    • \(c\)-chart: \(\text{Center} = \bar{c}\); \(\text{UCL/LCL} = \bar{c} \pm 3 \sqrt{\bar{c}}\), clipped to \([0, \infty)\).
    • \(u\)-chart: \(\bar{u} = \frac{\sum C_i}{\sum n_i}\); \(\text{UCL/LCL} = \bar{u} \pm 3 \sqrt{\frac{\bar{u}}{n_i}}\), clipped to \([0, \infty)\).

Run Rules

Standardized scores \(z_i = (X_i - \text{Center}) / \text{SE}_i\) are evaluated against: - Western Electric (WE1–WE4): - WE1: 1 point beyond 3 sigma. - WE2: 2 of 3 consecutive points beyond 2 sigma (same side). - WE3: 4 of 5 consecutive points beyond 1 sigma (same side). - WE4: 8 consecutive points on one side of the center line. - Nelson (N1–N8, Nelson 1984): - N1: 1 point beyond 3 sigma. - N2: 9 consecutive points on one side of the center line. - N3: 6 consecutive points steadily increasing or decreasing. - N4: 14 consecutive points alternating up and down. - N5: 2 of 3 consecutive points beyond 2 sigma (same side). - N6: 4 of 5 consecutive points beyond 1 sigma (same side). - N7: 15 consecutive points within 1 sigma (stratification). - N8: 8 consecutive points beyond 1 sigma on both sides without any in Zone C (mixture).

Process Capability

  • Within-subgroup capability: \[C_p = \frac{\text{USL} - \text{LSL}}{6 \hat{\sigma}_{\text{within}}}; \quad C_{pu} = \frac{\text{USL} - \bar{X}}{3 \hat{\sigma}_{\text{within}}}; \quad C_{pl} = \frac{\bar{X} - \text{LSL}}{3 \hat{\sigma}_{\text{within}}}; \quad C_{pk} = \min(C_{pu}, C_{pl})\]
  • Overall process performance: \[P_p = \frac{\text{USL} - \text{LSL}}{6 \hat{\sigma}_{\text{overall}}}; \quad P_{pk} = \min\left(\frac{\text{USL} - \bar{X}}{3 \hat{\sigma}_{\text{overall}}}, \frac{\bar{X} - \text{LSL}}{3 \hat{\sigma}_{\text{overall}}}\right)\]
  • Expected defect rates (PPM): Normal cumulative tail areas \(\Phi\left(\frac{\text{LSL} - \bar{X}}{\sigma}\right) \times 10^6\) and \(\Phi\left(\frac{\bar{X} - \text{USL}}{\sigma}\right) \times 10^6\).

Reading the results

  • Process reading block (Dashboard!A5:B9): Summarizes process health in plain language:
    • Stability: Indicates whether any subgroup exceeds 3-sigma control limits.
    • Run rules: Reports multi-point run rule violations (trends, runs, stratification).
    • Capability: Interprets \(C_{pk}\) using standard conventions:
      • \(C_{pk} \ge 1.33\): Capable under usual industrial guidelines.
      • \(1.00 \le C_{pk} < 1.33\): Marginally capable; process variation approaches tolerances.
      • \(C_{pk} < 1.00\): Not capable; expected defect rate is elevated.
  • Native Excel charts:
    • Process Location: Displays subgroup means (\(\bar{X}\)), individual values, or attribute counts against center lines and control limits. Flagged points identify special-cause occurrences.
    • Process Dispersion: Displays subgroup ranges (\(R\)), sample standard deviations (\(S\)), or moving ranges (\(MR\)). For attribute charts (\(p, np, c, u\)), dispersion limits are not defined and the second chart renders gracefully blank.
  • Run-rule signals table (Dashboard!A36:D46): Lists each flagged subgroup with its corresponding rule and description.

Saving, sharing and App mode

  • Saving: Save the workbook normally as an .xlsx file. Python logic and settings are preserved.
  • Sharing: When opened by colleagues with Python in Excel enabled, the template recalculates automatically upon input edits.
  • Task Pane (App mode): The Marimo task pane provides a compact 350 px view with tabbed navigation (Reading, Summary, Signals, Capability, Phase I), mirroring workbook tables.

Errors, empty states and limits

  • Non-numeric cells: Non-numeric data in measurement columns generates a visible error message naming the offending subgroup without breaking workbook execution.
  • Unequal subgroup sizes: \(X\text{-bar}\) and Range/S charts require constant subgroup sizes across rows. Jagged rows produce a descriptive error message.
  • Subgroup size limits: X-bar/R and X-bar/S require a constant subgroup size from 2 to 10 (a larger size is a blocking error, although the engine supports up to 50). Individuals and MR takes one value per row (\(n=1\)).
  • Minimum data volume: At least 2 subgroups or attribute samples are required (a blocking error). No warning is issued for small samples; a common practice is 20 to 25 or more subgroups before Phase I limits are locked. Phase I screening never removes below 2 subgroups.
  • Inverted specification limits: An LSL greater than or equal to USL is a blocking error: the workbook shows an error message and does not compute the charts.

Customizing as an author

Practitioners can modify thresholds directly in notebook.py inside the designated # --- Edit these rules --- section:

# --- Edit these rules ---
# Thresholds for process capability interpretation
CPK_CAPABLE_THRESHOLD = 1.33
CPK_MARGINAL_THRESHOLD = 1.00

# Maximum signals to display in the task pane and workbook table
MAX_SIGNALS_ROWS = 10

Editing these variables automatically updates plain-language interpretation sentences and display caps throughout the workbook.

Limitations and when to use this

  • Normal distribution dependence: Capability indices (\(C_p, C_{pk}\)) and PPM defect projections assume a normal distribution. If data are skewed or bimodal, standard capability calculations will be misleading. Non-normal methods (such as Box-Cox or Johnson transformations) are excluded.
  • Measurement systems: The template assumes measurements are free from excessive gage error. Gage R&R and measurement systems analysis (MSA) must be conducted separately.
  • Advanced SPC: CUSUM, EWMA, short-run, multivariate control charts, and automated rule selection are outside the scope of this template.
  • Industrial standards: \(C_{pk}\) thresholds (1.33 for capable and 1.00 for marginal, the notebook defaults) are industry conventions; they do not constitute regulatory or contractual certification.

Developer reference

Notebook implementation

The notebook reads workbook ranges through a single bf.inputs call:

inputs = bf.inputs(
    variables_data=bf.ref("Data!A4:K304", headers=True),
    attributes_data=bf.ref("Attributes!A4:C104", headers=True),
    settings_data=bf.ref("Settings!A4:B12", headers=True),
)

Outputs are published back to their respective spill rectangles in one call. Each list starts with a header row, which matches the header row of its output block in the workbook:

publication = bf.publish(
    outputs={
        "reading": [["Item", "Finding"], *reading_rows],
        "summary": [["Metric", "Value"], *summary_rows],
        "capability": [["Capability metric", "Value"], *capability_rows],
        "signals": [["Subgroup", "Chart", "Rule", "Description"], *signals_rows],
        "phase_one": [["Phase I parameter", "Value"], *phase_one_rows],
        "chart_data": [
            ["Subgroup", "Location", "Location Center", "Location UCL", "Location LCL",
             "Dispersion", "Dispersion Center", "Dispersion UCL", "Dispersion LCL", "Flag"],
            *chart_data_rows,
        ],
        "validation": [["Actual", "Difference", "Status"], *val_rows],
    }
)

How the calculation/model works

  1. Input validation: Ingests Data, Attributes, and Settings, trimming trailing empty rows and verifying numeric types.
  2. Chart computation: Dispatches to the shared engine module spc.py (xbar_r_chart, xbar_s_chart, imr_chart, p_chart, np_chart, c_chart, or u_chart).
  3. Phase I screening: If enabled, phase_one iteratively identifies 3-sigma violations, removes them from the active dataset, and recomputes limits until convergence.
  4. Run-rule evaluation: Evaluates standardized residuals against Western Electric (WE1–4) or Nelson (N1–8) rules.
  5. Capability analysis: Evaluates within-subgroup and overall standard deviation to calculate \(C_p, C_{pk}, P_p, P_{pk}\), and PPM defect rates.

Validation / expected results

All calculations are continuously verified on every run via the Validation sheet: - NIST/SEMATECH e-Handbook 6.3.2.1: Control chart constants (\(A_2, D_3, D_4\) for \(n=2\dots 10\), and \(c_4(10) = 0.9727\)) match printed reference values. (Note: NIST prints \(D_4(5) = 2.115\), whereas the exact mathematical value is 2.1145; both pass within the stated 0.001 tolerance). - NIST 6.3.2.2: Individuals and Moving Range example (\(\bar{X} = 50.81\), \(\overline{MR} = 1.8778\), \(\text{UCL} = 55.8041\), \(\text{LCL} = 45.8159\) with \(d_2 = 1.128\) override; exact limits \(55.8024 / 45.8176\)). - NIST 6.3.3.1: \(c\)-chart count example on 25 wafers (\(\bar{c} = 16.0\), \(\text{UCL} = 28.0\), \(\text{LCL} = 4.0\), wafer 24 flagged). - NIST 6.1.6: Summary capability worked example (\(\text{USL} = 20, \text{LSL} = 8, \text{mean} = 16, s = 2 \implies C_p = 1.0, k = 0.3333, C_{pk} = 0.6667, C_{pu} = 0.6667, C_{pl} = 1.3333\)). - Montgomery piston-ring regression guards: Grand mean \(74.0012\), \(\bar{R} = 0.0232\), \(\text{UCL}_{\bar{X}} = 74.0146\), \(\text{LCL}_{\bar{X}} = 73.9878\), \(\text{UCL}_R = 0.0491\). - NIST 6.3.3.2 \(p\)-chart regression guards: \(\bar{p} = 0.231333\), \(\text{UCL} = 0.410239\), \(\text{LCL} = 0.052428\), samples 15 and 23 flagged. - Overall status: Displays “53 of 53 checks passed” on row 59.

Architecture

[ Data (A4:K304) ]       [ Attributes (A4:C104) ]       [ Settings (A4:C12) ]
        |                          |                            |
        +--------------------------+----------------------------+
                                   |
                           bf.inputs(...)
                                   |
                                   v
                         [ notebook.py / Marimo ]
                                   |
                   +---------------+---------------+
                   |                               |
          [ Statistics Engine ]           [ Run-Rule Engine ]
          - xbar_r_chart                  - rule_violations
          - xbar_s_chart                  - Western Electric (WE1-4)
          - imr_chart                     - Nelson (N1-8)
          - p/np/c/u_chart                - phase_one screening
          - capability
                   |                               |
                   +---------------+---------------+
                                   |
                           bf.publish(...)
                                   |
        +--------------------------+----------------------------+
        |                          |                            |
        v                          v                            v
 [ Dashboard Sheet ]      [ ChartData Sheet ]          [ Validation Sheet ]
 - reading (A5:B9)         - chart_data (A5:J305)       - validation (G5:I59)
 - summary (A12:B25)               |
 - capability (C12:D27)            v
 - signals (A36:D46)      [ Native Excel Charts ]
 - phase_one (H36:I44)    - Location Chart (F4)
                          - Dispersion Chart (F19)

Changelog

  • v1.0.0 (Initial Release): Control Charts (SPC) field-workbook template implementing Shewhart variables and attribute charts, Western Electric and Nelson run rules, Phase I trial limits, process capability analysis, and automated validation against NIST e-Handbook benchmarks.