Tolerance Analysis in Excel with MEboost

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.



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.

MEboost Ribbon

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.

RSS Tolerance Analysis > Set-up Page

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.


 

RSS Tolerance Analysis > Assembly Stack Page

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.

RSS Tolerance Analysis > Results Page

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.

MEboost Ribbon

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

Worst Case Tolerance Analysis > Set-up Page

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.

Worst-Case Tolerance Analysis > Assembly Stack Page

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

Worst-Case Tolerance Analysis > Results Page

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.

MEboost Ribbon

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.

Monte Carlo Tolerance Analysis > Set-up Page

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.

Monte Carlo Analysis > Assembly Stack Page

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.

Monte Carlo Tolerance Analysis > Results Page

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 DetermineSet Point to Use
Probability < set pointUpper
Probability <= set pointLower
Probability > set pointLower
Probability >= set pointUpper

 

Single Point Probability Analysis

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

Range Probability Analysis

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.


Excel is a registered trademark of Microsoft Corporation.  Used with permission from Microsoft.