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 |
#31
|
|||
|
|||
Bug in Excel's (not VBA's) MOD function
Harlan Grove wrote:
"Dana DeLouis" wrote... Just to share some thoughts. I may be wrong, but it doesn't look to me that the following two terms can be combined into - 2*GAMMALN(512) . .. You're right. Mental typo on my part. I used LN(COMBIN(1023,511)) in my actual calculations, but miswrote this as GAMMALN(1024)-2*GAMMALN(512). It should have been GAMMALN(1024)-GAMMALN(512)-GAMMALN(513). However it remains the case that NEGBINOMDIST(512,512,0.5) doesn't need to choke. Unfortunately, the program Mathematica shows this to be accurate to only 11 sig. digits. :( FWIW, Mathematica and Maple differ in the results of their respective logarithm of complete gamma functions at the 14th digit, so *prove* that Mathematica is more accurate than Maple. Good luck finding standard mathematical tables for this function beyond 10 significant digits. Probably a rounding issue (Maple is sometimes optimistic about the number of correct figures ... less experience with Mathematica). If I request enough more digits in each package, then I can get arbitrarily precise agreement between them. Jerry |
#32
|
|||
|
|||
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. Jerry Harlan Grove wrote: "Jerry W. Lewis" wrote... I got it [ the limit discussed in http://support.microsoft.com/kb/119083 ] 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. |
#33
|
|||
|
|||
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. Jerry Harlan Grove wrote: "Jerry W. Lewis" wrote... I got it [ the limit discussed in http://support.microsoft.com/kb/119083 ] 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. ----------------------- Perhaps I'm missing something, but 8.88178419700125E-16 looks extremely close to 0.000000000000000 to me. If that's the biggest error one can find in Excel, I'd be content. Bill |
#34
|
|||
|
|||
It is close, but it's not quite zero, which means that, as in all other
floating point math, one can't blindly write things like: =IF(MOD(A1,B1)=0,"Good","Bad") Instead, it's good practice to use something like: =IF(MOD(A1,B1)1E-10,"Good","Bad") where the comparison value is some number "close enough" to zero. And if B1 can be negative: =IF(ABS(MOD(A1,B1))1E-10,"Good","Bad") is appropriate. Unfortunately, there are lots of applications out there where the developer was naive about floating point math, and you can get some incorrect results from very simple errors. In article , "Bill Martin -- (Remove NOSPAM from address)" wrote: Perhaps I'm missing something, but 8.88178419700125E-16 looks extremely close to 0.000000000000000 to me. If that's the biggest error one can find in Excel, I'd be content. |
#35
|
|||
|
|||
Bill Martin -- (Remove NOSPAM from address) wrote...
.... Perhaps I'm missing something, but 8.88178419700125E-16 looks extremely close to 0.000000000000000 to me. If that's the biggest error one can find in Excel, I'd be content. That's just standard floating point rounding error. Far more obnoxious is the call that began this thread, MOD(12345678000,64) This returns #NUM! in Excel, but 48 in Gnumeric, OpenOffice Calc, 123 and pretty much every other piece of non-Microsoft software I have that can calculate modulus. Heck, even the Calculator applet that comes with Windows returns 48. So at one time there was at least one programmer somewhere in Microsoft who avoided the temptation to screw this up. |
#36
|
|||
|
|||
Harlan Grove wrote:
Bill Martin -- (Remove NOSPAM from address) wrote... ... Perhaps I'm missing something, but 8.88178419700125E-16 looks extremely close to 0.000000000000000 to me. If that's the biggest error one can find in Excel, I'd be content. That's just standard floating point rounding error. Far more obnoxious is the call that began this thread, MOD(12345678000,64) This returns #NUM! in Excel, but 48 in Gnumeric, OpenOffice Calc, 123 and pretty much every other piece of non-Microsoft software I have that can calculate modulus. Heck, even the Calculator applet that comes with Windows returns 48. So at one time there was at least one programmer somewhere in Microsoft who avoided the temptation to screw this up. Now that's a more substantial error! Bill |
#37
|
|||
|
|||
Where did I call it an error? Indeed, I showed that this result is
consistent with the binary representations of the numbers involved. 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 Bill Martin -- (Remove NOSPAM from address) wrote: 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. Jerry Harlan Grove wrote: "Jerry W. Lewis" wrote... I got it [ the limit discussed in http://support.microsoft.com/kb/119083 ] 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. ----------------------- Perhaps I'm missing something, but 8.88178419700125E-16 looks extremely close to 0.000000000000000 to me. If that's the biggest error one can find in Excel, I'd be content. Bill |
#38
|
|||
|
|||
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. |
#39
|
|||
|
|||
"Jerry W. Lewis" wrote...
.... 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. .... Warning - some cynicism to follow. Microsoft's original commercial language was cassette BASIC. It morphed into BASICA when Microsoft started selling operating systems. IIRC, BASICA had only one floating point type, and it wasn't IEEE. A quick Google search leads me to believe it was 4-byte/32-bit. Excel's MOD function dies at 2^27. The cynic in me is tempted to leap to the conclusion that Microsoft used it's BASIC/BASICA code in the original Excel for Mac 512Ks in the mid-1980s and hasn't revised the code since. Surely that can't be? End cynicism (and sarcasm). Excel's MOD is clearly *NOT* IEEE-compliant. Why would they target a non-IEEE virtual FPU? |
#40
|
|||
|
|||
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. |
Thread Tools | |
Display Modes | |
|
|