Gage Repeatability and Reproducibility, Gage R&R in Excel

2016/11/10 by Jody Muelaner 46 Comments

A Gage Repeatability and Reproducibility (Gage R&R) study is a great way to understand the variation in a measurement process. You don’t need special software like Minitab. You can do Gage R&R in Excel just fine. No macros or special functions are required but it does take a while to set everything up. If you just want to do Gage R&R in Excel quickly and reliably then save yourself a headache and get my Gage R&R Excel add-in. Many people using special software just plug in the numbers without understanding what is being calculated. If you follow this article you will fully understand the maths. But when it’s time to perform actual Gage R&R studies, I strongly recommend you do use a validated tool such as Minitab or my add-in.

What’s covered in this article:

What is Gage R&R?
Calculating Gage R&R using an Excel Spreadsheet

What is Gage R&R?

The variation in measurement results, or precision, is affected by many factors such as:

The operator
The equipment used
The calibration of the equipment
The environment
The time elapsed between measurements

As more of these factors are varied it can be expected that the variation in measurement results will increase. This leads to two extreme conditions of precision; repeatability and reproducibility. Repeatability is the minimum condition for precision in which the above factors are held constant while reproducibility is the maximum condition in which all of these factors vary. Often some intermediate measure of precision is relevant in which all of the possible factors effecting reproducibility are not varied since some of these will be maintained constant for the process under consideration.

When designing a Gage R&R study you need to decide which reproducibility conditions will vary for the actual measurement process. Will different operators be involved, or measurements take place in different locations, with different environments? The differences between the reproducibility conditions should represent differences encountered in the process being studied.

A typical Crossed Gage R&R study might involve 10 parts each being measured 3 times by 3 different operators in their own work area. By applying Analysis of Variance (ANOVA) it is then possible to determine the individual variance components due to the part variation, the repeatability of measurements and the reproducibility between different operators. The total Gage R&R variance is the sum of the variance for repeatability and the variance for reproducibility; this is an important component of a full uncertainty evaluation.

The calculation of variance components and standard deviations using ANOVA is equivalent to calculating variance and standard deviation for a single variable but it enables multiple sources of variation to be individually quantified which are simultaneously influencing a single data set.

A Gage R&R study should be used as part of a full uncertainty analysis and included in an uncertainty budget. This Hybrid Measurement Systems Analysis and Uncertainty of Measurement Approach for Industrial Measurement is a relatively new approach which enables conformance to be prove with known statistical confidence.

Calculating Gage R&R in Excel

Before reading the detailed description of the calculations it is recommended that you download the Gage R&R Spreadsheet Example. This can then be referred to while reading the article and once you have a full understanding of how it works it will be easy to adapt it to your own studies. For clarity the spreadsheet only uses 5 parts, 2 operators and 2 measurements per part/operator. For a real study at least 10 parts, 3 operators and 3 measurements is recommended.

The example spreadsheet is divided into two tables to make things clearer. The first table, shown below, has a separate row for each measurement made in the study. Columns A to D contain the inputs recorded for the measurements in the study. For each measurement this gives the Part ID, Operator ID, Repeat ID (whether it is the first or second measurement of the same part) and the recorded measurement value. The subsequent columns in this table are then used to calculate means and squared differences for each measurement as explained below.

Gage R&R ANOVA Table 1: Used to Input Study Data and Calculate Values for each Individual Measurement

The second table in the example spreadsheet, shown below, is used to calculate various values which summarize the complete data set. Intermediate calculations are used to finally calculate the variance components and standard deviations for the; variation between the actual parts; repeatability; the reproducibility due to different operators; and some other sources explained in the following sections.

Gage R&R ANOVA Table 2: Used to Calculate Values Summarizing the Complete Data Set including Variance Components and Standard Deviations

The Gage R&R ANOVA calculations follow these steps (with links to the detailed explanations below):

In Table 1:

