Skip to content

Excel's arithmetic rounding and its snap to zero

Status: design, measured 2026-11-13. Item ltg-xl-calc-excel-arithmetic-rounding-design-20261112.

Decision

Rule Excel for Mac 16.113 Excel for Windows 16.0 wolfxl-calc today Cost of emulating it Recommendation
1. Each + - * / is rounded to a 64-bit significand, then to a double absent: IEEE double on 3,080 of 3,080 cases, 907 of which rule 1 would change present: 240 of 240 constructed rows (Wave 1, C4) IEEE double 4.6x to 6.2x per isolated operation; within 0.6% on a full calculation of the calc benchmark; +12% (0.2 ms) on one recalculation that re-sums 100,000 cells implement behind a mode, off by default
2. A cancelling root +/-, and SUM's last step, return 0 below 2^(E-49) present: 7,616 of 7,616 cases, 2,668 of which it changes present (Wave 1, C5); its operand basis was not separated on Windows never at the root; SUM snaps every step against a different threshold 1.13x per isolated subtraction; within 0.7% on the calc benchmark implement, on in every mode

Filed: ltg-xl-calc-excel-arithmetic-rules-20261113 implements both rules as recommended. ltg-xl-calc-excel-arithmetic-windows-probes-20261113 runs the three probe workbooks below on Excel for Windows. That run settles the two Windows facts this design takes from Wave 1 rather than from these probes: which operand sets the snap threshold, and whether rule 1 holds for SUM over more than two terms and for the parenthesised form.

The two rules

Rule 1 (Excel for Windows only). Every +, -, * and /, and every step of SUM and PRODUCT, is computed as the x87 unit does with 64-bit precision: the exact result is rounded to a 64-bit significand, and that is rounded to a double, RN53(RN64(x)). It differs from IEEE RN53(x) only when the IEEE result has an odd significand and the exact error lies within 2^-12 ulp of half an ulp, where RN64 lands on the midpoint and the tie then goes to the even neighbour. Excel for Mac does not do this: it returned the IEEE double in every probe case, including all 907 where the two models disagree.

Rule 2 (both platforms). Let the formula's root operation be a binary + or - with left operand a and IEEE result r. Let E be the binary exponent of a (2^E <= |a| < 2^(E+1)). If 0 < |r| < 2^(E-49), that is, below 8 ulps of a's binade, the cell's value is 0. The same test applies to the last accumulation step of SUM, with the running total before the last term as a. Earlier SUM steps never snap. Precisely:

  • The basis is the left operand. It is not the larger operand, the right operand, the smaller one, or a ratio to the larger operand: each of those misses 48 of 96 vectors on at least one output of the order probe.
  • Only the root counts. =A-B and =A-B*1 snap, because the root is still the subtraction. =1*(A-B), =A-B+0, =IF(TRUE,A-B) and =SUM(A-B) do not. In =SUM(A-B) the single SUM step has a 0 running total, so there is no basis.
  • A root wrapped whole in parentheses does not snap: =(A-B), =((A-B)), =+(A-B). Parentheses around an operand change nothing: =(A)-B, =A-(B), =(A)-(B) and =-(B)+A snap exactly as =A-B and =-B+A do. =+A-B snaps, because unary plus binds to A and the root is the subtraction.
  • SUM snaps wherever it appears: =(SUM(..)) and =1*SUM(..) snap like =SUM(..). SUM's rule lives in the function, not at the formula root.
  • Under rule 1 (Windows), r is the rule 1 result. The threshold is unchanged, because a difference that small is exact in both models: the operands are within a factor of 2 of each other.

Wave 1 (engines/wolfxl/crates/wolfxl-compile/docs/excel-divergence-20261112.md, C5) stated the basis as "the larger operand". Its snap rows pair operands a few ulps apart, which share a binary exponent unless they straddle a power of two, so those rows cannot separate the two readings. The Mac order probe separates them, and the left operand is the basis there.

Evidence

All values are native Excel for Mac 16.113 (build 26091433) on macOS 26.6.2, arm64, from wolfsuite-native-check --app excel --mode vectors --recalc full (manual calculation, full recalculation after every vector), run 2026-11-13. Every number is compared bit for bit with exact rational models in engines/wolfxl/scripts/excel_arithmetic_probes.py check.

