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.

Jerry

Harlan Grove wrote:

"Jerry W. Lewis" wrote...

I got it


[ the limit discussed in http://support.microsoft.com/kb/119083 ]

by experimentation, and then saw that Arvi had also determined the
same limit. I find it interesting that MS makes no attempt to
explain such an unusual limit.

An additional unusual limit that applies, is that MOD returns #NUM!
regardless of the quotient if the first argument exceeds
2.68873542664192E14 = 2^20+2^19+2^18+2^17+2^15+2^12+2^8+2^6
which is within the range of exact DP representation of whole numbers
by more than an order of magnitude.


It's mysteries like this that make Excel so much more (and so much
less) than
just a dry (reliable) mathematical tool.


-----------------------

Perhaps I'm missing something, but 8.88178419700125E-16 looks extremely close to
0.000000000000000 to me.

If that's the biggest error one can find in Excel, I'd be content.

Bill