Step 1: Calculate the Grand Mean (the mean of all measurement values)
Step 2: For each measurement calculate the mean for all measurements with the same Part ID, the same Operator ID and the same Part and Operator ID’s
Step 3: For each measurement calculate the squared difference between means

In Table 2:

Step 4: Sum each of the squared differences for all measurements
Step 5: Calculate Part Operator Interaction
Step 6: Calculate the Mean of the Squared Differences
Step 7: Calculate the significance of Part Operator interaction
Step 8: Decided whether to include Part Operator interaction in the model and if not calculate a different value for Mean Squared Difference for Repeatability
Step 9: Calculate Variance Components and Standard Deviations

The figure below gives a slightly more detailed overview of the Gage R&R ANOVA calculations before the full explanation is given for each below.

If this all seems a bit too much effort then you might prefer to simply download my Gage R&R Excel add-in. This puts all of these steps into a simple Excel formula which quickly and simply gives you results from a Gage R&R Study.

Gage R&R Excel add-in…

Step 1: Calculate the Grand Mean (the mean of all measurement values)

The Grand Mean is first calculated (Cell D24) which is simply the mean for all measurement values.

Step 2: For each measurement calculate the mean for all measurements with the same Part ID, the same Operator ID and the same Part and Operator ID’s

In the “Gage R&R Spreadsheet Example.xls” the mean for all measurements with the same Part ID is calculated in column E and with the same operator ID in column F. In column G the mean for each ‘factor level’ is calculated which is the mean for all measurements with the same part and operator ID’s and is used to represent repeatability.

The formula used to calculate the Mean for Part is repeated in column E of the spreadsheet on each row so the mean for the part is given for each measurement made, the formula in cell E3 looks like this:

Mean for Part:

=SUMIF(  A$3:A$22,   "="&A3, D$3:D$22 ) / COUNTIF( A$3:A$22,  "="&A3  )

It uses the SUMIF function to compare the Part ID for the current row (A3) with the Part ID for each row in turn (A$3:A$22). This formula is copied down each row, for all the rows where the Part ID is the same as the current row the measurement values (D$3:D$22) are summed. The COUNTIF function is then used to count the number of measurements with the same Part ID as the current measurement. Dividing the result of the SUMIF by the result of the COUNTIF gives the mean average for all measurement values with the same Part ID as the current measurement. All measurements with the same Part ID will have the same value for Mean for Part.

The formula used to calculate the Mean for Operator is the same as that used for Mean for Part with the only difference that the Operator ID is substituted for the Part ID:

Mean for Operator:

=SUMIF($B$3:$B$22,"="&B3,$D$3:$D$22) / COUNTIF($B$3:$B$22,"="&B3)

The formula used to calculate the Mean for Each Factor Level which represents repeatability is slightly different to that used for the Part and the Operator. SUMIFS is used in place of the SUMIF function to test for multiple criteria. In this case we need to sum the measurement values (D$3:D$22) if the Part ID’s (A$3:A$22) match the Part ID for the current measurement (A3) and the Operator ID’s (B$3:B$22) match the Operator ID for the current measurement (B3). The COUNTIFS is then used to divide the sum by the number of measurements matching this same condition. So for each measurement the mean of all measurements of the same part by the same operator is found.

Mean for Each Factor Level:

=SUMIFS(D$3:D$22,A$3:A$22,"="&A3,B$3:B$22,"="&B3)/COUNTIFS($A$3:$A$22,"="&A3,$B$3:$B$22,"="&B3)

Step 3: For each measurement calculate the squared difference between means

Once the relevant means for each measurement value have been calculated the grand mean is subtracted from each one and the difference is squared. These values are given in columns H, I and J. The total sum of squared differences, given in column K, is simply the square of the difference between each individual measurement value and the grand mean.

Step 4: Sum each of the squared differences for all measurements

The final stage in calculating the sums of the squared differences is simply to sum the values in the columns H, I, J and K, the resulting sums are given in Table 2 of the example spreadsheet in cells O3, P3, Q3 and R3 respectively.