Probe Vectors x outputs Recorded cases Stated rules (Mac) match Rule 1 would change Rule 2 changes Engine today wrong
rounding 280 x 11 3,080 3,080 907 0 0
snap 256 x 20 5,120 5,120 0 1,294 1,109
order 96 x 26 2,496 2,496 0 1,374 1,138

"Rule 1 would change" counts cases where the Windows model and IEEE disagree. Excel for Mac returned IEEE in all of them. "Rule 2 changes" counts cases where the stated rules and a no-snap model disagree.

Rule 1 cases (probe rounding). The 280 vectors are constructed so that RN64 lands on a double midpoint: near-half-ulp addends for +/-, near-tie operands found by search for *//, and pairs of half-ulp addends for three-term sums. The probe covers 11 outputs:

  • the bare operators =A+B, =A-B, =A*B, =A/B and the chain =A+B+C
  • the parenthesised =(A+B) (280 cases, 101 distinguishing)
  • =A*B+C, =PRODUCT(A,B) and =SUM(A,B)
  • SUM over three terms, =SUM(A,B,C) and =SUM(A2:A4): 560 cases, 202 distinguishing

The Windows side of rule 1 is Wave 1's C4: 240 of 240 constructed rows on Excel for Windows 16.0 build 20326, with SUM and PRODUCT reported to behave the same way. Separately, ltg-xl-calc-excel-integer-power-fv-20261112 captured 1,704 Windows rows of ^, POWER and FV, which it models with every product rounded this way. Neither Windows source records SUM over more than two terms or the parenthesised form; the filed Windows probe item does.

Rule 2 cases (probes snap and order).

  • snap pairs each of 13 bases with 1 to 16 ulps of difference, and each of 3 powers of two with 1 to 16 half-ulps below. The third term is 0, ±3 ulp, +9 ulp, the cancelling difference, or a tiny value. For all 13 bases, =A-B snaps at 1 to 7 ulps and keeps 8 to 16.
  • order builds p (a power of two) and q = p - k half-ulps for 6 powers, so the two operands of each cancellation have different exponents. This separates the left, right, larger and smaller operand as basis.
  • SUM over more than two terms: 1,344 cases. These are =SUM(A,B,C), =SUM(C,A,B) and =SUM(A2:A4) in snap (768), and three-term and four-term orderings in order (576). They show that only the last step snaps: a model that snaps every step is wrong on 136 of 256 =SUM(A2:A4) cases.
  • Parenthesised forms: 1,856 cases.
  • In snap, 1,280 cases: =(A-B), =((A-B)), =+(A-B) and =1*(A-B) never snap, and =(SUM(A,B)) snaps like =SUM(A,B).
  • In order, 576 cases: =(q-p) never snaps; =(p)-q-style operand parentheses and =(SUM(..)) snap like their bare forms.

The check also verifies the error-free transforms that the prototype uses for rule 1 against the exact RN53(RN64(x)) model. That covers 14,366 probe operations and 200,000 seeded random pairs (13,903 of which differ from IEEE), with 0 mismatches.

Cost

The prototype (evidence/…prototype.patch) adds a mode to wolfxl-calc with three settings: ieee (today), mac (rule 2) and windows (rules 1 and 2). It was applied for the measurements below and then removed; nothing in this item changes the engine. On all 10,696 recorded cases, its engine results match the Mac rules in mac mode, the Windows rules in windows mode, and today's engine model in ieee mode.

Runs used one release build on a shared Verda burst gate (linux-x86_64) through scripts/ltg_remote_cargo.py. The full receipt is in evidence/…cost.json.

Operation (2^20 pairs, 15 rounds, median) ns per op vs IEEE
a+b IEEE 1.77 1.00x
add rule 1 8.16 4.60x
mul rule 1 9.20 5.15x
div rule 1 11.09 6.19x
a-b with rule 2 snap 2.02 1.13x
sub rule 1 + snap 8.69 4.85x
calc_throughput workload, 100,000 rows, 9 interleaved rounds, median ieee mac windows
w1 fill-down, full calculation 0.1844 s +0.69% +0.59%
w1, one-input recalculation 1.645 ms -1.03% +12.34%
w2 running balance, full calculation 0.0920 s +0.19% -0.06%
w2, one-input recalculation 98.39 ms -0.05% +0.22%

