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

  • Thread starter Thread starter Harlan Grove
  • Start date Start date
H

Harlan Grove

The value 12,345,678,000 is too large to store as a long integer but well
within the range of integers that a double precision floating point real can
store. Excel's worksheet MOD function seems to be able to work with reals,
e.g., MOD(2.25,1/3) returns 0.25 and MOD(2.5,1/3) returns 0.166666666666667.
However, there seems to be a glitch in it when it comes to large integer
values divided by small integer values.

For instance, MOD(12345678000,64) returns #NUM! even though the equivalent
(per online help) expression 12345678000-64*INT(12345678000/64) returns 48
as expected. Clearly Excel's worksheet MOD function isn't just argument
checking wrapped around a call to standard C's fmod(3) call. [No, Microsoft
must have decided they needed to 'enhance' it.]

Maybe this is just a glitch on this particular machine. Does anyone else get
#NUM! from the preceding MOD call? I'm running Excel 97 SR-2 and 2000 SP-3
on this particular machine.
 
Harlan Grove said:
For instance, MOD(12345678000,64) returns #NUM! . . .
Click to expand...
....

FWIW, the Works 2000 (ver 5) spreadsheet also returns an error, but
OpenOffice Calc 1.0 returns 48 as expected. While I haven't tested this
under other spreadsheets (yet), I think it's safe to say this, er,
functionality is unique to Microsoft.
 
Hi

Win98: Excel2000 - the same result. It looks like the error is returned
whenever quotient reaches the value 134217728


Arvi Laanemets
 
Excel 2000 (with latest patches) returns #NUM
Lotus 1-2-3 97 returns 48
Lotus 1-2-3 Release 5 returns 48

Regards
gavin
 
I agree with your analysis. 134217728 is 2^27, but I have no clue why
2^27 as the integer part of the division (regardless of divisor) would
be a problem.

Jerry

Arvi said:
Hi

Win98: Excel2000 - the same result. It looks like the error is returned
whenever quotient reaches the value 134217728


Arvi Laanemets


The value 12,345,678,000 is too large to store as a long integer but well
within the range of integers that a double precision floating point real
can

store. Excel's worksheet MOD function seems to be able to work with reals,
e.g., MOD(2.25,1/3) returns 0.25 and MOD(2.5,1/3) returns
0.166666666666667.

However, there seems to be a glitch in it when it comes to large integer
values divided by small integer values.

For instance, MOD(12345678000,64) returns #NUM! even though the equivalent
(per online help) expression 12345678000-64*INT(12345678000/64) returns 48
as expected. Clearly Excel's worksheet MOD function isn't just argument
checking wrapped around a call to standard C's fmod(3) call. [No,
Microsoft

must have decided they needed to 'enhance' it.]

Maybe this is just a glitch on this particular machine. Does anyone else
get

#NUM! from the preceding MOD call? I'm running Excel 97 SR-2 and 2000 SP-3
on this particular machine.
Click to expand...
Click to expand...
 
Hi


Jerry W. Lewis said:
I agree with your analysis. 134217728 is 2^27, but I have no clue why
2^27 as the integer part of the division (regardless of divisor) would
be a problem.
Click to expand...


The only explanation I can think of - by calculation is the quotient as
intermediate value temporarily stored into some variable with upper limit as
2^27, and it's overflow causes error.


Arvi Laanemets

Jerry

Arvi said:
Hi

Win98: Excel2000 - the same result. It looks like the error is returned
whenever quotient reaches the value 134217728


Arvi Laanemets


The value 12,345,678,000 is too large to store as a long integer but well
within the range of integers that a double precision floating point real
can

store. Excel's worksheet MOD function seems to be able to work with reals,
e.g., MOD(2.25,1/3) returns 0.25 and MOD(2.5,1/3) returns
0.166666666666667.

However, there seems to be a glitch in it when it comes to large integer
values divided by small integer values.

For instance, MOD(12345678000,64) returns #NUM! even though the equivalent
(per online help) expression 12345678000-64*INT(12345678000/64) returns 48
as expected. Clearly Excel's worksheet MOD function isn't just argument
checking wrapped around a call to standard C's fmod(3) call. [No,
Microsoft

must have decided they needed to 'enhance' it.]

Maybe this is just a glitch on this particular machine. Does anyone else
get

#NUM! from the preceding MOD call? I'm running Excel 97 SR-2 and 2000 SP-3
on this particular machine.
Click to expand...
Click to expand...
Click to expand...
 
...

FWIW, the Works 2000 (ver 5) spreadsheet also returns an error, but
OpenOffice Calc 1.0 returns 48 as expected. While I haven't tested this
under other spreadsheets (yet), I think it's safe to say this, er,
functionality is unique to Microsoft.
Click to expand...

And FWIW, Lotus 123 releases 5 and 9.7, Quattro Pro 10 and OpenOffice Calc 1.1
all give the correct/expected result of 48. I'm not going to bother testing
various windows shareware/freeware or Linux spreadsheets.
 
Did you get 134217728 from the following Microsoft kb article?

XL: MOD() Function Returns #NUM! Error Value
http://support.microsoft.com/default.aspx?scid=kb;en-us;119083

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


Arvi Laanemets said:
Hi

Win98: Excel2000 - the same result. It looks like the error is returned
whenever quotient reaches the value 134217728


Arvi Laanemets


Harlan Grove said:
The value 12,345,678,000 is too large to store as a long integer but well
within the range of integers that a double precision floating point real can
store. Excel's worksheet MOD function seems to be able to work with reals,
e.g., MOD(2.25,1/3) returns 0.25 and MOD(2.5,1/3) returns 0.166666666666667.
However, there seems to be a glitch in it when it comes to large integer
values divided by small integer values.

For instance, MOD(12345678000,64) returns #NUM! even though the equivalent
(per online help) expression 12345678000-64*INT(12345678000/64) returns 48
as expected. Clearly Excel's worksheet MOD function isn't just argument
checking wrapped around a call to standard C's fmod(3) call. [No, Microsoft
must have decided they needed to 'enhance' it.]

Maybe this is just a glitch on this particular machine. Does anyone else get
#NUM! from the preceding MOD call? I'm running Excel 97 SR-2 and 2000 SP-3
on this particular machine.
Click to expand...
Click to expand...
 
Did you get 134217728 from the following Microsoft kb article?

XL: MOD() Function Returns #NUM! Error Value
http://support.microsoft.com/default.aspx?scid=kb;en-us;119083
Click to expand...
...

I did try searching the KB, but I was lazy and just fed Excel and MOD to basic
search rather than using advanced search restricted to Excel-related articles.

Interesting they don't call this a bug. So I guess it's a feature. Also
interesting that Microsoft seems to believe they needed to improve upon either
the FPREM1 FPU (yes, I'm being very Wintel-centric) instruction's or standard C
fmod(3) call's results. Of course the code for MOD may never have been touched
since the original Mac version was released, and that would have preceded both
wide-spread hardware floating point support and C language standardization, and
it could possibly explain the odd 2^27 value. Was there something magic about
three nine-bit words or maybe 28-bit signed values on really old Macs?

It'd be interesting to find out whether Excel 5/95 and 2003 also work like this.
 
Harlan Grove said:
...
..

Seems pretty conclusive that Microsoft considers this a 'feature'.
Click to expand...


Yep..
I tried it on 2003 commercial version and it returns the same..
 
I got it 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.

Jerry
 
Back
Top