If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Bug in Excel's (not VBA's) MOD function
One way:
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. In article , Harlan wrote: 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? |
#12
|
|||
|
|||
Bug in Excel's (not VBA's) MOD function
Did you get 134217728 from the following Microsoft kb article?
XL: MOD() Function Returns #NUM! Error Value http://support.microsoft.com/default...b;en-us;119083 -- Dana DeLouis Using Windows XP & Office XP = = = = = = = = = = = = = = = = = "Arvi Laanemets" wrote in message ... 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. |
#13
|
|||
|
|||
Bug in Excel's (not VBA's) MOD function
"Dana DeLouis" wrote...
Did you get 134217728 from the following Microsoft kb article? XL: MOD() Function Returns #NUM! Error Value http://support.microsoft.com/default...b;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. -- Never attach files. Snip unnecessary quoted text. Never multipost (though crossposting is usually OK). Don't change subject lines because it corrupts Google newsgroup archives. |
#14
|
|||
|
|||
Bug in Excel's (not VBA's) MOD function
"Harlan Grove" wrote in message ... I did try searching the KB, but I was lazy and human? g |
#15
|
|||
|
|||
Bug in Excel's (not VBA's) MOD function
On Excel 2003 Beta, I got #NUM! with =MOD(12345678000,64)
Bernard "Harlan Grove" wrote in message ... "Dana DeLouis" wrote... Did you get 134217728 from the following Microsoft kb article? XL: MOD() Function Returns #NUM! Error Value http://support.microsoft.com/default...b;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. -- Never attach files. Snip unnecessary quoted text. Never multipost (though crossposting is usually OK). Don't change subject lines because it corrupts Google newsgroup archives. |
#16
|
|||
|
|||
Bug in Excel's (not VBA's) MOD function
"Bernard V Liengme" wrote...
On Excel 2003 Beta, I got #NUM! with =MOD(12345678000,64) ... Seems pretty conclusive that Microsoft considers this a 'feature'. |
#17
|
|||
|
|||
Bug in Excel's (not VBA's) MOD function
"Harlan Grove" wrote in message
... "Bernard V Liengme" wrote... On Excel 2003 Beta, I got #NUM! with =MOD(12345678000,64) .. Seems pretty conclusive that Microsoft considers this a 'feature'. Yep.. I tried it on 2003 commercial version and it returns the same.. -- Regards, Peo Sjoblom |
#18
|
|||
|
|||
Bug in Excel's (not VBA's) MOD function
The free .xls editor from
http://www.byedesign.freeserve.co.uk/ also gets it right. Jerry Harlan Grove wrote: "Harlan Grove" wrote... "Harlan Grove" wrote... ... 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. 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. |
#19
|
|||
|
|||
Bug in Excel's (not VBA's) MOD function
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 Dana DeLouis wrote: Did you get 134217728 from the following Microsoft kb article? XL: MOD() Function Returns #NUM! Error Value http://support.microsoft.com/default...b;en-us;119083 |
#20
|
|||
|
|||
Bug in Excel's (not VBA's) MOD function
"Jerry W. Lewis" wrote...
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. -- Never attach files. Snip unnecessary quoted text. Never multipost (though crossposting is usually OK). Don't change subject lines because it corrupts Google newsgroup archives. |
Thread Tools | |
Display Modes | |
|
|