Bug in Excel's (not VBA's) MOD function
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 Laanemets wrote:
Hi
Win98: Excel2000 - the same result. It looks like the error is returned
whenever quotient reaches the value 134217728
Arvi Laanemets
"Harlan Grove" wrote in message
...
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.
|