An Excel worksheet for a crossed variable gauge study: 10 parts, 3 operators, 3 trials each. It uses the average-and-range method, not ANOVA, and calculates repeatability (EV), reproducibility (AV), %GRR, %tolerance and the number of distinct categories.
New templates, charts and calculators for quality and manufacturing engineers, when they ship.
Quality and manufacturing engineers who must show a variable gauge is fit for use: for a control plan, a PPAP, a new gauge, or after a gauge is repaired or recalibrated with a large adjustment.
Tip: The K1, K2 and K3 constants sit in header cells. They are set for 3 trials, 3 operators and 10 parts. Keep that layout, or the constants no longer match.
Header
Part and characteristic, gauge, date, tolerance, the K1/K2/K3 constants, trials and parts
Readings
One row per part, three trials for each of operators A, B and C
Row calculations
Average and range per operator per part, and the part average across all 9 readings
Operator statistics
Average range and grand average for each operator, X̄diff and the part range Rp
Variance components
EV, AV, GRR, PV and TV in measurement units
Result
%EV, %AV, %GRR, %PV, ndc, %GRR of tolerance and the acceptance verdict
Example Study for Example Precision Co., a fictional machine shop, on a turned shaft with an outside diameter of 12.00 ± 0.05 mm (tolerance 0.10 mm), measured with a 0.001 mm micrometer. Every reading is invented for illustration.
| Part | Operator A | Operator B | Operator C | Part average |
|---|---|---|---|---|
| 1 | 11.972 / 11.973 / 11.972 | 11.973 / 11.973 / 11.973 | 11.972 / 11.970 / 11.970 | 11.9720 |
| 2 | 11.981 / 11.980 / 11.981 | 11.982 / 11.983 / 11.983 | 11.978 / 11.979 / 11.979 | 11.9807 |
| 3 | 11.989 / 11.989 / 11.989 | 11.989 / 11.988 / 11.989 | 11.989 / 11.985 / 11.985 | 11.9880 |
| 4 | 11.994 / 11.994 / 11.994 | 11.997 / 11.995 / 11.996 | 11.993 / 11.995 / 11.994 | 11.9947 |
| 5 | 11.997 / 12.000 / 12.000 | 12.001 / 11.999 / 12.001 | 11.996 / 11.995 / 11.998 | 11.9986 |
| 6 | 12.004 / 12.001 / 12.001 | 12.006 / 12.002 / 12.004 | 12.001 / 12.001 / 12.003 | 12.0026 |
| 7 | 12.007 / 12.007 / 12.008 | 12.009 / 12.009 / 12.010 | 12.008 / 12.007 / 12.007 | 12.0080 |
| 8 | 12.015 / 12.016 / 12.014 | 12.016 / 12.015 / 12.017 | 12.015 / 12.016 / 12.015 | 12.0154 |
| 9 | 12.021 / 12.021 / 12.020 | 12.023 / 12.024 / 12.024 | 12.021 / 12.021 / 12.018 | 12.0214 |
| 10 | 12.031 / 12.030 / 12.030 | 12.031 / 12.031 / 12.029 | 12.030 / 12.029 / 12.029 | 12.0300 |
| Average range A / B / C | 0.0013 / 0.0016 / 0.0020 mm |
|---|---|
| Operator averages A / B / C | 12.00103 / 12.00240 / 11.99997 mm |
| EV (repeatability) | 0.00096 mm |
| AV (reproducibility) | 0.00126 mm |
| GRR | 0.00159 mm |
| PV (part variation) | 0.01825 mm |
| %GRR of total variation | 8.7% |
| %GRR of tolerance | 9.5% |
| ndc | 16 |
| Verdict | Acceptable |
| Field | What to enter | Example |
|---|---|---|
| Tolerance (USL − LSL) | The full tolerance width. Leave it blank for a gauge used only for process control; %GRR of tolerance then stays empty. | 0.10 |
| K1, K2, K3 | Published average-and-range constants for 3 trials, 3 operators and 10 parts. Only change them if you change the layout. | 0.5908, 0.5231, 0.3146 |
| Part | A blind number. Operators should not be able to match a part to an earlier reading. | 7 |
| Operator trial readings | The value as read, to the gauge resolution. Do not round or average before entering. | 12.007 |
| Average / Range (calculated) | Per operator per part. One large range points at a misread or a part that moved in the fixture. | 12.0073 / 0.001 |
| Part average (calculated) | All 9 readings of the part. Rp, the spread of part averages, drives PV. | 12.0080 |
| %GRR | GRR as a share of total variation. Read with ndc; a low %GRR from too narrow a part spread is not a pass. | 8.7% |
| ndc | Number of distinct categories the gauge can tell apart across the part spread, truncated to a whole number. | 16 |
Average-and-range. It is easy to check by hand and matches the classic paper worksheet. It does not separate operator-by-part interaction; use the ANOVA method or the gage R&R calculator when you suspect that.
The common guideline is under 10% acceptable, 10% to 30% possibly acceptable depending on the application and cost, and over 30% not acceptable. Your customer may set its own limits.
It shows how many groups of parts the gauge can tell apart across the part spread. Fewer than 5 means the gauge cannot track process variation well enough for control, even if %GRR looks fine.
The constants change. For 2 trials K1 is 0.8862 and for 2 operators K2 is 0.7071. This sheet is laid out for 3 × 3, so blank operator columns break the averages. Keep the 3 × 3 × 10 layout or adapt the sheet.
Six standard deviations cover about 99.73% of a normal distribution. Some older studies use 5.15; state which one you use.
COPLAIN AI PLATFORM
Coplain turns your measurement procedure into a step-by-step work instruction, so every operator in the next gauge study measures the same way.
Generate yours automatically with Coplain →Related Tools & Templates