Bug in Excel's (not VBA's) MOD function

  • Thread starter Thread starter Harlan Grove
  • Start date Start date
Further Correction: The result of =MOD(12.3,1.23) is obtainable without
any extra bits as
=((12.3-8*1.23)-2*1.23)
where the subtraction is arranged to avoid any intermediate binary rounding.

Consequently this example gives no guidance about the basis for the two
unexplained limits in MOD. Specifically that

1. MOD(n,d) returns #NUM! if the quotient n/d >= 134217728 (22^7)
http://support.microsoft.com/kb/119083

2. MOD returns #NUM! regardless of the quotient if the first argument
exceeds 2.68873542664192E14 = 220+219+218+217+215+212+28+26
which is within the range of exact DP representation of whole numbers by
more than an order of magnitude.

Jerry
 
And I cannot reproduce #2 (even after correcting for formatting problems)
=MOD(987654321098765*8+1,543210987654321)
returns the correct answer, despite a 16-digit first argument.

Jerry
 
Jerry W. Lewis wrote...
And I cannot reproduce #2 (even after correcting for formatting problems)
=MOD(987654321098765*8+1,543210987654321)
returns the correct answer, despite a 16-digit first argument.
Click to expand...
....

OK, but the main point should be that Excel doesn't use IEEE 64-bit
modulus. In hardware terms, Excel's MOD is (unfortunately) more than
just a simple wrapper around the Wintel FPU's FPREM1 operation.

Yes, that's right folks, Excel screws up some arithmetic operations,
and while it may or may not have been intentional, it may be inferred
from

http://support.microsoft.com/default.aspx?scid=kb;en-us;119083

that Microsoft has no immediate plans to fix it.

what a company! [In case anyone needs a lesson in why lack of
competition is a BAD THING . . .]
 
Back
Top