MOD with tiny operands gives wrong signs and #NUM!, by a rule that fits every recorded row

FN-MOD-001: MOD with tiny operands gives wrong signs and #NUM!, by a rule that fits every recorded row

Excel differs from the mathematical answer · measured · recorded 2026-11-04

MOD(n,d) takes the exact remainder r of n by d and adds d when their signs differ. Three things change when r is subnormal and its sign differs from d's. The result is #NUM! when |n/d| is 2^26 or more, or when d's low part under a Veltkamp split at 2^27 + 1 is subnormal. Otherwise, when |r| is below 2^-1026, the result is |d| + |r| with d's sign: the remainder is added where it should be taken away. A larger r takes the ordinary step. Any subnormal final result is #NUM!. So MOD(-(1+121*2^-52)*2^-983, 2^-983) returns +(1+121*2^-52)*2^-983, and MOD(2^-983, -(1+120*2^-52)*2^-983) returns #NUM!, where the exact answers are ordinary numbers.

Correct value: MOD(n,d) = n - d*INT(n/d) in exact rational arithmetic; the result is exactly representable.

Correct value: MOD(n,d) = n - d*INT(n/d) in exact rational arithmetic; the result is exactly representable.

Basis: All 152,432 recorded MOD rows match, 4,974 of them rows the ordinary step misses, and so do 1,128 new Excel readings (build 20527). Those readings overturned an earlier test on power-of-two divisors: 1E-296 behaves like a power of two and 1E-300 does not.

Inferred: The rule is what is measured. Reading it as Veltkamp's split, the parts of a Dekker product of the count and the divisor, is an interpretation: a table over exponent and trailing zeros puts the boundary between exponents -1000 and -990 and at about 27 trailing zeros.