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

 

[gavin]

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

Regards
gavin

 

[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