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

[Jerry W. Lewis]

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.

[Bill Martin -- (Remove NOSPAM from address)]

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

[JE McGimpsey]

It is close, but it's not quite zero, which means that, as in all other
floating point math, one can't blindly write things like:

=IF(MOD(A1,B1)=0,"Good","Bad")

Instead, it's good practice to use something like:

=IF(MOD(A1,B1)<1E-10,"Good","Bad")

where the comparison value is some number "close enough" to zero.

And if B1 can be negative:

=IF(ABS(MOD(A1,B1))<1E-10,"Good","Bad")

is appropriate.

Unfortunately, there are lots of applications out there where the
developer was naive about floating point math, and you can get some
incorrect results from very simple errors.


In article <#uWOWWYqFHA.3544@TK2MSFTNGP15.phx.gbl>,
"Bill Martin -- (Remove NOSPAM from address)"
<wylie@earthNOSPAMlink.net> wrote:

> 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.

[Harlan Grove]

Bill Martin -- (Remove NOSPAM from address) wrote...
....
>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.

That's just standard floating point rounding error. Far more obnoxious
is the call that began this thread,

MOD(12345678000,64)

This returns #NUM! in Excel, but 48 in Gnumeric, OpenOffice Calc, 123
and pretty much every other piece of non-Microsoft software I have that
can calculate modulus. Heck, even the Calculator applet that comes with
Windows returns 48. So at one time there was at least one programmer
somewhere in Microsoft who avoided the temptation to screw this up.

[Bill Martin -- (Remove NOSPAM from address)]

Harlan Grove wrote:
> Bill Martin -- (Remove NOSPAM from address) wrote...
> ...
>
>>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.
>
>
> That's just standard floating point rounding error. Far more obnoxious
> is the call that began this thread,
>
> MOD(12345678000,64)
>
> This returns #NUM! in Excel, but 48 in Gnumeric, OpenOffice Calc, 123
> and pretty much every other piece of non-Microsoft software I have that
> can calculate modulus. Heck, even the Calculator applet that comes with
> Windows returns 48. So at one time there was at least one programmer
> somewhere in Microsoft who avoided the temptation to screw this up.
>

Now that's a more substantial error!

Bill

[Jerry W. Lewis]

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

[Jerry W. Lewis]

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.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.

[Harlan Grove]

"Jerry W. Lewis" <JerryWLewis@discussions.microsoft.com> wrote...
....
>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.
....

Warning - some cynicism to follow.

Microsoft's original commercial language was cassette BASIC. It morphed into
BASICA when Microsoft started selling operating systems. IIRC, BASICA had
only one floating point type, and it wasn't IEEE. A quick Google search
leads me to believe it was 4-byte/32-bit. Excel's MOD function dies at 2^27.

The cynic in me is tempted to leap to the conclusion that Microsoft used
it's BASIC/BASICA code in the original Excel for Mac 512Ks in the mid-1980s
and hasn't revised the code since. Surely that can't be?

End cynicism (and sarcasm).

Excel's MOD is clearly *NOT* IEEE-compliant. Why would they target a
non-IEEE virtual FPU?

[Jerry W. Lewis]

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.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 W. Lewis]

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.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.
>>>>
>