A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

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



 
 
Thread Tools Display Modes
  #41  
Old August 30th, 2005, 11:02 AM
Jerry W. Lewis
external usenet poster
 
Posts: n/a
Default

And I cannot reproduce #2 (even after correcting for formatting problems)
=MOD(987654321098765*8+1,543210987654321)
returns the correct answer, despite a 16-digit first argument.

Jerry

Jerry W. Lewis wrote:

Further Correction: The result of =MOD(12.3,1.23) is obtainable without
any extra bits as
=((12.3-8*1.23)-2*1.23)
where the subtraction is arranged to avoid any intermediate binary
rounding.

Consequently this example gives no guidance about the basis for the two
unexplained limits in MOD. Specifically that

1. MOD(n,d) returns #NUM! if the quotient n/d = 134217728 (22^7)
http://support.microsoft.com/kb/119083

2. MOD returns #NUM! regardless of the quotient if the first argument
exceeds 2.68873542664192E14 = 220+219+218+217+215+212+28+26
which is within the range of exact DP representation of whole numbers by
more than an order of magnitude.

Jerry

Jerry W. Lewis wrote:

Correction: MOD uses at least 1-bit more than IEEE double precision.
There is no upper limit on the precision imposed by this example,
because MOD(B(12.3),B(1.23)) = (2/x)*B(1.23) not simply 2/x. In the
Wintel world, the most obvious source for more than double precision
is the 10-byte internal registers in the processor. Does anyone know
if extended precision is available in hardware on the Mac (since Excel
on the Mac gives the same answer)?

WAG alert:
I am not aware of commercial MS languages offering access to the
processor's extended precision (at least not in recent memory), so it
is possible that this cross-platform consistency is due to some
non-standard software extended precision. If so, then this extra
precision on the mantissa and the unexplained limits for MOD may all
be related to fitting this hypothetical custom FP precision into a
convenient word size. It would be interesting to see other examples
that further define the size of the mantissa that MOD must be using.

Jerry

"Jerry W. Lewis" wrote:


... The "mystery" is that getting this particular result requires
more than IEEE double preicison (which is presumably the basis of all
Excel calculations) but less than the 10-byte floating point
precision available internally in the processor.

Jerry


Jerry W. Lewis wrote:


Here is another one for the mystery books.
http://www.bygsoftware.com/issues/modbug.html
completely misses the point about what is happening, but does give
an interesting example.

=MOD(12.3,1.23)
returns 8.88178419700125E-16 (both Windows and Mac), which is an
extremly curious result, even considering binary approximations to
the inputs. If B(x) is the (IEEE double precision) binary
approximation to x, then
B(12.3)/B(1.23) = 10 + 2/x
where x=2769713770832855. B(10+2/x)=10, so I would have expected
MOD to return 0, instead of what it does return. The sign of
MOD(12.3,1.23) and 2/x are the same, which is promising, but 2/x =
7.22096276178964E-16 which is smaller than what MOD returned. Now
10+2/x in binary is
1.010000000000000000000000000000000000000000000000 0000011010000001...B3

vs
1.010000000000000000000000000000000000000000000000 000010B3
as the binary representation to 10+8.88178419700125E-16 = 10+2^-50.
Since all previous MOD results (that I have seen questioned) were
consistent with binary math, my best guess is that the worksheet
MOD is doing custom arithmetic that evaluates the quotient to 55
bits (vs. 53 bits for IEEE double precision).

Unfortunately that still does not lead me to a guess about the
basis for the two unexplained limits discussed in this (ancient)
thread.



  #42  
Old August 30th, 2005, 05:13 PM
Harlan Grove
external usenet poster
 
Posts: n/a
Default

Jerry W. Lewis wrote...
And I cannot reproduce #2 (even after correcting for formatting problems)
=MOD(987654321098765*8+1,543210987654321)
returns the correct answer, despite a 16-digit first argument.

....

OK, but the main point should be that Excel doesn't use IEEE 64-bit
modulus. In hardware terms, Excel's MOD is (unfortunately) more than
just a simple wrapper around the Wintel FPU's FPREM1 operation.

Yes, that's right folks, Excel screws up some arithmetic operations,
and while it may or may not have been intentional, it may be inferred
from

http://support.microsoft.com/default...b;en-us;119083

that Microsoft has no immediate plans to fix it.

what a company! [In case anyone needs a lesson in why lack of
competition is a BAD THING . . .]

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:20 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.