Where did I call it an error? Indeed, I showed that this result is
consistent with the binary representations of the numbers involved. 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

Bill Martin -- (Remove NOSPAM from address) wrote:

> 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.0100000000000000000000000000000000000000000000000000011010000001...B3
>> vs
>> 1.010000000000000000000000000000000000000000000000000010B3
>> 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