# Gage R&R - Is my sheet calculation wrong?

#### kedarg6500

##### Quite Involved in Discussions
Dear friends

I took a GRR study. But I could not get OK results. The GRR sheet I am using is as per MSA 4 edition example.

I have attached the GRR study I conducted recently. I don’t know whether sheet calculation is wrong, or study conducted is wrong. I may be wrong, but I could see consistency in data.

Kedar

#### Attachments

• 23.3 KB Views: 230
Last edited:

#### Jen Kirley

##### Quality and Auditing Expert
Staff member
A quick bump - can anyone answer this? I don't trust my memory of MSA formulas.

#### howste

##### Thaumaturge
Super Moderator
I took a GRR study. But I could not get OK results. The GRR sheet I am using is as per MSA 4 edition example.

I have attached the GRR study I conducted recently. I don’t know whether sheet calculation is wrong, or study conducted is wrong. I may be wrong, but I could see consistency in data.
The "Conclusion" cell is evaluating cell B54, which is % of total variation (30.08%). This has nothing to do with the tolerance of the part. I believe it should be looking at cell C54, which is % of tolerance (2.76%).

N

#### ncwalker

Your sheet calculation is wrong. The numbers for % Total Variation are correct, but the numbers for % of Tolerance are NOT correct. For all four percentages you are dividing the different variations by the total tolerance 0.007 - 0.003 = 0.004. You need to divide this result by 6 to get what is called Tolerance Variation. 0.004 / 6 = .00067 and use this as the denominator in all your % of Tolerance calculations.

Source: NOT the MSA manuals, I copy pasted your data into a sheet that I know works. Double check with the MSA.

#### normzone

Trusted Information Resource
Also, you are working in the Quality field.

You were either mad to take that step, or have been driven mad by the pressures.

Or both.

N

This is to "ncwalker" could you please provide a copy of the GRR that you know that works or tell us where it can be found? I have tried a few of the GRR forms (using excel) and all I could do is repeat what "kedarg6500" had calculated?

Try this one.

#### Attachments

• 38.2 KB Views: 261

#### kedarg6500

##### Quite Involved in Discussions
Your sheet calculation is wrong. The numbers for % Total Variation are correct, but the numbers for % of Tolerance are NOT correct. For all four percentages you are dividing the different variations by the total tolerance 0.007 - 0.003 = 0.004. You need to divide this result by 6 to get what is called Tolerance Variation. 0.004 / 6 = .00067 and use this as the denominator in all your % of Tolerance calculations.

Source: NOT the MSA manuals, I copy pasted your data into a sheet that I know works. Double check with the MSA.
Attached is MSA example from MSA 4 handbook. I am using the same excel sheet for calculation then how my calculations are wrong?

#### Attachments

• 32.2 KB Views: 594
Last edited:
N

#### ncwalker

Try this one.

This one is clearly dividing the tolerance by 6. But - it is ONLY reporting the % of Tolerance. Which means it is a suitable Gage R&R (I haven't really double checked everything, so I'm not certifying it is error free ....) for answering the question - "Is this gage suitable for checking these parts against this tolerance?" Which is the PPAP question - are you going to catch bad parts?

It does not report % Study Variation, which is the six sigma or DOE question. "Given these parts, is my gage good enough to detect the difference?" Which is the question you ask when doing a study. Remember - you're tweaking inputs looking for some optimization. You have to demonstrate you can detect the changes, which has nothing to do with the tolerance.

#### Attachments

• 57.2 KB Views: 231
N

#### ncwalker

Attached is MSA example from MSA 4 handbook. I am using the same excel sheet for calculation then how my calculations are wrong?

Your calculations are wrong because people aren't careful. Everyone goes and hacks together an Excel spreadsheet because due to computers today, it's "easy." Then, nobody goes back and TESTS their spreadsheets against known working models. "It's in a computer, therefore it must be correct."

Absolutely false.

You should question ANY spreadsheet you are given, ESPECIALLY if it isn't protected. Copies float around and people trample through them thinking they understand and the click and copy and try to do things quickly - next thing you know, broken formulas start appearing.

There's LOTS of reasons your calculations could be wrong.

That's, in fact, how I learned so much about Gage R&Rs. Suppliers were giving them to me, everyone had their "own" spreadsheet. One day, I had some free time, and started copy pasting the input data between sheets. And found that in MANY cases the spreadsheets did not agree. And I found that not all spreadsheets are created equal. Heck, I found calculation errors in the professional gage management software my own company bought.

So I made a gage R&R spreadsheet and locked it down. That way, I KNEW nobody had accidentally stepped on a formula. And I don't mean to say people were giving me intentional errors to make their gages look better, they were just careless spreadsheet errors, that propagated copy by copy. At that point, we no longer accepted Gage R&Rs other than those done in Minitab or those done on my Excel sheet. We found too many instances where the underlying math was wrong.

Same thing with capability studies.

Now, like I said, *I* may be wrong, but I don't think I am.

PS - It would be nice if the Cove created a library of TESTED sheets. I see a lot of people flinging up sheets. And they are OK (NOT protected). USE WITH CAUTION. So if, say, like this in these comments someone flings up a sheet, great. BUT ... it would be nice if we could submit an "official" sheet to the Cove, have it peer tested and certified, and then have a "Cove approved" tool section. I don't know how that would fit with their mandate or not.

#### Attachments

