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

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

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

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.

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.

#### 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*), repeatability (

_{Op}*) and total variation (*SS

_{Rep}*SS*) using the following equations.

_{Total}where *n _{Op}* is the number of operators,

*n*is the number of replicate measurements of each part by each operator,

_{Rep}*n*is the number of parts,

_{Part}*x̄*is the grand mean,

*x̄*is the mean for each part,

_{i}*x̄*is the mean for each operator,

_{j}*x*is each observation and x̄

_{ijk}_{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

#### 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:

#### Step 6: Calculate the Mean of the Squared Differences

The numbers of different parts (*n _{Part}*), of operators (

*n*) and of repetitions of the measurement of each part by each operator (

_{Op}*n*) 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

_{Rep}`=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.

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*but

_{OP}*MS*is ignored and

_{Part*Op}*MS*is now the residual variation and therefore

_{Rep}*SS*is calculated as

_{Rep}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 (σ^{2}_{Part}) is calculated using

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

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 (σ^{2}_{Op}) is calculated using

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

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 (σ^{2}_{Rep}) is calculated using

##### Variance Component for Part by Operator Interaction

The variance component for part by operator interaction (σ^{2}_{Part*Op}) is given by

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 (σ^{2}_{Reprod}) is equal to the operator variation (σ^{2}_{Op}). When there is significant interaction it is given by

##### Variance Component for Total Gage R&R

The total Gage R&R (σ^{2}_{GRR}) is the sum of repeatability and reproducibility.

**Total Process Variation**

The total process variation (σ^{2}_{Tot}) is the sum of total Gage R&R and part-to-part variation.

**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:

Pingback:Gage Studies and Gage R&R

Hi, I am wondering how DoF was re-calculated for Repeatability in the WITHOUT Interaction table to get the value of 14.

Also, what is the calculation for Number of Distinct Categories?

Thank you!

I am having the same issue Jo had back in 2016 where I get a negative value in cell S4. This causes problems in cells O8:S8 and beyond. Made cell S4 the absolute value [ =ABS(R4-O4-P4-Q4) ] then I get rid of the #NUM! errors but not sure if the values and results for the rest of the sheet remain valid. Is there a particular reason for a negative value in S4 and is there a best method to deal with it?

Using Excel 2016.

I understand the importance of determining the contribution of the part, appraiser and measurement equipment variation as a portion of the total variation. Is it important to understand the contribution of all the sources of variation as a portion of the tolerance as well? I assume this would give you an indication if the proposed test is capable of providing test results that can meet the tolerance. If this is important, can you propose a method for analyzing it?

Dear Dr. Muelaner,

Thank you so much for your topic and sample spreadsheet or GR&R. It was really a great help to me!

Question: What is the ideal, or acceptable, Total GR&R value?

Thanks,

Albert

Dear Sir,

I am not able to understand the “Total” in table 1. Please clarify how to calculate it.

Also what the mean of Part operator interaction please describe with formula if any.

Regards

Hemraj Singh

Dr. Muelaner,

Thank you for the detailed explanation. I am doing destructive testing. How would I develop a Gage R&R in this situation?

Thanks for the help.

Hello Dr. Muelaner

Thank you for the explanation.

I have a question, What is the criteria to accept a measuring system?.

Thanks.

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.

This article and sheet is life savior.

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

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

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

Hi Elliot, did you solve this? If not what version of Excel are you using?

For future readers of this article:

If you copy and paste the formulas from the text above and get a #DIV/0 error, you must replace the “double quote” marks (“) with keyboard input “double quote” marks (“)

In other words, copy and paste may require refinement, but typing it in should work ok.

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

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

I’m having the same problem. Did you ever figure it out?

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?

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!

Hi Dr. Muelaner,

Should the repeat measurements be done same time or separate days or separate shifts?

Regards,

Bhanu

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.

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,

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.

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?

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.

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

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

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

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.

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)

Yes it does agree with Minitab exactly…

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!

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

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…

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

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.

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.

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

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

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)

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.

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!

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: https://www.muelaner.com/metrology/