Worst case vs RSS tolerance stack-up in a spreadsheet
Set up a one-dimensional tolerance stack-up in a spreadsheet, with loop signs, mean-and-tolerance conversion, worst case and RSS compared in a worked example.
A tolerance stack-up predicts how much a gap, fit or clearance varies when every part in the loop varies within its tolerance. Two methods cover most one-dimensional cases. Worst case adds the tolerances arithmetically and guarantees the result as long as every part is in tolerance. RSS (root sum of squares) adds them in quadrature, gives a much smaller result, and holds only if the dimensions behave statistically. One spreadsheet table can produce both numbers, so you can show the difference instead of arguing about it.
The example below is a five-part loop with a requirement of 3.80 to 4.30 mm. Worst case predicts 3.67 to 4.39 mm and fails. RSS predicts 3.864 to 4.196 mm and passes. Which answer to believe is the real subject of this article.
How do you set up the loop and sign convention for a stack-up?
Start with the characteristic you care about, for example the gap between a gear and the end of its housing. Trace a closed path from one side of the gap, through each part in contact, to the other side. Pick a positive direction along that path, then give every dimension a direction factor:
- +1 if making that dimension larger makes the gap larger (the housing bore).
- -1 if making it larger makes the gap smaller (every part that sits inside the housing).
The gap is the sum of direction times mean dimension. Tolerances carry no sign in the totals. Each one can push the gap up or down, so each contributes its magnitude.
How do you convert each tolerance to a mean and plus-minus value?
Drawings mix three shapes: equal bilateral (12.00 ± 0.05), unequal bilateral, and unilateral (8.00 +0.10/-0.04 or 6.00 +0.00/-0.12). Restate each one as a mean with an equal plus-minus tolerance. Put the part in column A, the nominal in B, the upper deviation in C, the lower deviation in D (entered as a negative number) and the direction factor in E. Then:
- Mean in F:
=B2+(C2+D2)/2 - Tolerance in G:
=(C2-D2)/2 - Mean gap:
=SUMPRODUCT(E2:E6, F2:F6)
The spacer 8.00 +0.10/-0.04 becomes a mean of 8.00 + (0.10 - 0.04)/2 = 8.03 with a tolerance of (0.10 + 0.04)/2 = ±0.07. The gear 6.00 +0.00/-0.12 becomes 5.94 ± 0.06.
This is not cosmetic. A stack's mean is built from the means of the parts, not from their nominals. In the example the nominal gap is 4.00 mm, but the mean gap is 4.03 mm because two parts have asymmetric limits. Centering the analysis on 4.00 would misplace every range that follows.
What does a worked tolerance stack-up example show?
The loop is a housing bore (positive) containing a bearing, a spacer, a shaft collar and a gear (all negative). The gap left over is the characteristic. All values are in millimeters.
| Part | Drawing dimension | Mean | ± Tol | Direction | Tol² | Share of RSS variance |
|---|---|---|---|---|---|---|
| Housing bore | 50.00 ± 0.10 | 50.00 | 0.10 | +1 | 0.0100 | 36.5% |
| Bearing | 12.00 ± 0.05 | 12.00 | 0.05 | -1 | 0.0025 | 9.1% |
| Spacer | 8.00 +0.10/-0.04 | 8.03 | 0.07 | -1 | 0.0049 | 17.9% |
| Collar | 20.00 ± 0.08 | 20.00 | 0.08 | -1 | 0.0064 | 23.4% |
| Gear | 6.00 +0.00/-0.12 | 5.94 | 0.06 | -1 | 0.0036 | 13.1% |
| Total | 4.03 (gap) | 0.0274 | 100.0% |
Mean gap: 50.00 - 12.00 - 8.03 - 20.00 - 5.94 = 4.03 mm.
- Worst case:
=SUM(G2:G6)= 0.10 + 0.05 + 0.07 + 0.08 + 0.06 = ±0.36. The gap ranges from 3.67 to 4.39 mm. - RSS:
=SQRT(SUMSQ(G2:G6))= √0.0274 = ±0.166. The gap ranges from 3.864 to 4.196 mm.
Against the requirement of 3.80 to 4.30 mm, worst case misses the lower limit by 0.13 and the upper by 0.09. RSS clears the lower limit by 0.064 and the upper by 0.104.
What does a worst case tolerance stack-up guarantee?
Worst case assumes every part sits at its limit in the direction that hurts most, all at once. It needs no assumption about how parts are distributed, only that each one passes inspection. If the gap must never go below a limit, such as a seal that must not be crushed or a shaft that must not seize, this is the method that can promise it.
The cost is tighter, more expensive tolerances, and the cost grows with the number of parts. For n parts with equal tolerances, worst case grows as n times the tolerance, while RSS grows as √n times the tolerance. With five equal tolerances RSS is already 45% of worst case (1/√5 = 0.447).
What does an RSS tolerance stack-up assume?
RSS is valid under four assumptions:
- The dimensions are independent. One part's size does not predict another's. Parts machined in one setup or cut from one bar of stock are often correlated.
- Each dimension is normally distributed.
- Each is centered on the mean of its tolerance zone.
- Each tolerance equals ±3σ of the process that makes the part, so σ = tolerance / 3.
Under those assumptions the gap is also normally distributed. Its standard deviation is the square root of the summed variances, and its ±3σ half-width equals =SQRT(SUMSQ(tolerances)). About 99.73% of assemblies fall within ±3σ, so about 0.27% (2,700 per million) fall outside it, even when everything is ideal.
In the example the requirement limits sit 0.23 and 0.27 mm from the mean, outside the ±0.166 RSS band, so the predicted fraction out of limits is about 16 per million. That figure is only as good as the four assumptions.
If a supplier reports process capability for a dimension, use it instead of assuming ±3σ. For a centered process, σ = tolerance / (3 × Cp), so a part with Cp = 1.33 has σ = tolerance / 4. Add the variances with =SQRT(SUMSQ(sigma_range)) and multiply by 3 for the stack tolerance.
For angles, lever arms or any dimension that does not map one-to-one onto the gap, multiply each tolerance by its sensitivity coefficient before using either method: =SQRT(SUMPRODUCT((coef*tol)^2)). Loops that span several directions, or geometric tolerances such as position under ASME Y14.5, need more than this table can do.
How do you use percent contribution to choose what to tighten?
The last column of the table shows each part's share of the total variance: =G2^2/SUMSQ($G$2:$G$6). Because RSS squares each tolerance, the largest tolerance dominates. The housing bore is 36.5% of the variance, the collar 23.4%, and the bearing only 9.1%.
Tightening the bearing from ±0.05 to ±0.04 would barely move the result. Tightening the housing bore from ±0.10 to ±0.06 reduces the RSS band from ±0.166 to ±0.145 (a 12.5% reduction) and the worst case from ±0.36 to ±0.32 (11.1%). Under worst case the bore's share is only 27.8% (0.10 / 0.36), so RSS punishes a single loose tolerance harder than worst case does.
How does a mean shift affect RSS results and Cpk?
RSS assumes each process is centered. Tool wear, a lathe set to run at the high side of a tolerance, or a casting that shrinks consistently all move a part's mean off center, and the stack mean moves with it.
Measure the effect with Cpk, using the RSS half-width as 3σ of the gap: =MIN(USL-mean, mean-LSL)/RSS. In the example, USL = 4.30, LSL = 3.80 and the mean is 4.03, so Cpk = 0.23 / 0.166 = 1.39.
Now shift the gap mean toward the lower limit by one gap standard deviation (0.166 / 3 = 0.055 mm). The distance to the lower limit falls from 0.23 to about 0.175, Cpk falls from 1.39 to 1.06, and the predicted share below 3.80 mm rises from about 16 per million to about 766 per million. The same loop and the same tolerances produce a very different risk once the mean moves.
Some teams inflate the RSS result by a factor such as 1.5 (often attributed to Bender) as a rough allowance for shifts. Here that gives ±0.248 and a lower bound of 3.78 mm, which would miss the 3.80 mm limit. Whether that is excessive or prudent depends on whether you have process data. If you do, use it. If you do not, treat the RSS result as optimistic.
Which tolerance method should you use, worst case or RSS?
| Worst case | RSS | |
|---|---|---|
| Formula | =SUM(tol) |
=SQRT(SUMSQ(tol)) |
| Assumes | Every part is within tolerance | Independent, normal, centered, ±3σ |
| Example result | ±0.36 mm | ±0.166 mm |
| Guarantee | 100% of assemblies | About 99.73% at ±3σ, if assumptions hold |
| Suits | Few parts, low volume, safety-critical, no process data | Many parts, high volume, capable and monitored processes |
A practical split: use worst case when a failure is dangerous or expensive to rework, when the volume is too low to rely on statistics, or when there are only two or three parts. Use RSS when there are many parts, the volume is high, and the processes are monitored. Running both and stating which one the drawing tolerances were allocated against is a reasonable practice. See what is RSS tolerance analysis for a short definition.
Tolerance stack-up checklist
- Write the requirement first, with both limits.
- Trace the loop and assign +1 or -1 to every part.
- Convert each tolerance to a mean and an equal plus-minus value.
- Compute the mean gap from the means, then both totals.
- Compare each range to the requirement limits, not to the nominal.
- Rank the parts by share of variance before deciding what to tighten.
- Compute Cpk, shift the mean by one standard deviation, and see whether the answer still passes.
- Record which method and which assumptions the drawing relied on.
The structure above (inputs, calculations, totals) follows the pattern in spreadsheet modeling: inputs, calculations, outputs. A ready-made version with example data is the Tolerance stack-up analysis template from Sheet Reserve. For another engineering calculation built the same way, see beam deflection in a spreadsheet.