These sums of squared differences are normally represented using the below equations for the part (SS_Part), the operator (SS_Op), repeatability (SS_Rep) and total variation (SS_Total) using the following equations.

$SS_{Part} =n_{Op} \cdot n_{Rep} \sum \left(\bar{x}_{i...} -\bar{x}\right) ^{2}$

$SS_{Op} =n_{Part} \cdot n_{Rep} \sum \left(\bar{x}_{j...} -\bar{x}\right) ^{2}$

$SS_{Part*Op} =n_{Op} \cdot n_{Rep} \sum \left(x_{ijk...} -\bar{x}\right) ^{2}$

$SS_{Rep} =\sum \sum \sum \left(x_{ijk} -\bar{x}_{ij} \right) ^{2}$

$SS_{Tot} =\sum \left(x_{ijk...} -\bar{x}\right) ^{2}$

where n_Op is the number of operators, n_Rep is the number of replicate measurements of each part by each operator, n_Part is the number of parts, x̄ is the grand mean, x̄_i is the mean for each part, x̄_j is the mean for each operator, x_ijk is each observation and x̄_ij is the mean for each factor level. When following the spreadsheet method of calculation the n terms are not explicitly required since each squared difference is automatically repeated across the rows for the number of measurements meeting each condition.

The sum of the squared differences for part by operator interaction (SS_Part*Op) is the residual variation given by

$SS_{Part*Op} =SS_{Tot} -SS_{Part} -SS_{Op} -SS_{Rep}$

Step 5: Calculate Part Operator Interaction

The sum of the squared differences for part by operator interaction, given in cell S3, is simply the residual variation given by:

$SS_{Part*Op} =SS_{Tot} -SS_{Part} -SS_{Op} -SS_{Rep}$

Step 6: Calculate the Mean of the Squared Differences

The numbers of different parts (n_Part), of operators (n_Op) and of repetitions of the measurement of each part by each operator (n_Rep) are given in cells O3, P3 and Q3 respectively. This is calculated in Excel by counting the number of unique number values in the column containing the ID numbers (not counting blank cells or text values) which, for the Part ID is given by

=SUM(IF(FREQUENCY(A3:A22,A3:A22)>0,1))

These values are then used to calculate the degrees of freedom (DF) for each factor using the below equations and given in cells O5, P5, Q5, R5 and S5 of the example spreadsheet.

$DF_{Part} =n_{Part} -1$

$DF_{Op} =n_{Op} -1$

$DF_{Rep} =n_{Part} \cdot n_{Op} \cdot \left(n_{Rep} -1\right)$

$DF_{Tot} =n_{Part} \cdot n_{Op} \cdot n_{Rep} -1$

$DF_{Part*Op} =(n_{Part} -1)(n_{Op} -1)$

It is then possible to calculate the mean squared difference for each factor by dividing the corresponding sum of the squared differences by the degrees of freedom. These values are given in O6, P6, Q6 and S6 on the example spreadsheet. At this stage the similarity with the calculation of a simple variance should be quite apparent.

Step 7: Calculate the significance of Part Operator interaction

The significance of the part by operator interaction on variation should then be determined by first calculating the F-statistic (in cell S7) which is the Mean Squared value for Part by Operator interaction divided by the Mean Squared value for Repeatability.

The probability of F_Part*Op being significant is then calculated in cell S8 by looking up the probability from an F-distribution where the value of the F-statistic is given in cell S7, the degrees of freedom for the numerator is given in S5, degrees of freedom for the denominator is in Q5 and a cumulative distribution is used:

=1-F.DIST(S7,S5,Q5,TRUE)

If the interaction is significant then the above values of the mean squared differences are used to calculate components of variance. The alpha value to test against is given in cell O10 and an if statement is used in P10 to state whether the interaction is significant.

Step 8: Decide whether to include Part Operator interaction in the model and if not calculate a different value for Mean Squared Difference for Repeatability

