How is % GRR calculated?

GRR is the square root of EV squared plus AV squared, total variation is the square root of GRR squared plus PV squared, and % GRR of study variation is GRR divided by TV. With a repeatability SD of 0.012 mm, a reproducibility SD of 0.009 mm and a part SD of 0.080 mm, GRR is 0.015 mm, TV is 0.0814 mm and % GRR is 18.4 percent, the same result the Gauge R&R Percentage calculator gives.

What % GRR is acceptable?

The AIAG MSA manual's guidance is under 10 percent generally acceptable, 10 to 30 percent possibly acceptable depending on the application, cost and customer, and over 30 percent unacceptable, with ndc of 5 or more. The template uses those values as defaults on Setup, but they are yours to change, and your customer's requirements come first.

Should I use % of study variation or % of tolerance?

Use % of study variation when the gage controls the process, because it compares measurement error with the real part-to-part spread, and % of tolerance when the gage only sorts parts against the specification. If your study parts do not span the process range, % study variation looks worse than it is and % tolerance is the fairer number.

Why is the multiplier 6 and not 5.15?

The current AIAG MSA manual expresses % tolerance with a 6 sigma spread (99.73 percent); older editions used 5.15 sigma (99 percent). The workbook uses 6 by default and lets you set 5.15 on Setup if your customer asks for it. With a GRR of 0.002 mm and a 0.050 mm tolerance, % tolerance is 24 percent at 6 sigma and 20.6 percent at 5.15.

What does the number of distinct categories tell me?

ndc = 1.41 x PV / GRR, truncated to a whole number, estimates how many non-overlapping groups of parts the gage can tell apart within the process spread. With PV of 0.0100 mm and GRR of 0.0020 mm, ndc is 7. Below 5 the gage cannot resolve the process well enough for SPC.

Does it do the ANOVA method?

No. It uses the Average and Range method, which is quick and transparent but cannot separate an appraiser-by-part interaction (one appraiser measuring some parts differently). If the average-by-part lines cross or the ranges differ a lot by appraiser, run the ANOVA method in a statistics package to confirm.

Quality Systems and Compliance

Gage R&R Study Excel Template

Enter a crossed gage study and get repeatability, reproducibility, % GRR of study variation and of tolerance, and the number of distinct categories, with charts that show where the error comes from.

Free, and a plain .xlsx with no macros to enable. A CSV with live formulas and a printed how-to guide come with it.

Overview

This gage R&R template is a working Excel workbook for quality and metrology engineers who need a measurement system analysis they can defend. It ships with a complete example study, ten parts measured three times by three appraisers with a micrometer at a fictional machine shop, so every result and chart is filled in when you open it; replace the example rows with your own and everything recalculates.

The workbook follows the Average and Range method of the AIAG Measurement Systems Analysis manual. Repeatability (EV) comes from the average range of repeat readings, reproducibility (AV) from the spread of the appraiser averages corrected for repeatability, and part variation (PV) from the range of the part averages, each scaled by a public d2* constant for the study size. Gage R&R combines EV and AV, total variation combines GRR and PV, and every component is reported as a percentage of the study variation, as a variance share and as a percentage of the tolerance, together with the number of distinct categories.

In the example the gage takes about 19 percent of the study variation and 24 percent of the tolerance, both in the band between the 10 and 30 percent limits, and repeatability is the larger source, which points at the instrument or fixturing rather than the appraisers. The acceptance limits are yours to set on Setup, and the Method sheet states what the Average and Range method cannot see, such as the appraiser-by-part interaction that the ANOVA method separates.

What this template includes

Study table: one row per appraiser per part with two or three trials, the row mean and range, and a Check column that flags missing trials, parts outside the study, duplicate rows and ranges above the range control limit
Average and Range method with the public K1, K2, K3 and D4 constants: repeatability (EV), reproducibility (AV), gage R&R (GRR), part variation (PV) and total variation (TV) as standard deviations
% study variation, % contribution and % tolerance for every component, with a spread multiplier you set (6 by default) and ndc = 1.41 PV / GRR
Dashboard with verdict tiles against the acceptance limits you set, the larger source of error in words, a components-of-variation chart and an average-by-part chart per appraiser
Average and range by appraiser and part tables, appraiser averages and part averages
Method sheet with every formula, assumptions and limits, and a Verify sheet that re-checks the formulas against an independent reference (Self-check: PASS)
CSV with live formulas for the study table; the dashboard, charts, dropdowns and self-check are in the .xlsx

Suggested use case

Use it to qualify a new gage or fixture, to support a PPAP or control-plan measurement system analysis, or to settle whether a measurement problem is the instrument or the people.

How to use it

On Setup, name the gage and the characteristic, set the trials (2 or 3), the number of parts (2 to 10), the tolerance and your acceptance limits, and list the 2 or 3 appraisers.
Pick parts that cover the process range, number them, and have each appraiser measure every part in random order without seeing earlier readings.
On Study, replace the example rows with one row per appraiser per part and the trials in measured order.
Fix anything the Check column names; Range above UCL means that part should be measured again.
Read the Dashboard: % GRR of study variation and of tolerance against your limits, ndc, and whether repeatability (equipment) or reproducibility (appraisers) dominates.

Frequently Asked Questions

How is % GRR calculated?: GRR is the square root of EV squared plus AV squared, total variation is the square root of GRR squared plus PV squared, and % GRR of study variation is GRR divided by TV. With a repeatability SD of 0.012 mm, a reproducibility SD of 0.009 mm and a part SD of 0.080 mm, GRR is 0.015 mm, TV is 0.0814 mm and % GRR is 18.4 percent, the same result the Gauge R&R Percentage calculator gives.
What % GRR is acceptable?: The AIAG MSA manual's guidance is under 10 percent generally acceptable, 10 to 30 percent possibly acceptable depending on the application, cost and customer, and over 30 percent unacceptable, with ndc of 5 or more. The template uses those values as defaults on Setup, but they are yours to change, and your customer's requirements come first.
Should I use % of study variation or % of tolerance?: Use % of study variation when the gage controls the process, because it compares measurement error with the real part-to-part spread, and % of tolerance when the gage only sorts parts against the specification. If your study parts do not span the process range, % study variation looks worse than it is and % tolerance is the fairer number.
Why is the multiplier 6 and not 5.15?: The current AIAG MSA manual expresses % tolerance with a 6 sigma spread (99.73 percent); older editions used 5.15 sigma (99 percent). The workbook uses 6 by default and lets you set 5.15 on Setup if your customer asks for it. With a GRR of 0.002 mm and a 0.050 mm tolerance, % tolerance is 24 percent at 6 sigma and 20.6 percent at 5.15.
What does the number of distinct categories tell me?: ndc = 1.41 x PV / GRR, truncated to a whole number, estimates how many non-overlapping groups of parts the gage can tell apart within the process spread. With PV of 0.0100 mm and GRR of 0.0020 mm, ndc is 7. Below 5 the gage cannot resolve the process well enough for SPC.
Does it do the ANOVA method?: No. It uses the Average and Range method, which is quick and transparent but cannot separate an appraiser-by-part interaction (one appraiser measuring some parts differently). If the average-by-part lines cross or the ranges differ a lot by appraiser, run the ANOVA method in a statistics package to confirm.