• 379.1 KB Views: 224
F Leak Tester Gage R&R Sheet Gage R&R (GR&R) and MSA (Measurement Systems Analysis) 0
A Excel Spread Sheet for Gage R&R wanted Excel .xls Spreadsheet Templates and Tools 1
S Gage R&R for Visual Inspection for Sheet Steel Gage R&R (GR&R) and MSA (Measurement Systems Analysis) 6
L Good Excel .xls sheet for Gage R&R that calculates both Anova and Range Excel .xls Spreadsheet Templates and Tools 9
T Gage R&R Sheet Formula error?! NDC less than 4 Gage R&R (GR&R) and MSA (Measurement Systems Analysis) 3
How to Validate a Gage R&R Spread Sheet General Measurement Device and Calibration Topics 3
K Gage R & R - How to enter appraiser results into an Excel .xls spread sheet Excel .xls Spreadsheet Templates and Tools 12
A Significance of X chart in AIAG MSA sheet for Gage R&R Gage R&R (GR&R) and MSA (Measurement Systems Analysis) 9
Gage R sheet - Can someone make sense of this data? Gage R&R (GR&R) and MSA (Measurement Systems Analysis) 8
J Seeking: Gage Calibration Data Collection sheet example General Measurement Device and Calibration Topics 3
D Gage R&R / Inspection Sheet - Example Excel .xls Templates Attached Excel .xls Spreadsheet Templates and Tools 39
Plug Gage Calibration Calibration and Metrology Software and Hardware 1
Gage R&R TMV Acceptance Criteria Gage R&R (GR&R) and MSA (Measurement Systems Analysis) 4
MSA for attribute relation gage Gage R&R (GR&R) and MSA (Measurement Systems Analysis) 3
Zero part to part variation - Gage R&R project Gage R&R (GR&R) and MSA (Measurement Systems Analysis) 15
Gage R&R and right way to measure Gage R&R (GR&R) and MSA (Measurement Systems Analysis) 16
Gage RandR on automated equipment. IATF 16949 - Automotive Quality Systems Standard 7
Gage calibration frequency, ISO and IATF - What are the requirements Calibration Frequency (Interval) 3
Difference between Test Method Validation and Gage R&R Qualification and Validation (including 21 CFR Part 11) 18
Determining Uncertainty from Gage R&R Gage R&R (GR&R) and MSA (Measurement Systems Analysis) 1
Correct Calibration Method for Dial Depth Gage General Measurement Device and Calibration Topics 6
Gage Block Wringing General Measurement Device and Calibration Topics 3
Gage R&R with NDC=1 Gage R&R (GR&R) and MSA (Measurement Systems Analysis) 19
Type 1 Gage R&R or something else? Reliability Analysis - Predictions, Testing and Standards 6
Should I perform Gage R&R only at the beginning of a new project? Gage R&R (GR&R) and MSA (Measurement Systems Analysis) 6
Thread Plug Gage Pitch GO Diameter out of spec AQL - Acceptable Quality Level 8
Gage R&R - Where am I going wrong? Part of a FAIR submission (Aerospace) Gage R&R (GR&R) and MSA (Measurement Systems Analysis) 2
Gage R&R Excel templates Gage R&R (GR&R) and MSA (Measurement Systems Analysis) 3
Gage R&R with more than 3 appraisers Gage R&R (GR&R) and MSA (Measurement Systems Analysis) 2
Gage R&R Template AIAG 4th Edition IATF 16949 - Automotive Quality Systems Standard 3
Attributes SPC study - Attributive control (Go gage) Statistical Analysis Tools, Techniques and SPC 5
Capability or Gage R&R Study for Leak Tester? Reliability Analysis - Predictions, Testing and Standards 15
% Tolerance - Type 1 study on the gages, then a gage R&R (ANOVA) Gage R&R (GR&R) and MSA (Measurement Systems Analysis) 4
Definitive answer on Type 1 vs Type 2 vs Type 3 Gage Study Gage R&R (GR&R) and MSA (Measurement Systems Analysis) 0
Gage Management and Gage R&R Software General Measurement Device and Calibration Topics 1
Gage R&R Study on Load Cells - Large chemical blending tanks IATF 16949 - Automotive Quality Systems Standard 1
Minitab Type 1 Gage Study on True Position Question Measurement Uncertainty (MU) 1
Gage R&R on alloy Gage R&R (GR&R) and MSA (Measurement Systems Analysis) 10
Need Help - Runout - Function Gage Inspection, Prints (Drawings), Testing, Sampling and Related Topics 7
Gage R&R studies for identical Devices - Need to confirm the requirement to perform them Reliability Analysis - Predictions, Testing and Standards 2
Stab Pin Size For Check Gage Inspection, Prints (Drawings), Testing, Sampling and Related Topics 3
Functional Gage for TOP applied at RFS Design and Development of Products and Processes 5
No reproducibility Error in Gage R&R? Reliability Analysis - Predictions, Testing and Standards 4
Template for Attribute Gage R & R wanted Gage R&R (GR&R) and MSA (Measurement Systems Analysis) 3
Warning about Deltronic Gage Pins - The "Certification of Accuracy" document General Measurement Device and Calibration Topics 13
A Gage type 1 study on CMM Capability, Accuracy and Stability - Processes, Machines, etc. 2
Gage R&R for Instant Measurement Machine Manufacturing and Related Processes 6
M Gage R&R with large inter part variance - Torque Gage R&R (GR&R) and MSA (Measurement Systems Analysis) 7
Do we need normal data for gage r&r studies? Gage R&R (GR&R) and MSA (Measurement Systems Analysis) 5
Do I need part variation while doing Destructive Variable Gage R&R MSA study Gage R&R (GR&R) and MSA (Measurement Systems Analysis) 19