Related Resources: Design Engineering

Gage Repeatability and Reproducibility Excel Spreadsheet Calculator

NOTE:

• Refunds are not awarded after excel files have been downloaded - review your membership agreement for details.
• This excel spreadsheet may contain macros which will need to be enabled in your excel application, see web page: Enable macros in Downloaded excel files
• Units utilized within calculators are either SI or Imperial (some enable both SI and Imperial) and member (you) are responsible for conversions. Ensure that you verify units utilized in excel application meet your requirements before downloading.

Engineering Excel Spreadsheet Downloads
Structural Design Engineering Resources
Mechanical Engineering Tolerance and Design

Gage Repeatability and Reproducibility Data Collection excel Spreadsheet Calculator

Note: Accessing this resource requires an active Annual Premium Membership with Engineers Edge

Download:

Gage Repeatability and Reproducibility Data Collection excel Spreadsheet Calculator

Preview

Gage Repeatability and Reproducibility Data Collection excel Spreadsheet Calculator

When to use this spreadsheet

1) This spreadsheet allows the user to perform a complete Gage Repeatability and Reproducibility (GR&R) study. Both Analysis of Variance and Xbar/Range calculations are performed. Up to three operators, ten parts and three trials can be accommodated. The GR&R is reported in terms of percent of study variation and percent of tolerance. The percentage of equipment variation, appraiser variation, part variation, and the number of distinct categories that can be distinguished are also displayed.

Instructions for Data Entry

1) Generally the coloring of the cells indicate their use. White cells are for data entry, yellow cells provide instruction, turquoise cells are headings and light green cells display data that can't / shouldn't be changed.

2) Enter the part number and / or name, the characteristic of the part being measured, the tolerance specification, the gage name, the gage number, the gage type, the name of the person conducting the study, the upper specification limit and the lower specification limit. Both upper and lower specification limits are required.

3) Determine the number of parts and the number of appraisers to be used. Conduct the study and enter the data in the appropriate rows. The spreadsheet will count the number of parts and appraisers automatically.

Reading the Results

1) The GR&R results are displayed as a percentage of part variation and percentage of tolerance. If no tolerance is entered, the Percent of Tolerance report will display "Enter Tol" in the data fields. Be sure to enter a value for the upper and lower specification, including zero, to ensure a proper result.

2) The percentage of equipment variation, appraiser variation, part variation, and the number of distinct categories that can be distinguished are displayed. Generally, an acceptable gage will have lower values for the equipment and the appraiser and higher values for the parts. In most industries, a GR&R less than 10% of the tolerance is considered acceptable while a GR&R greater than 30% is unacceptable. A GR&R between 10% and 30% should be investigated for improvement. The Number of Distinct Categories should be 5 or greater.

3) The Graphical Summary provides a full graphical analysis of the data. An Xbar and R chart is shown as well as Multi-vari charts showing Part Variation, Operator Variation and Part - Operator Interaction. A bar chart summarizes the total Gage R&R, Repeatability and Reproducibility for % Contribution, % Study Variation and % Tolerance. Guidance for using the Xbar and R charts follows:

Xbar Chart : The area between the control limits represents the measurement uncertainty and so at least half of the points should be outside the control limits.

R Chart : All points should be stable and in control.

4) The Numerical Summary displays the results of all calculations used in the ANOVA and Xbar/Range method of analysis. The Variance, Standard Deviation and Percent of Variance for each variation component are displayed. Gage R&R for Percent of Study Variation and Percent of Tolerance are displayed. The Percent of Tolerance is calculated using 5.15 standard deviations (99% of the variation) and 6.0 standard deviations (99.7% of the variation.) Both 5.15 and 6.0 are commonly used and so both are displayed. Also, the ANOVA table is displayed.

A Special Note Regarding K Factors

1) All calculations are performed using the K factors outlined in revision three of the AIAG MSA manual. The new K factors are just the old factors divided by 5.15 sigma. The Percent of Tolerance report accounts for the new K factors by including 5.15 sigma in the calculations.