Tolerance Analysis

R

rustie

New Member
Joined
Mar 3, 2013
Messages
5
Hi,

I am trying to do something in excel which I am sure is a very simple task, but I cannot put my finger on it. I want to complete a linear tolerance stack up analysis. For any given dimension I will have three values. I will populate these dimensions into three columns. A Min, B Nominal and C Max, as shown below:

A B C
Dim 1 Min, Dim 1 Nominal, Dim 1 Max
Dim 2 Min, Dim 2 Nominal, Dim 2 Max
etc...

This list may end up with up to 150+ dimensions long. Sometimes it may only be 5-10 dimensions long.
I want to output:

Worst Case Minimum Resultant
Worst Case Maximum Resultant
Nominal Results

This will require some form of matrix addition (I think)? So that all possible combinations are calculated and the min and max are output.

Any help will be very much appreciated.

Thanks
 
Sort by date Sort by votes
Hi Rustie,

I think some more definition is required around "worst case". Are you after the largest value off the nominal, or the largest % variation off nominal. And if it is % is it % of the nominal dimension or of the tolerance band?

Teeroy
 
Upvote 0
Hi

An example would broaden the number of members that could try to help.

Some rows of input data, logic and expected results
 
Upvote 0
pgc01 said:
Hi

An example would broaden the number of members that could try to help.

Some rows of input data, logic and expected results
Click to expand...

Hi, please see this very simple example below. Now obviously A minimum is = sum total of 66.9+ -35.1 + 85.9. The problem comes when inputting hundreds of dimensions, the user will often make mistakes. I really want something i can drop all the numbers in, and guarantee the min and max results are correctly calculated. A fool proof method.

https://www.dropbox.com/s/i973ihqzv8izo6z/Tolerance Analysis.jpg
 
Upvote 0
Hi Rustie,

Try this formulas too (without helper column):

Layout

[TABLE="width: 195"]
<colgroup><col width="27" style="width: 20pt; mso-width-source: userset; mso-width-alt: 987;"> <col width="34" style="width: 26pt; mso-width-source: userset; mso-width-alt: 1243;"> <col width="47" style="width: 35pt; mso-width-source: userset; mso-width-alt: 1718;"> <col width="34" style="width: 26pt; mso-width-source: userset; mso-width-alt: 1243;"> <col width="17" style="width: 13pt; mso-width-source: userset; mso-width-alt: 621;"> <col width="57" style="width: 43pt; mso-width-source: userset; mso-width-alt: 2084;"> <col width="42" style="width: 32pt; mso-width-source: userset; mso-width-alt: 1536;"> <tbody>[TR]
[TD="class: xl63, width: 27, bgcolor: transparent"]Dim[/TD]
[TD="class: xl63, width: 34, bgcolor: transparent"]Min[/TD]
[TD="class: xl63, width: 47, bgcolor: transparent"]Nominal[/TD]
[TD="class: xl63, width: 34, bgcolor: transparent"]Max[/TD]
[TD="class: xl63, width: 17, bgcolor: transparent"] [/TD]
[TD="class: xl63, width: 57, bgcolor: transparent"]A-min[/TD]
[TD="class: xl64, width: 42, bgcolor: yellow, align: right"]117,7[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]66,9[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]67[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]67,1[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"]A Nominal[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]118,0[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]-34,9[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]-35[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]-35,1[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"]A Max[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]118,3[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]85,9[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]86[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]86,1[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]****[/TD]
[TD="class: xl63, bgcolor: transparent"]*****[/TD]
[TD="class: xl63, bgcolor: transparent"]********[/TD]
[TD="class: xl63, bgcolor: transparent"]*****[/TD]
[TD="class: xl63, bgcolor: transparent"]**[/TD]
[TD="class: xl63, bgcolor: transparent"]**********[/TD]
[TD="class: xl63, bgcolor: transparent"]*******[/TD]
[/TR]
</tbody>[/TABLE]


Formulas

Code: 
In G1

=SUMPRODUCT(SUBTOTAL(5,OFFSET($B$2:$D$2,ROW($B$2:$B$150)-ROW($B$2),)))

In G2

=SUM($C$2:$C$150)

In G3

=SUMPRODUCT(SUBTOTAL(4,OFFSET($B$2:$D$2,ROW($B$2:$B$150)-ROW($B$2),)))


Markmzz
 
Upvote 0
Old post, but for those who come by later, searching for similar answers, maybe this will help. I have done what you are doing in the past, and although you will likely get the worst case boundaries correct, you may find yourself also asking what the probability is of encountering such a case in the real world. I would suggest using a tool dedicated to performing tolerance analyses. Personally, I use QTA from QuickTol. It is an excel addon developed specifically for doing tolerance analyses. It does worst case, and also monte carlo and sensitivity. For more than just simple 1D analyses (think bonus tolerance, ramps, and such). Hope that helps.

-Jason


rustie said:
Hi,

I am trying to do something in excel which I am sure is a very simple task, but I cannot put my finger on it. I want to complete a linear tolerance stack up analysis. For any given dimension I will have three values. I will populate these dimensions into three columns. A Min, B Nominal and C Max, as shown below:

A B C
Dim 1 Min, Dim 1 Nominal, Dim 1 Max
Dim 2 Min, Dim 2 Nominal, Dim 2 Max
etc...

This list may end up with up to 150+ dimensions long. Sometimes it may only be 5-10 dimensions long.
I want to output:

Worst Case Minimum Resultant
Worst Case Maximum Resultant
Nominal Results

This will require some form of matrix addition (I think)? So that all possible combinations are calculated and the min and max are output.

Any help will be very much appreciated.

Thanks
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Min/Max
Replies
8
Views
196
Joe4
Joe4
I
  • Solved
VBA MaxIfs problem with criteria date < cell value
Replies
3
Views
89
Alex Blakenburg
A
L
  • Question Question
VBA to Get Sum of Data and Write to another Worksheet
Replies
8
Views
243
Fuji
F
P
  • Question Question
make an open link where macro asks everytime you click the button for the location & then remainig macro works as programmed
Replies
4
Views
161
Peter_SSs
Peter_SSs
O
  • Question Question
Basic sorting: my sub works for sorts on columns with text but for a column with formulas results are the bottom of the range being sorted.
Replies
0
Views
88
OaklandJim
O

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top