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

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

 

[J.E. McGimpsey]

I get #NUM! in XL01 and XLv.X.

 

[Harlan Grove]

I get #NUM! in XL01 and XLv.X.

So will Microsoft consider this a bug or a feature?

 

[Harlan Grove]

For instance, MOD(12345678000,64) returns #NUM! . . .

....

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.

 

[Arvi Laanemets]

Hi

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


Arvi Laanemets

 

[Jerry W. Lewis]

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

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.

 

[J.E. McGimpsey]

So will Microsoft consider this a bug or a feature?

Given their big "mea culpa" on statistical functions,

http://support.microsoft.com/?kbid=828888

I'm hopeful.

 

[Arvi Laanemets]

Hi

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.

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

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.

 

[Harlan Grove]

Given their big "mea culpa" on statistical functions,

http://support.microsoft.com/?kbid=828888

I'm hopeful.

OK, so how does one submit a bug report for Excel to Microsoft without having to
call their support number and pay for the priviledge of reporting it?

 

[Harlan Grove]

...

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.

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.

 

[J.E. McGimpsey]

One way:

Email (e-mail address removed)

make sure Excel is in the subject line so that it can be routed to
the appropriate product managers.

Mac users have a Feedback item in Office v.X's Help menus that take
them to

http://www.microsoft.com/mac/feedback/suggestion.asp

Don't remember if there's a dedicated site for WinOffice.

 

[Dana DeLouis]

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

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.

 

[Harlan Grove]

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

...

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.

 

[Sandy Mann]

I did try searching the KB, but I was lazy

and human? <g>

 

[Bernard V Liengme]

On Excel 2003 Beta, I got #NUM! with =MOD(12345678000,64)
Bernard

 

[Harlan Grove]

On Excel 2003 Beta, I got #NUM! with =MOD(12345678000,64)

...

Seems pretty conclusive that Microsoft considers this a 'feature'.

 

[Peo Sjoblom]

...
..

Seems pretty conclusive that Microsoft considers this a 'feature'.

Yep..
I tried it on 2003 commercial version and it returns the same..

 

[Jerry W. Lewis]

The free .xls editor from
http://www.byedesign.freeserve.co.uk/
also gets it right.

Jerry

 

[Jerry W. Lewis]

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

 

[Harlan Grove]

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.

It's mysteries like this that make Excel so much more (and so much less) than
just a dry (reliable) mathematical tool.