Formula evaluation costs about 0.9 µs per cell, so 6 to 9 ns more per operation does not show on a full calculation. The visible cost is w1's recalculation: it re-sums 100,000 cells, and under rule 1 every SUM step runs the transform. Rule 2 costs one exponent extraction and one comparison, at the root and once per SUM.

⚠️ No baseline build without the prototype was timed; the ieee arm stands in for today's engine and carries the mode checks. Neither workload produces a value that the rules change, so the bench prices the checks and the transforms, not changed results.

Recommendation

  1. Rule 2: implement, on in every mode. Every Excel measured applies it. The engine misses 2,247 of 7,616 recorded rule 2 cases today, and it costs nothing measurable. It replaces add_with_cancellation in evaluator/aggregates.rs, whose every-step snap below 2^-50 of the larger term is wrong both ways: it snaps intermediate steps that Excel keeps, and it misses root snaps.
  2. Rule 1: implement behind a mode, default off. Excel for Mac, the native Excel this suite checks against, does not apply it. Excel for Windows does. Make the mode a per-evaluator setting chosen by the caller, not the prototype's process-global switch, so that one process can check against both platforms. The default stays IEEE, which equals Excel for Mac.

What the parser must keep

Rule 2 is decidable only if evaluation can tell which binary operation is the formula's root and whether the whole formula body was parenthesised. Today the primary parser's (TokenKind::Paren, TokenSubKind::Open) arm in parser.rs returns the inner expression and drops the parentheses, so =A-B and =(A-B) parse to the same Expr.

Keep:

  1. Top-level grouping. Record whether the formula body is a single parenthesised group: its first token opens a parenthesis whose matching close is the last token, at any nesting depth (=((A-B))). Store it as a parse-time fact on the parsed formula (for example root_grouped: bool on ParsedFormula, or an Expr::Group node at the root only). It must not depend on the arithmetic mode. Parsed formulas are stored in the dependency graph and reused across calculations and shared templates, and the prototype's rewrite of a grouped root into unary + read the mode at parse time, which is wrong once the mode is per evaluator.
  2. Unary plus at the root. =+(A-B) must stay Unary('+') over the subtraction so it does not snap, while =+A-B stays Binary(Sub) with +A on the left. The parser keeps both today; any simplification that drops a unary + would break this.
  3. Operand order. The root Binary { left, right } must keep source order, because the left operand is the basis. Rewrites that commute operands (constant folding, canonicalisation) must not run on a root +/-.

Not needed:

  • Parentheses inside operands: they never change the result.
  • Anything for SUM: its last-step rule needs only its own argument order, which visit_class_arguments already follows.
  • The two-leaf fast path direct_scalar_expression in parser.rs: it accepts no parentheses.

The root is the cell formula's root, evaluated in eval_formula_expression (evaluator/scalar.rs) and in the packed scalar path in evaluator/expression.rs. These cases were not probed and should keep today's behaviour until they are:

  • array-valued roots (=A1:A3-B1:B3)
  • defined names whose formula is a cancelling subtraction
  • conditional-formatting and data-validation formulas

Reproduce

uv run --with openpyxl python engines/wolfxl/scripts/excel_arithmetic_probes.py build --prefix evidence/ltg-xl-calc-excel-arithmetic-rounding-design-20261112
uv run wolfsuite-native-check --app excel --mode vectors --interface P.interface.json --vectors P.vectors.jsonl --recalc full --out DIR P.xlsx   # per probe
uv run --extra dev python engines/wolfxl/scripts/excel_arithmetic_probes.py check --prefix evidence/ltg-xl-calc-excel-arithmetic-rounding-design-20261112

Engine results: apply evidence/…prototype.patch, then run python3 scripts/ltg_remote_cargo.py --host auto --crate engines/wolfxl/crates/wolfxl-calc -- run --release --example excel_arith_cost -- probe | ops | bench 100000 9.

Evidence files (prefix evidence/ltg-xl-calc-excel-arithmetic-rounding-design-20261112):

  • .json: item evidence
  • .check.json: checker report
  • .cost.json: cost receipt
  • .prototype.patch: the prototype
  • per probe P in rounding, snap, order: .P.xlsx, .P.interface.json, .P.vectors.jsonl, .P.excel-mac.results.jsonl, .P.excel-mac.results.meta.json, .P.receipt.json and .P.engine.results.jsonl.gz