MEboost allows for easy analysis of a tolerance stack. In this article we'll show how to perform worst case, root sum squared (RSS), and Monte Carlo tolerance analysis in Excel using MEboost. If you want more background information on the three methods, check out the tolerance analysis article.
The MEboost tolerance analysis tools are capable of analyzing 1D/2D/3D dimension chains. This article will be limited to 1D analysis tools. For more on the multi-dimensional tools, refer to multi-dimensional tolerance analysis basics.
PART I
Basic Concepts
Before we start using MEboost, let's cover some basic concepts to understand how the software works.
Calculation Types
Depending on the situation, we can either calculate the stack height or the clearance of one or more parts fitting inside other parts.
Stack Height
The stack height calculation assumes parts are in a single stack and the stack height is calculated. For RSS, a minimum and/or maximum stack height is specified, and the rejection rate is determined from these values.
For worst case, the maximum and minimum stack heights are calculated based on either minimum material condition or maximum material condition.
Monte Carlo simulation treats each part’s dimension as a random variable that is repeatedly sampled. Each simulation trial, the sampled dimensions are added together to determine stack height. From all the trials we get summary statistics for the stack such as mean, variance, and percentiles. The probability of the stack being greater or less than a value can easily be determined from the simulation data.
Clearance
To calculate clearance, we need to define parts in the assembly as either inner or outer parts. Inner parts are assembled together to fit inside one or more outer parts. Clearance calculations determine the clearance between inner and outer parts. Outer part dimensions must be greater than inner part dimensions to have clearance.
The figure above shows an assembly with greatly exaggerated part clearances. Parts A and B must fit inside part C. For tolerance analysis in MEboost, we define parts A and B as inner parts and part C as an outer part.
Assemblies
For all tolerance analysis methods, an assembly needs to be defined. This includes the nominal part dimensions, information about how each part's dimension varies, and how the parts fit together.
Nominal Dimensions
RSS uses nominal dimensions as the mean value for the normal distribution. For worst case, nominal dimension is used to calculate the nominal stack height or clearance. Nominal dimensions are not used by Monte Carlo simulation since the probability distribution defines the dimension.
Dimensional Variation
RSS assumes that each part's dimension follows a normal probability distribution. Therefore, to define each dimensional variation we need to specify the mean (nominal dimension) and standard deviation.
Worst case defines variation by specifying the minimum material condition and maximum material condition.
Monte Carlo simulation accounts for variation by assigning a probability distribution to each dimension. MEboost allows for the normal, triangular, trapezoidal, and PERT distributions. The parameters of each distribution are specified to define the possible values for a dimension.
How the Parts Fit Together
Part type is used to determine how the parts fit together. Inner parts are assumed to mate together and are used to calculate stack height. Outer parts contain inner parts and are used when calculating clearance. In the example earlier, parts A and B are inner parts, and part C is an outer part. Clearance is calculated by:
Model Sheets
After an assembly is created, it can be saved to a worksheet called a model sheet. This sheet contains all information about the assembly and can be loaded at a later time for further analysis.
PART II
Running Each Type of Tolerance Analysis in Excel
Now we will go through each type of analysis. For each we will use the example assembly shown earlier where parts A and B have a nominal dimension of 1. Part C has a nominal inside dimension of 2.015.
RSS Tolerance Analysis
Click the Tolerance Analysis > RSS button in the MEboost Excel ribbon to open the RSS form.
Model Sheet
If a model already exists (a worksheet can be saved containing the model), it can be edited by selecting its name in the drop-down box. If a new model will be created, select "New model" from the drop-down and enter the name.
Calculate...
Select the type of calculation. We are calculating clearance inside part C so we will select "Clearance."
Parameters for Rejection Rate
Depending on the calculation type, some of the parameters in this area are disabled. In the example, we are using a clearance calculation so "Minimum clearance" is active. This is used to determine if an assembly will be rejected. If clearance is less than the minimum clearance, the assembly is rejected.
A negative minimum clearance means that some interference is acceptable. For example, a minimum clearance of -.001 means we can have an interference of up to .001 without rejecting the assembly.
If we had chosen to calculate stack height, we could use a minimum or maximum stack height as our rejection criteria.
Getting back to the example, we'll use 0 for minimum clearance.
Now let's click on the Assembly Stack tab. To add a part, enter the part name, nominal dimension (mean), standard deviation, and type. Then click the Add Part button. The part will appear in the assembly stack box.
To edit or delete a part, click on the part and click either the Edit Part or Delete Part button.
If you want to save the model, now would be a good time to click the Save Model button. If this is a new model, a new worksheet will be created, otherwise the existing worksheet will be overwritten.
Important
You still need to save the Excel workbook before closing it. The Save button does not save the workbook, and any changes to the workbook will not be saved on closing.
Now that everything is set up, it's time to run the analysis. Click on the Results tab. Then click the Calculate button and the following results appear.
Nominal clearance is the clearance based on nominal dimensions: 2.015 - (1 + 1) = .015.
The standard deviation of clearance is the square root of the sum of part variances (std. deviation squared).
In our example, 1.4524% of assemblies would have a clearance less than 0. Hence, they would be rejected.
A report of the results can be created in a new worksheet in the current workbook, or in a new workbook.
Worst Case Tolerance Analysis
Click the Tolerance Analysis > Worst case button in the MEboost Excel ribbon to open the worst case form.
Model Sheet
If a model already exists (a worksheet can be saved containing the model), it can be edited by selecting its name in the drop-down box. If a new model will be created, select "New model" from the drop-down and enter the name.
Calculate...
Select the type of calculation. We are calculating clearance inside part C so we will select "Clearance."
The next step is to add or edit parts. Click on the Assembly Stack tab. To add a part, enter the part name, nominal dimension, minimum value, maximum value, and type. Then click the Add Part button. The part will appear in the assembly stack box.
Minimum value is the minimum possible value of the dimension. Maximum value is the maximum possible value of the dimension.
To edit or delete a part, click on the part and click either the Edit Part or Delete Part button.
To run the analysis, click on the results tab. Then click the Calculate button and the following results appear.
The stack nominal dimension is the sum of all inner part nominal dimensions.
Maximum interference is when all parts are at maximum material condition: 2.01 - (1.01 + 1.01) = -0.01
Maximum clearance is when all parts are at minimum material condition: 2.04 - (.99 + .99) = 0.06
A report of the results can be created in a new worksheet in the current workbook, or in a new workbook.
Monte Carlo Tolerance Analysis
Click the Tolerance Analysis > Monte Carlo button in the MEboost Excel ribbon to open the Monte Carlo form.
Model Sheet
If a model already exists (a worksheet can be saved containing the model), it can be edited by selecting its name in the drop-down box. If a new model will be created, select "New model" from the drop-down and enter the name.
Calculate...
Select the type of calculation. We are calculating clearance inside part C so we will select "Clearance."
Simulation
Enter the number of simulation trials to run. However, before running the simulation, we need to create or edit the assembly.
Click the Assembly Stack tab. To add a part, enter the part name and select the type and probability distribution. Depending on the distribution selected, parameter boxes will appear. Enter the distribution parameters and click the Add Part button.
MEboost allows for the trapezoidal, triangular, normal, and PERT distributions. The uniform distribution can also be employed by using the trapezoidal distribution. A uniform distribution is a special case of the trapezoidal where the lower most likely value is set to the minimum, and the upper most likely value is set to the maximum.
To edit or delete a part, click on the part and click either the Edit Part or Delete Part button.
Once the assembly is correct, click on the Set-up tab. Then click the Run Simulation button to start the analysis.
After the simulation is completed, click on the Results page. Summary statistics will appear along with a histogram of the results. The values used to calculate statistics and shown on the histogram are based on either stack height or clearance, depending on the setting chosen on the set-up page. Simulation data for the last simulation is on the model worksheet.
Probability Analysis
To conduct a probability analysis of the model output (stack height or clearance) falling within a range of values use the Lower Set Point and Upper Set Point boxes.
To determine the probability above and below a single output value, enter that value in either the Lower Set Point or Upper Set Point box as shown below and click the Update button. The lower set point was used in the example shown below.
If the lower or upper set points are used without the other, they calculate probability differently to accomplish different goals.
When a value is entered in the lower set point, but not the upper set point, the probability will be calculated for less than or equal to the lower set point. The probability of greater than the lower set point will also be calculated.
When a value is entered in the upper set point, but not the lower set point, the probability will be calculated for less than the upper set point. The probability of greater than or equal to the upper set point will also be calculated.
The use of single set points is summarized in the table below:
| To Determine | Set Point to Use |
| Probability < set point | Upper |
| Probability <= set point | Lower |
| Probability > set point | Lower |
| Probability >= set point | Upper |
In the example above, the model output is clearance. We would like to know the probability of interference which would result in rejection. Entering 0 (zero clearance) in the lower set point box and clicking Update, we see that the probability of an interference fit is 2.664%.
To determine the probability between two output values, enter the lower value in the Lower Set Point box and the upper value in the Upper Set Point box as shown below and click the Update button. In the example, the probability of the clearance from 0 to .001 is 0.916%.
Situations Where a Full-Featured Monte Carlo Simulation Package is Required
The Monte Carlo tool in MEboost was deliberately simplified to make it easier to use. There may be situations where using a complete simulation tool, such as Simulation Master, may be desirable. In the following situations, a more general simulation tool may be needed:
- A part's dimension follows a probability distribution that is not included in MEboost.
- A part's dimension has a truncated distribution. For example, if a dimension follows the normal distribution, and there is 100% inspection, we could assume no out of tolerance parts make it to assembly. In this case we could use a truncated normal distribution where the tails are truncated at the upper and lower tolerance limits.
- You have a predetermined rejection rate, and want to find nominal dimensions or distribution parameters that will meet the rejection rate. Since each part's dimension is a random variable, this is an optimization with simulation problem. This requires a Monte Carlo simulation package with a built-in optimizer. Simulation Master can do this, for example.
Conclusion
Tolerance analysis in Excel is easily done with MEboost. You can perform RSS, worst case, and Monte Carlo analysis while saving the results in a worksheet. MEboost also has a tolerance calculator for setting limits or determining rejection rate for individual part dimensions. To learn more, refer to this article.
Excel is a registered trademark of Microsoft Corporation. Used with permission from Microsoft.