Excel's COMBIN and integers
in [Functions]
|
Prev: Worksheets are stuck on default text format with left align
Next: HOW CAN I USE "MODE" ON WORDS (NAMES OF PEOPLE)?
From: Dana DeLouis on 2 Apr 2010 14:33 >> But "=COMBIN(9,3)-84-0" gives -1.42109E-14 Hi. Just to mention. This behavior appears in some math programs as well. Therefore, it would appear now that Excel is not really that far off. At common low values, Excel and others agree (ie 9 & 3) Binomial[9., 3] - 84 -1.42109*10^-14 So, I guess Excel is ok. What I thought was strange occurs at higher values. At some combinations, Excel was more exact, and math programs were off. At other numbers, Math programs were more exact, and Excel was off. Here is one that was flagged near the upper limit of machine precision. =Combin(53, 21) - 0 - 317986441828055 =Combin(53, 21) - 317986441828055 - 0 Returns: 0 0.125 Math... Binomial[53., 21] - 317986441828055 2.625 Hmmm. Excel was off by 1/8, and the Math program was off by 2+5/8. Just for geewiz, I believe their documentation applies to Arbitrary-precision... (and not to machine-precision above) "...Binomial and related functions use a divide-and-conquer algorithm to balance the number of digits in subproducts." = = = = = Interesting... Dana DeLouis On 3/27/10 11:01 PM, joeu2004 wrote: > "Henry"<se16(a)btinternet.com> wrote: >> Similarly "=COMBIN(9,3)-84" gives 0. >> But "=COMBIN(9,3)-84-0" gives -1.42109E-14 > > Interesting find! What that tells us is that COMBIN(9,3) is not > returning an integer(!). Indeed, COMBIN(9,3) returns exactly > 83.9999999999999,857891452847979962825775146484375 in IEEE 64-bit > floating point form. > > Off-hand, I cannot think of any reason for the computational > inaccuracy in __this__ case. Even if COMBIN computes this the hard > way (which it shouldn't), FACT(9)/FACT(6)/6 and FACT(9)/6/FACT(6) > yield exactly 84, as they should since 362880/720 (9!/6!) and 362880/6 > (9!/3!) are both integers, and all factors are well within the > computational limitations of 64-bit floating point arithmetic. > > I can only guess that COMBIN uses some approximation formula, which > might be more accurate for larger factors that exceed the > computational limitations. I am not aware of any such approximation > formula. > > FYI, given the fact that COMBIN(9,3) does not return an exact integer, > the reason why COMBIN(9,3)-84 is different from COMBIN(9,3)-84-0 is > because of the half-baked heuristic described (poorly) in KB 78113. > See the section "Example When the Value Reaches Zero" at > support.microsoft.com/kb/78113. > > >> =IF(COMBIN(9,3)=84,"same","different") and >> =IF(COMBIN(9,3)-84=0,"same","different") >> do not give identical > > That is a common side-effect of the heuristic, which is why I call it > half-baked. > > The work-around, as you might realize, is to compute ROUND(COMBIN(9,3), > 0). > > But like you, I would never have expected that is necessary for such > small numbers. > > > ----- original message ----- > > "Henry"<se16(a)btinternet.com> wrote in message > news:f3a39c54-269c-486e-9ad9-2c2e80e991cc(a)t23g2000yqt.googlegroups.com... >> In theory COMBIN works with integers in Excel (2003 and 2007). >> >> So "=COMBIN(9,3)" gives 84 and so does "=COMBIN(9.7,3.6)". >> Similarly "=COMBIN(9,3)-84" gives 0. >> >> But "=COMBIN(9,3)-84-0" gives -1.42109E-14 >> and there are many similar examples suggesting that some sort of >> rounding is involved. >> The sign can even change so for example "=COMBIN(15,3)-455-0" gives >> 5.68434E-14 >> >> =IF(COMBIN(9,3)=84,"same","different") and >> =IF(COMBIN(9,3)-84=0,"same","different") do not give identical >> results. >> Nor do =COMBIN(9,3)-67-17 and =COMBIN(9,3)-68-16. >> >> >> >> >> It gets stranger: |