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 |
#41
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|