If the interaction is not significant then the same values are used for MS_Part and MS_OP but MS_Part*Op is ignored and MS_Rep is now the residual variation and therefore SS_Rep is calculated as

$SS_{Rep} =SS_{Tot} -SS_{Part} -SS_{Op}$

This value is calculated in cell Q15. When calculating the variance components, in Step 9 below, IF statements are used in cells O21, P21, Q21 and S21 to determine which value for SS_Rep should be used.

Step 9: Calculate Variance Components and Standard Deviations

The variance components for each factor can now be calculated. In some cases the equation used depends on whether Part by Operator interaction is included in the model and in these cases an IF statement is used to check the value in P10 and select the correct equation accordingly.

Variance Component for Part-to-Part Variation

When the part by operator interaction is significant the variance component for part-to-part variation (σ²_Part) is calculated using

$\sigma _{Part}^{2} =\frac{MS_{Part} -MS_{Part*Op} }{n_{Op} \cdot n_{Rep} }$

When the part by operator interaction is not significant the variance component for part-to-part variation is calculated using

$\sigma _{Part}^{2} =\frac{MS_{Part} -MS_{Rep} }{n_{Op} \cdot n_{Rep} }$

It is possible for these equations to return a negative value in which case the value should be set to zero, therefore the variance component for part by operator interaction is calculated in cell O21 using the formula:

=MAX(0,IF(P10="Interaction is not significant",(O17-Q17)/(P14*Q14),(O6-S6)/(P3*Q3)))

Variance Component for Variation due to Operator

When the part by operator interaction is significant the variance component for operator variation (σ²_Op) is calculated using

$\sigma _{Op}^{2} =\frac{MS_{Op} -MS_{Part*Op} }{n_{Part} \cdot n_{Rep} }$

When the part by operator interaction is not significant it is given by

$\sigma _{Op}^{2} =\frac{MS_{Op} -MS_{Rep} }{n_{Part} \cdot n_{Rep} }$

The Excel formula again selects the correct equation and sets negative values to zero:

=MAX(0,IF(P10="Interaction is not significant",(P17-Q17)/(O14*Q14),(P6-S6)/(O3*Q3)))

Variance Component for Repeatability

The variance component for repeatability (σ²_Rep) is calculated using

$\sigma _{Rep}^{2} =MS_{Rep}$

Variance Component for Part by Operator Interaction

The variance component for part by operator interaction (σ²_Part*Op) is given by

$\sigma _{Part*Op}^{2} =\frac{MS_{Part*Op} -MS_{Rep} }{n_{Rep} }$

Since this is only included when this factor is significant and negative values are set to zero the Excel function is

=MAX(0,IF(P10=”Interaction is not significant”,0,(S6-Q6)/Q3))

Variance Component for Reproducibility

When the part by operator interaction is not significant the variance component for reproducibility (σ²_Reprod) is equal to the operator variation (σ²_Op). When there is significant interaction it is given by

$\sigma _{Reprod}^{2} =\sigma _{Op}^{2} +\sigma _{Part*Op}^{2}$

Variance Component for Total Gage R&R

The total Gage R&R (σ²_GRR) is the sum of repeatability and reproducibility.
$\sigma _{GRR}^{2} =\sigma _{Rep}^{2} +\sigma _{Reprod}^{2}$

Total Process Variation

The total process variation (σ²_Tot) is the sum of total Gage R&R and part-to-part variation.
$\sigma _{Tot}^{2} =\sigma _{GRR}^{2} +\sigma _{Part}^{2}$

Standard Deviations

The standard deviations for each factor are simply the square root of the corresponding variance component. Hopefully, working through this process has given you a deeper understanding of how the Gage R&R calculations work. I would not, however, recommend using this approach to analyze production data. There are too many steps leaving room for human error. If you want a verified, low-cost and easy to use way to analyse Gage R&R data in Excel then I’d recommend having a look at my simple add-in for Excel: