Template · Engineering
Tolerance stack-up analysis (worst case and RSS)
Worst-case and root-sum-square tolerance analysis for a chain of up to 15 dimensions, with pass or fail.
Downloads are included in the $19.85 yearly membership. Sign in
XLSXCSVODSXMLNumbers| Tolerance stack-up analysis (worst case and RSS) | |||||||||||
| Example data — replace the blue input cells with your own. | |||||||||||
| Gap specification | |||||||||||
| Gap name | Clearance, bracket to housing | Gap = Σ direction × dimension. Direction +1 adds a dimension to the gap; −1 subtracts it. | |||||||||
| Lower limit of gap (mm) | 0.20 | Tolerances are entered as positive numbers: plus and minus from the nominal. | |||||||||
| Upper limit of gap (mm) | 1.20 | ||||||||||
| Dimensions (up to 15 rows; leave unused rows empty) | |||||||||||
| # | Dimension | Direction (+1 or −1) | Nominal (mm) | Plus tolerance (mm) | Minus tolerance (mm) | Mean shift (mm) | Half tolerance (mm) | Mean (mm) | Minimum (mm) | Maximum (mm) | Worst-case low (mm) |
| 1 | Housing pocket depth | 1 | 30.00 | 0.06 | 0.06 | 0.000 | 0.060 | 30.000 | 29.940 | 30.060 | 29.940 |
| 2 | Boss height on housing | 1 | 3.50 | 0.05 | 0.05 | 0.000 | 0.050 | 3.500 | 3.450 | 3.550 | 3.450 |
| 3 | Bracket flange thickness | -1 | 6.00 | 0.05 | 0.05 | 0.000 | 0.050 | 6.000 | 5.950 | 6.050 | -6.050 |
| 4 | Mounting plate thickness | -1 | 4.00 | 0.03 | 0.03 | 0.000 | 0.030 | 4.000 | 3.970 | 4.030 | -4.030 |
| 5 | Gasket, compressed thickness | -1 | 1.50 | 0.04 | 0.04 | 0.000 | 0.040 | 1.500 | 1.460 | 1.540 | -1.540 |
Showing the first 16 of 30 rows and 12 of 16 columns. Cells with formulas show the formula on hover.
| Tolerance stack-up results | |||||
| Calculated from the Dimensions tab. Change the inputs there. | |||||
| Gap and limits | |||||
| Lower limit (mm) | 0.200 | ||||
| Upper limit (mm) | 1.200 | ||||
| Specification width (mm) | 1.000 | ||||
| Number of dimensions | 9 | ||||
| Worst case (all tolerances at their limits together) | |||||
| Nominal gap (mm) | 0.700 | ||||
| Mean gap, with mean shifts (mm) | 0.800 | ||||
| Worst-case minimum gap (mm) | 0.370 | ||||
| Worst-case maximum gap (mm) | 1.230 | ||||
| Worst-case total spread (mm) | 0.860 | ||||
| Margin to the lower limit (mm) | 0.170 |
Showing the first 16 of 49 rows and 6 of 6 columns. Cells with formulas show the formula on hover.
| Tolerance stack-up analysis (worst case and RSS) |
| Notes: what this workbook does, how to use it and the method behind it. |
| What it does |
| Adds up a one-dimensional chain of up to 15 dimensions to find the gap between two parts, and tests the gap against its lower and upper limits by worst-case and root-sum-square (RSS) methods. The example is a clearance between a bracket an… |
| How to use it |
| 1. On the Dimensions tab, enter the gap name and its lower and upper limits. |
| 2. List each dimension in the chain. Enter direction +1 if the dimension adds to the gap, or −1 if it subtracts from it. |
| 3. Enter the nominal and the plus and minus tolerances as positive numbers. Asymmetric tolerances are allowed. |
| 4. Read the worst-case and RSS results on the Results tab, with the margins to each limit. |
| 5. Use the contribution table to see which dimension dominates the variation, and tighten that one first. |
| Formulas and method |
| Direction d_i = +1 or −1. Nominal gap = Σ d_i × nominal_i. |
| Mean shift = (plus − minus) ÷ 2. Mean_i = nominal_i + mean shift. Half tolerance T_i = (plus + minus) ÷ 2. |
Showing the first 16 of 34 rows and 1 of 1 columns. Cells with formulas show the formula on hover.
What does this template do?
This workbook adds up a chain of up to 15 dimensions and tests the resulting gap against its limits. It uses two methods. Worst case assumes every tolerance is at its limit at the same time. Root sum square (RSS) treats the dimensions as independent and combines their spread statistically. The example is a clearance between a bracket and a housing, built from nine dimensions.
Each dimension has a direction of +1 or −1, a nominal value and plus and minus tolerances. Asymmetric tolerances are handled by shifting the mean of the dimension and keeping its half width, so the worst-case and RSS results still follow the tolerance band.
The Results tab shows the nominal and mean gap, the worst-case and RSS ranges, the margins to each limit, a pass or fail for each method, and each dimension's share of the variance. The largest contributor is named, so you know which tolerance to tighten first.
Use it to compare tolerance schemes early in design. The RSS result is a statistical estimate that depends on the assumptions in the Notes tab, so it does not guarantee a yield.
What’s inside
- Up to 15 dimensions, each with direction +1 or −1
- Asymmetric tolerances handled by mean shift: (plus − minus) ÷ 2
- Worst case from the sum of low and high contributions for each dimension
- RSS half width = √ΣT² and share of variance = T² ÷ ΣT² for each dimension
- Pass or fail for both methods, with margins and the largest contributor
Which tabs does the workbook have?
| Tab | What it holds |
|---|---|
| Dimensions | Gap limits and the dimension table: direction, nominal, tolerances, mean shift, limits and worst-case contributions. |
| Results | Worst-case and RSS ranges, margins, pass or fail, contribution by dimension, and the largest contributor. |
| Notes | What the workbook does, how to use it, the method, the assumptions and the professional-verification note. |
What formulas does this template use?
This template holds 252 formulas in 403 cells across 3 tabs, so 63% of its cells calculate. They use 10 distinct functions; the longest formula is 118 characters and 83 of them read from another tab.
| Function | Uses | What it does |
|---|---|---|
IF | 260 | one result when a test is true, another when false |
RANK | 15 | position of a value in a list |
SUM | 5 | adds numbers |
AND | 3 | true when every test is true |
MAX | 2 | largest value |
COUNTA | 1 | counts non-empty cells |
INDEX | 1 | value at a position in a range |
MATCH | 1 | position of a value in a range |
SQRT | 1 | square root |
SUMPRODUCT | 1 | multiplies matching entries, then adds them |
Counted from the workbook itself. Only functions that Excel, LibreOffice Calc, Google Sheets and Apple Numbers evaluate the same way are used, so the formulas survive every download format.
How do you use it?
- On the Dimensions tab, enter the gap name and its lower and upper limits.
- List each dimension with direction +1 if it adds to the gap, or −1 if it subtracts.
- Enter the nominal and the positive plus and minus tolerances in millimetres.
- Read the worst-case and RSS results and their margins on the Results tab.
- Use the contribution table to find the dimension that dominates and tighten it first.
What is it good for?
- Checking a clearance or interference fit in an assembly
- Comparing worst-case and RSS tolerance schemes early in design
- Finding which tolerance drives the variation in a stack
- Teaching the difference between worst-case and statistical tolerancing
Questions about this sheet
What does direction mean?
A dimension with direction +1 adds to the gap. Use −1 for a dimension that subtracts from it. The sign convention must match the loop you draw.
How are asymmetric tolerances treated?
The mean of the dimension moves by half the difference between the plus and minus tolerances. The half width stays the same, so the worst-case and RSS limits still bracket the dimension.
Is the RSS result a guarantee?
No. RSS assumes independent dimensions with normal distributions and a centred process. It estimates the ±3σ spread, and real processes can break those assumptions.
Does it handle parallel or angular chains?
No. It handles one linear chain. Geometric tolerances such as position and runout need a separate analysis.