"Dana DeLouis" wrote:
It does seem strange...
?2^-46
1.4210854715202E-14

What's your point?

Yes, COMBIN(9,3) is 84 - 2^-46 (according to Excel). 2^-46 represents the
least-significant bit of the 64-bit floating-point representation of 84. So
COMBIN(9,3) differs from what we expect by only 1 bit, the LSB.

What's "strange" about that?

If COMBIN(n,k) were implemented as FACT(n)/FACT(n-k)/FACT(k), then
COMBIN(23,2) would be 253 + 2^-45, where 2^-45 represents the LSB.

So, yes, the non-integer result can be off by just 1 bit, the LSB, due to
the floating-point algorithm.

Of course, that shouldn't be the case for COMBIN(9,3), as I explained
previously.

And ironically, Excel's COMBIN(23,2) returns the exact integer 253. Now
__that__ I find "strange", given the result of COMBIN(9,3).


----- original message -----

"Dana DeLouis" wrote in message
...
On 3/27/2010 11:01 PM, joeu2004 wrote:
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 -----

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.