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:

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

Jerry W. Lewis wrote:

Correction: MOD uses at least 1-bit more than IEEE double precision.
There is no upper limit on the precision imposed by this example,
because MOD(B(12.3),B(1.23)) = (2/x)*B(1.23) not simply 2/x. In the
Wintel world, the most obvious source for more than double precision
is the 10-byte internal registers in the processor. Does anyone know
if extended precision is available in hardware on the Mac (since Excel
on the Mac gives the same answer)?

WAG alert:
I am not aware of commercial MS languages offering access to the
processor's extended precision (at least not in recent memory), so it
is possible that this cross-platform consistency is due to some
non-standard software extended precision. If so, then this extra
precision on the mantissa and the unexplained limits for MOD may all
be related to fitting this hypothetical custom FP precision into a
convenient word size. It would be interesting to see other examples
that further define the size of the mantissa that MOD must be using.

Jerry

"Jerry W. Lewis" wrote:


... The "mystery" is that getting this particular result requires
more than IEEE double preicison (which is presumably the basis of all
Excel calculations) but less than the 10-byte floating point
precision available internally in the processor.

Jerry


Jerry W. Lewis wrote:


Here is another one for the mystery books.
http://www.bygsoftware.com/issues/modbug.html
completely misses the point about what is happening, but does give
an interesting example.

=MOD(12.3,1.23)
returns 8.88178419700125E-16 (both Windows and Mac), which is an
extremly curious result, even considering binary approximations to
the inputs. If B(x) is the (IEEE double precision) binary
approximation to x, then
B(12.3)/B(1.23) = 10 + 2/x
where x=2769713770832855. B(10+2/x)=10, so I would have expected
MOD to return 0, instead of what it does return. The sign of
MOD(12.3,1.23) and 2/x are the same, which is promising, but 2/x =
7.22096276178964E-16 which is smaller than what MOD returned. Now
10+2/x in binary is
1.010000000000000000000000000000000000000000000000 0000011010000001...B3

vs
1.010000000000000000000000000000000000000000000000 000010B3
as the binary representation to 10+8.88178419700125E-16 = 10+2^-50.
Since all previous MOD results (that I have seen questioned) were
consistent with binary math, my best guess is that the worksheet
MOD is doing custom arithmetic that evaluates the quotient to 55
bits (vs. 53 bits for IEEE double precision).

Unfortunately that still does not lead me to a guess about the
basis for the two unexplained limits discussed in this (ancient)
thread.