I don't have one in Excel, But I created one in Mathcad.
Basically, you create a few colums ranges and tag them as
"Component, Dimension, tolerance, Mating Component,
MC dimension, MC dimension Tolerance, Assembly dimension, and Stackup". You will also need to tag an array to run the stackup calculations(Matrix) and in your formula, use the "MIN" and MAX" functions to define the Sigma sets.
Then after you run your data through the loops, you can reset the matrix array to use on the next dimension.
It takes a little bit to define the boundaries, but with a little elbow grease, it should work okay.
It took a few hours to get the mathcad version to work (mostly because Mathcad isn't open-ended in its calculations (back Calc).
NONLINEARMIND - I've written up several Tolerance stack up spreadsheets I could send you a template....What's your email?
As a "generic" spreadsheet you have to obviously enter the dimensions and tolerances and decide which "direction" they are in the loop.
The sheet I have does "worse case" and RSS (Root, sum, square)tolerance analysis.
ASMENUT- I have seen tolerance analysis sheets that purport to calculate six sigma -- I've never understood how...surely six sigma can only be calculated using "real" data i.e. from dimensional measurements of parts from a production run??? Whats the principal?
Also :- There is software avilable "C/ETol" and try
The context of a "Six Sigma" analysis is comprised of two different points of development
1) Using actual production parts to develop an "In-Process"
Analysis on a specific set of assembly criterion. This
is a great way to verify problem areas and also to
validate the existence of known problems.
2) During the design cycle, Six Sigma analysis is used to
prevent potential problems that CAN occur during
production. Remember that each component (part) has a
specific tolerance, based upon a company's
requirements. You also have a material tolerance (i.e
Thickness, Shrink), and of course you have the dreaded
assembly tolerances. Combining the all the possibilities
that one could encounter, enables you, the Engineer,
Designer (what have you), to be able to predict the
scenerios before hand, and therefore help to prevent
problems before they start.
A normal worse case Tol Stack up will let you identify problem areas of a design (Critical Dimensions). You should anyway include all relavent tolerances.
But "sigma" surely is the standard deviation from measured results....how do you predict the SD before the part is made??
If I put a tolerance on a moulded or machined part I would select that tolerance based on knowledge of the material and the process. A certian manufacturer (A) may even achieve that tolerance -- capable process.
If I switch supplier I know that manufacturer (B) with a similar process should be able to make parts within tolerance -- but I wont know how capable he is until I get the parts.
If I design a new part -- historical/product knowledge will give me idea of what sort of tolerances can be achieved, a tolerance analysis will tell me if it fits in the assembly. Put ultimately I wont know the process capability and thus SD (sigma) until its made??
In order to answer your questions, Let us first define what a "Six Sigma Analysis is:
The term “Six Sigma” is a statistical term that refers to 3.4 defects per million opportunities (or 99.99966 percent accuracy), which is as close as anyone is likely to get to perfect. A defect can be anything from a faulty part to an incorrect customer bill.
Such being the case, the Engineering Community developed this analysis suite about 20-25 years ago to define, diagnose and develop solutions to processes and designs.
Since its conception, many companies and individuals have expanded on its uses to include "preventive" analysis' (to help prevent problems at the start of the cycle).
In order to develop out a "Preventive" Sigma Analysis, you have to understand the specifics of how a process or in my case, Machine, is designed, setup, ...
You know that every component has a base set of tolerances, (i.e. shop tolerances (.x = +/- .5, .xx = .06, and so on).
But the materials and their processes have their own (ASTM tolerances on sheetmetal thicknesses, or casting tolerances, and so on). Understanding their link is key to analyzing at the front end.
If you know that a "Sand Casting" has an approximate shrinkage rate of 1-3%, and a sheetmetal component can be within +/- .004" of it's thickness (and still be of that gauge), and that the shop tolerances are some set of variables, you can take all of these and set them to a matrix.
By using the above information (vendors are integral to this type of Preventive Design), You can plug and play with the variables (hole size, boundary constraints, material tolerances, etc.) to see what the possibilities are and can be.
Another way to look at this type of Analysis is to think of a standard tolerance analysis, and then boost it 100-1000%.
In a standard tolerance analysis, you can define as many variables as you like, but only what is real (i.e. bore, shaft diameter, etc. (and their tolerances)).
A sigma analysis (as I use it) also uses empirical data (shrinkage, variations in material thicknesses and in some instances, compositions, base clearances, etc), to predict what is and could be.
In the 19+ years that I have been designing machines and processes, I have been able to validate this method of design a 100 times over. Machines that are designed with this method at the start of the cycle, work much better, with less changes or problems (opportunities), over their life.
Many years ago, I took a course (seminar) on a concept called "Six Sigma Anaylsis". In this seminar, they talked about SPC (Statistical Process Control) and other Metric-related processes, to help define, diagnose and eliminate errors in the processes. Although the approach was valid, in my mind, it was incomplete. Too many times had I been approached by vendors (and others) and asked if I could "Live with what they could achieve" (basically compromising Fit, Form and Function" with something less).
After a while of this nonsense, I helped develop a method that mixed the original concept of SSA and the standard tolerance analysis, to work with design, vendors and manufacturing, to create a machine that contained all the metrics and tolerancing at the beginning of the cycle. This allowed the vendors to see, up front, what was required and what we could live with, allowed the designs to be more definitive (yet flexible), and functional, and allowed the manufacturing people to build machines instead of rejecting due to bad "Parts". It has also bled out to the service side of the equation and more importantly to the end-user. The machines are more reliable, and in many cases, can be serviced in shorter time with less complications.
This in no way keeps opportunities from creeping up (Heck we are all human (I think)), and mistakes are made, but the concept is solid and even when opportunities arise, they are easier to address and even more so, easier to prevent from creeping up again.
Sorry for the long winded answer. This is still a new approach to design, and as such, many don't understand the concepts as well as others, so I fiqure that if I can get a few to understand, others will follow. (But I digress).
Asmenut,
I went to a seminar this summer where someone gave a talk on this approach, starting with "Voice of the Customer", numerical "experiments" to create a response surface, Monte Carlo analysis of all the tolerances for all the variables, and genetic algorithms for optimization to the desired parameters. Put together that way, it made good sense -- now I just need to figure out how to implement that stuff!
This is a template I've developed over the years while working on Projects with General Motors. It originated from a contact at GM who last I knew went to work for Whirlpool.
The template does not contain any macros. There is an RSS Toolbar addin that gives you much greater efficency in creating new sheets and updating the summary sheet. It automates a lot of the tasks. The biggest thing to be aware of with the Toolbar is that the Sheets need to end with a number in parenthesis (1). I have a colleague who simply renamed the sheets with just the numbers in parenthesis and this works also.
You should be able to go to the address listed in my previous submission and right click on the RSS Spreadsheet link and select save target as and save a copy of it to your own hard drive.
The sheets are password protected, but if you download the addin the same way and install it, there are 2 buttons on the toolbar that protect and unprotect all the sheets in the spreadsheet. They are the frowning face and the smiling face respectively.
If you don't want to download the toolbar, the pasword to unprotect the sheets is michael1.
![[thumbsup]](/data/assets/smilies/thumbsup.gif "[thumbsup] [thumbsup]")
