# Need a Gage R&R Template in Excel? QI Macros Has One!

## You Don't Have to be an Expert to Conduct a Gage R&R Study

#### QI Macros Gage R&R Template

1. Click on QI Macros menu > DOE Gage R&R > Gage R&R.
2. Conduct your Gage R&R Study and enter the measurements in the template.
3. Analyze results

### QI Macros includes an easy to use Gage R&R template that:

• performs all of the calculations
• interprets the results for you (acceptable or needs improvement)
• is compatible with AIAG Measurement Systems Analysis - MSA, 4th ed.

### Gage R&R Template Input Area

Per AIAG recommendations, the template is designed for up to 3 Appraisers, 5 trials and 10 parts.

After you input your data, just scroll down to view the results.

### Gage R&R template uses three methods to evaluate your measurement system

It also draws a series of charts to visualize your results.

### Average and Range Method Results in the Gage R&R Template

1. Key cells are shaded turquoise, yellow or red, based on their values:
2. A message regarding Gage System acceptability and recommendations are provided.
3. Turquoise = This aspect of your Gage System is acceptable
Yellow = This aspect of your Gage System MAY be acceptable
Red = This aspect of your Gage System needs improvement

4. NDC Calculation in row 53 indicates whether you have enough part variation for a valid test:

### Specification Tolerance Method Results in the Gage R&R Template

To utilize this method, manually input your Spec Tolerance into cell B:45 of the MSA 4th templates - this will override the automatically calculated Spec Tolerance output.

And once your Spec Tolerance has been inputted, the calculations for % EV, % AV, and %R&R are then displayed in column C, just next to the Average and Range method results in column B.

NOTE: This method can be used as an alternative to the Average and Range method, if you do not have enough Part Variation to perform a successful Gage R&R with an acceptable NDC.

### Historical Variation in the Gage R&R Template

Added in the January 2023 version of QI Macros, the Historical Variation output can be found in cells B46, along with cells D49 through D58:

This value is calculated as 75% of the calculated or inputted Spec Tolerance found in cell B45. And while 75% of the tolerance is the default, you can input your own Historical Variation, should you have it already calculated.

However, please keep in mind that in order to use Historical Variation, the process you are getting your calculated information from must be in statistical control.

"If the process variation is known and its value is based on Six Sigma, then it can be used in place of the total study variation (TV) calculated from the gage study data." AIAG MSA 4th Edition, pg. 121.

NOTE: The Historical Variation is only included in the MSA 4th template - it will not be included in the 30 Parts template.

### ANOVA Method Results

Select the Constant you would like to use for your study (6 or 5.15):

6 (AIAG Standard): Introduced in AIAG 3rd Edition, this constant assumes that 99.73% of data falls within the Standard Deviation of the mean.

5.15 (Alternate multiplier): Introduced in AIAG 1st Edition, this old AIAG standard assumes that 99% of data falls within the Standard Deviation of the mean.

If your p value in Appraiser x Part < 0.05 (or your Alpha), use With Interaction
If your p value in Appraiser x Part > 0.05 (or your Alpha), use Without Interaction

QI Macros calculations have been tested and validated against the AIAG MSA 4th edition. For more details view this Gage R&R validation test using Ford Motor Co data.

### Honest Gage R&R

Added to the Gage R&R MSA 4th and Gage R&R 30-Parts templates in our April 2022 release, this output references Dr. Donald J. Wheeler's belief that there are specific problems with Gage R&R studies:

An Honest Gage R&R Study is a simplified version of a Basic EMP Study (Evaluating the Measurement Process), and Dr. Wheeler believes it provides more in-depth outputs then standard MSA 4th studies - this is found in his book, "EMP III Using Imperfect Data" (pgs. 232-235).

QI Macros saved us a ton of time.
They take all the guesswork out of it.

- Richard Nardini, Apptec

### Control Charts, Histograms, Scatter and Box Plots to Visualize Your Results

QI Macros template creates numerous charts to help you analyze the results. The most popular are grouped to the right of the calculations in columns R:AB. The remaining are displayed below the calculations. For more information about these charts refer to the AIAG MSA manual.

### Evaluating Gage Stability Over Time

One way to detect changes in your gages over time is to take three measurements of a single reference part every week. Plot these measurements on an XbarR chart to see if the measurement starts to trend in one direction or another. Click here for more detailed instructions.