Trap 1 · The displayed value is not the value that decided
Excel stores numbers as IEEE 754 binary64 values. It displays at most 15 significant digits, and it rounds whatever you format, but only on screen. Two different things go wrong here.
Display rounding hides a real result. A cell formatted to two decimals shows 10.00 for a stored 10.004. If the decision compares the stored value, the printed record and the decision disagree. If it compares a rounded value, the rounding rule is buried in a cell format that nobody validated. Cell formatting is not a decision rule.
The binary residue flips results that sit exactly on a limit. Excel hides most residues: type =0.1+0.2=0.3 and Excel returns TRUE, because it compares numbers to about 15 significant digits. But a subtraction that cancels most of the digits can push the residue past that tolerance. Then a result that is exactly on the limit on paper falls on the wrong side:
=IF(10.2-10.1>=0.1,"ACTION","NO ACTION") → NO ACTION (widen the format: Excel shows 0.0999999999999996)
We tested twenty pairs of values exactly 0.1 apart in Microsoft Excel 16.0. Fourteen of the twenty returned NO ACTION. With a fixed reporting resolution, results land exactly on limits all the time. Without an explicit rounding rule, the binary residue decides those cases instead of your documented decision rule, in whichever direction the bits happen to fall.
Test: =IF(a-b>=0.1,"ACTION","NO ACTION") for 10.2−10.1, 3.3−3.2, 25.4−25.3, 37.6−37.5, 12.7−12.6, 5.3−5.2, 100.3−100.2, 0.3−0.2, 1.3−1.2, 2.3−2.2, 8.2−8.1, 4.6−4.5, 6.4−6.3, 9.7−9.6, 15.2−15.1, 0.2−0.1, 20.4−20.3, 7.9−7.8, 11.5−11.4 and 9.5−9.4. NO ACTION for all except 37.6−37.5, 0.3−0.2, 1.3−1.2, 6.4−6.3, 0.2−0.1 and 7.9−7.8. Microsoft Excel 16.0, default settings, 2 November 2026. A strict IEEE 754 evaluation gives fifteen: Excel's comparison tolerance rescues 0.3−0.2. That difference is why NormXL evaluates every decision both ways.
ISO/IEC 17020:2026A minimum wall stored as 4.5053571428571431 mm prints as 4.5 mm. Compare rounded values and a 4.46 mm pipe is accepted; compare stored values and a 4.50 mm reading is rejected while the report says it meets the minimum.
ISO/IEC 17025:2017A gravimetric result of exactly 35.0 mg/L is stored as 35.000000000025011 and fails a ≤ 35 mg/L limit while displaying "35.00".
ISO 13485:2016A Cpk that is exactly 1.33 on paper is stored as 1.3299999999999794, so =IF(C_pk>=1.33,"RELEASE","HOLD") holds a capable lot.
What NormXL does:
- evaluates every decision cell under Excel's conventions and in strict IEEE 754;
- prints both operands to 17 significant digits;
- records the rounding function, the side it applies to and the number of places kept;
- flags every decision that depends on digits beyond the displayed precision.
Trap 3 · Constants buried inside formulas
=C5*C6/(2*(C7*C8*C9+C5*0.4))+1.5, =STDEV.S(C4:C8)/SQRT(5)*2.776, =730/2^((55-22)/10). Every number typed into a formula is a design decision without a label, a source or an owner. When the code edition, the uncertainty budget or the test protocol changes, the input cells get updated and the literals don't.
ISO/IEC 17020:2026Y = 0.4 (B31.3 Table 304.1.1: ferritic steel up to 482 °C), mill under-tolerance 0.875, corrosion allowance 1.5, gravity 9.81: all typed into wall-thickness formulas.
ISO/IEC 17025:2017A calibration intercept and slope from last quarter, typed into fifty result formulas; a coverage factor typed as 2 where the effective degrees of freedom need 2.28.
ISO 13485:2016TRT typed as 22 °C in an ageing formula whose protocol says 25 °C: the chamber time comes out 17 days short.
Not every literal is wrong: the 2 in the B31.3 equation and the 0.6 in ASME's UG-27 are the code's own coefficients. But every undocumented literal is a finding waiting to happen.
What NormXL does: it extracts every numeric literal from every formula into a Constant Ledger, with the cell, the formula, the value and every occurrence, plus an empty "source" column for your reviewer. NormXL does not guess where your constants come from. It makes sure none of them can hide.
Trap 4 · No documented boundary or negative tests
Spreadsheets are tested on the happy path: realistic numbers, typed by someone careful. Production data is not like that:
- Text such as "N/A" or "<0.05" is skipped by
AVERAGE(), which silently changes n.
- In a comparison, any text ranks above any number, so
=IF(B22>=H22,"ACCEPT","REJECT") returns ACCEPT for a reading entered as "N/A".
- A blank is zero, so
=IF(B5<=10,"PASS","FAIL") returns PASS for a missing result.
IFERROR() turns a division by zero into a plausible number.- Data-validation rules set to "Warning" can be clicked past, and a paste bypasses them entirely.
- Templates travel between countries, and Excel's regional settings travel with them. A label built with
TEXT(value,"0.00") on an English-locale laptop breaks, or formats wrongly, when the same file recalculates on a machine whose decimal separator is a comma, as in the Netherlands, Germany, the Nordics or Poland. We reproduced #VALUE! in Excel 16.0 with Polish regional settings.
ISO/IEC 17020:2026A corrosion rate of exactly zero: IFERROR(…,999) reports a remaining life of 999 years.
ISO/IEC 17025:2017Ten replicates with one "OVR": AVERAGE() divides by nine, and the mean falls.
ISO 13485:2016A tensile sample recorded as "SLIP" silently drops out of the mean; n = 1 makes σ undefined.
What NormXL does: it generates boundary vectors at every acceptance limit and one step either side, and injects text, blanks, zeros, negatives and out-of-range values into every input. The expected and actual results go into a negative and boundary stress-test log.
