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

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.

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

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

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.

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

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

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.

"Jerry W. Lewis" 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?

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.

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.

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

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...b;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 . . .]