Gage Repeatability and Reproducibility (Gage R&R) in an Excel Spreadsheet

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. Excel can do it 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 studies quickly and reliably then save youself the headache and download 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 math’s. But when it’s time to perform actual Gage R&R studies I strongly recomend you do use a validated tool such as Minitab or my add-in.

 

Whats covered in this article:

 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 proved with a known statistical confidence.

Calculating Gage R&R using an Excel Spreadsheet

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
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
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.

 

Gage R&R ANOVA Process in Excel
Gage R&R ANOVA Process in Excel

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 function with which you can simply reference the study data and get the Gage R&R results.

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 (SSPart), the operator (SSOp), repeatability (SSRep) and total variation (SSTotal) 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 nOp is the number of operators, nRep is the number of replicate measurements of each part by each operator, nPart is the number of parts, is the grand mean, i is the mean for each part, j is the mean for each operator, xijk 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 (SSPart*Op) is the residual variation given by

$latex 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 (nPart), of operators (nOp) and of repetitions of the measurement of each part by each operator (nRep) 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 FPart*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 MSPart and MSOP but MSPart*Op is ignored and MSRep is now the residual variation and therefore SSRep 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 SSRep 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 (σ2Part) 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 (σ2Op) 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 (σ2Rep) is calculated using

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

Variance Component for Part by Operator Interaction

