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:
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 -----

wrote in message
...
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: