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: Henry on 27 Mar 2010 21:19 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:
From: joeu2004 on 27 Mar 2010 23:01 "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:
From: Dana DeLouis on 28 Mar 2010 16:00 On 3/27/2010 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 It does seem strange... ?2^-46 1.4210854715202E-14 = = = = = Dana DeLouis > 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.
From: Schizoid Man on 29 Mar 2010 01:50 "joeu2004" <joeu2004(a)hotmail.com> wrote in message > "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. There are several ways to reproduce such errors in Excel. E.g. NORMSINV(NORMSDIST(0)). Rather than the zero that one would expect, one gets -1.39213763529183E-16.
From: Henry on 30 Mar 2010 04:22
On 29 Mar, 06:50, "Schizoid Man" <schizoid_...(a)london.com> wrote: > "joeu2004" <joeu2...(a)hotmail.com> wrote in message > > "Henry" <s...(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 thatCOMBIN(9,3) is not > > returning an integer(!). Indeed,COMBIN(9,3) returns exactly > > 83.9999999999999,857891452847979962825775146484375 in IEEE 64-bit > > floating point form. > > There are several ways to reproduce such errors in Excel. E.g. > NORMSINV(NORMSDIST(0)). Rather than the zero that one would expect, one > gets -1.39213763529183E-16. Or even NORMSINV(1/2), But you don't usually expect NORMSINV to return an integer for other values. You do expect COMBIN to do so. |