The variance component for part by operator interaction (σ2Part*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 (σ2Reprod) is equal to the operator variation (σ2Op). 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 (σ2GRR) is the sum of repeatability and reproducibility.
\sigma _{GRR}^{2} =\sigma _{Rep}^{2} +\sigma _{Reprod}^{2}

Total Process Variation

The total process variation (σ2Tot) 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.

35 thoughts on “Gage Repeatability and Reproducibility (Gage R&R) in an Excel Spreadsheet”

  1. Hello Dr. Muelaner

    Thank you for the explanation.
    I have a question, What is the criteria to accept a measuring system?.

    Thanks.

    1. That is for you to decide. Total Gauge R&R less than 20% of process tolerance is often used. But these are just arbitrary limits. I will be publishing a method of setting limits optimally to minimise cost in the near future so watch out for that.

  2. Hello Mr. Muelaner,

    thanks for this explenation and the spread sheet.

    I wanted please to ask, which cell in the spread sheet refers to the total process variation? In addition, which cell refers to the total Gage R&R?

    many thanks for the help
    Dan

    1. Cell R23 is the total variaton (exressed as a standard deviation) including the actual part variation as well as the measurement variation. U23 is the total Gage R&R

  3. Hello Mr. Muelaner,

    I recently followed your solution for a Gage R&R and I’m trying to figure out an error with my own data. Assuming the numbers are the same except the fact that the Repeat ID is the same across the board (say 3 times each), I am getting #DIV/0 in a bunch of cells. Did I input a formula incorrectly or is that by design? Is there a way to get around that? Much obliged by your article, thanks.

    Elliot Maire

  4. Dr. Muelaner,

    When I use my data, I get a negative value in cell S4. This causes problems in cells O8:S8 and beyond. If I make cell S4 the absolute value [ =ABS(R4-O4-P4-Q4) ] then I get rid of the #NUM! errors, but the values and results for the rest of the sheet make no sense.

    Can you help me understand what a negative value in S4 means, how to deal with it, etc.?

    1. Can you email your spreadsheet with the error so I can check it? Also what version of Excel are you using?

  5. How do I expand the worksheet to accommodate three appraisers with ten samples each, and not damage the formulae that exist within the worksheet now?

    1. Columns E to K, just copy the formulas down the columns. D24 must be moved so it’s summing all of the values. The ranges referenced in cells O3, P3, Q3, O4, P4, Q4 and R4 must then be draged out to cover all of your values. And that should work!

  6. Hi Dr. Muelaner,
    Should the repeat measurements be done same time or separate days or separate shifts?
    Regards,
    Bhanu

    1. Generally they should be done at the same time, this is always the case. Repeatability is the variation between measurements under all of the same conditions. But this exposes a weakness with the whole Gage R&R approach. It is defining ‘reproducibility’ as measurements by different operators. But the real definition of reproducibility would include under changed conditions so that might mean different operator, different time, different temperature, different part, different insturment, different method etc. You must consider what influences will vary for your measurement process. This is why an uncertainty evaluation approach is required.

  7. Everything looks great but confused as to what the guidelines are on the “Alpha to test interaction”. Why is .25 used and what influences this value? It appears to be manually entered. Please advise – thanks,

    1. This spreadsheet is really intended to explain the calculations. It’s not intended to be a foolproof template for implementing Gage R&R studies. An Alpha of 0.05 would be standard.

  8. In your J column the sum does not do what you state ie it is not column G minus grand mean…?

    Also why have you used such a poor GRR example?

  9. very transparent explanation, thank you.

    How does this change (if at all) when the number of repeats is not the same for all parts? Say part 5 is measured 3 times by operator 1 but only 2 times by operator 2. The SUM/IF/FREQUENCY will still calculate 3 for repeatability n in cell Q3.

  10. Hello Mr. Muelaner,

    In your Step 4: Sum each of the squared differences for all measurements
    Why in yours equations there aren’t nop or nRep…

    Thank you very much for this template

    1. there are, count functions are used to generate the number of operators and the number of repetitions within the equations, if you have another look I think you will find them

  11. Hi:
    Is there a specific reason why you are using an alpha of 0.25 instead of (standard) 0.05 in your excel template?
    Regards,
    -Deep

  12. Concerning the F-values, the ones for Part and Operator are both calculated F = MSP / MSPO and F = MSO / MSPO but then the F-value for Part*Operator (PO) is F = MSPO / MSRep. When I look at statistical programs like R and MiniTab, they appear to be calculating these F-values differently:
    Fp = MSP / MSRep
    Fo = MSO / MSRep
    Fpo = MSPO / MSRep
    I was wondering if you could shed some light on the hypotheses these F-tests are performing? Maybe that will help me understand why the spreadsheet is formatted the way it is.

    1. Have you tried running the same figures through MiniTab? I think you will get the same results as my spreadsheet (I’ve verified it against the standard MinitTab GRR calculation)

  13. Hello Mr. Muelaner,

    Thanks for your explanation, is indeed ‘uncovering’ the mystery around R&R calculations!
    I was wondering whether it is mandatory to measure all crossproducts? Would it be possible to calculate R&R from eg. using 4 setups, but only do repetitions on the first setup? Suppose you have 10 devices and are doing 4 repetitions, this would require only 70 measurements instead of 160? Thanks!

  14. Figured it out. I had to get the spreadsheet into the newest version on Excel. Everything works perfect 🙂 Sorry to bug

    1. Well Cabe what version did you goto I have this problem with Excel 2007…its weird since it works on Excel version at home which I guess must be 2011…

      1. I think Cabe has explained the problem, some of the functions have changed from Excel 2010 on. Any function that has a . in the middle will not work in earlier versions, you will need to replace these with the old functions to make it work

  15. Hey me again,

    I have been researching all day trying to get this figured out. I dont want to take too much of your time as I know you are most likely a very busy person.

    This is the problem that I see now. My 2007 version wanted FDIST instead of F.DIST and my version of Excel does not accept the “TRUE” at the end of the formula specified. It tells me that the formula should be like this;
    FDIST(x,degrees_freedom1,degrees_freedom2)

    Any ideas on how to work around this? Without the True statement I am not getting the correct answers.

  16. Hi Dr. Muelaner,

    I am having an issue with the P-Value in your spreadsheet. Everything else is working perfectly. Cells O8, P8 & S8 are filling in with #NAME? and gives me an error message; The formula contains unrecognized text.

    This is the formula in O8 now.
    =1-_xlfn.F.DIST(O7,O5,S5,TRUE)
    Excel tells me that it does not recognize the following part of the formula “_xlfn.F.DIST”.

    This formula differs slightly from the formula stated in the above document, which is =1-F.DIST(S7,S5,Q5,TRUE), but that formula isn’t even working for me even if I change the cell numbers to match the first formula mentioned.

    Help on this matter would be greatly appreciated.

  17. Hi Dr. Muelaner,

    A very helpful article. I am in the process of teaching myself measurement uncertainty so that I can calculate and report it for the calibration lab that I manage. Working towards ISO 17025. I only have one question, should I include Part Operator interaction? We have detailed set procedures that each cal tech is supposed to follow, so really each cal tech should be measuring with the same technique. Curious to know your thoughts on the matter

    1. It depends whether the Part Operator interaction is significant. A significance test is included in the spreadsheet.

      1. Dear Dr. Muelaner
        Thank you very much for your R&R topic.
        This spreadsheet is really intended to explain the calculations. Could you please tell me in which cell I have to place my Measurement Tolerances +/-0.01. (For example Diameter 5.04 +/- 0.01)

        1. This sheet does not include that. You could easily add a place for it and then calculate the Total Gauge R&R as a % of the tolerance.

  18. Dear Dr. Muelaner
    Thank you very much about your publication of the R&R topic. I really appriciate it. In fact, that helps me very much because actually I ‘m working on a new tool measuremetn that i have to evaluate its reproductivity and repeatability.
    I have a question for you: In general, the repeatability and reproducibility of any tool or machine are written in the instruction when we buy them or not? Thank you!

    1. A measurement instrument or gauge should come with a calibration certificate which will state the uncertainty of the instrument, a machine or tool will have some specified accuracy. These figures represent what the instrument or machine is capable of under ideal conditions. In the case of the instrument the calibration uncertainty includes repeatability for the calibration but not for subsequent measurements. In all cases Gage R&R should be done for your actual process. Read more about this here: http://www.muelaner.com/metrology/

Leave a Reply

Your email address will not be published. Required fields are marked *