"Jerry W. Lewis" wrote:
An obvious calculation for Combin(n,r) is
EXP( GAMMALN(n+1)-GAMMALN(r+1)-GAMMALN(n-r+1) )

"Obviously"! ;-)

Yeah, I had come across this formula myself. Well, the equivalent:
Gamma(n+1)/Gamma(r+1)/Gamma(n-r+1). (But Excel 2003 does not have a GAMMA
function per se, AFAIK.)

But I did not think that was the explanation since the Excel implementation
results in an error of about 1E-8, very much larger than the 2^-46 error
that COMBIN(9,3) produces, a one-bit error in the least-significant bit.

In fact, for COMBIN(n,k) with n=1,...,53 and k=1,...,n, 60% of the results
are integers, and the error is not more than the 3 least-significant bits,
and usually only in the least-significant bit [1]. (However, I did not
determine the accuracy of the integral results.)

I would be surprised to see that kind of accuracy from a Gamma
approximation, much less Exp and GammaLn approximations.

However, I admit that I am not familiar with implementations of these
approximations. And perhaps we can expect any approximation errors to
cancel out [2]. Moreover, Jerry says that GAMMALN "only gives about
10-figure accuracy". That would certainly contribute to, if not explain,
the large error in an Excel implemenation of the formula.

I would be interested in seeing the exact results for either the GammaLn or
Gamma formula from an independent math program. By "exact" results, I mean
either the 64-bit binary representation or some equivalent decimal
presentation of it. For example, Excel COMBIN(9,3) results in 84-2^-46, or
&h4054FFFF,FFFFFFFF, or 83.9999999999999,857891452847979962825775146484375 .
The latter two are my own stylistic presentation.


-----
Endnotes

[1] For Excel COMBIN(n,k) with n=1,...,53 and k=1,...,n, 853 results are
integers, 333 are off in the least-significant bit, 222 are off in the 2
least-significant bits, and 23 are off in the 3 least-significant bits.
Note that "off" means off from the rounded integer. I did not determine the
accuracy of the integers.

[2] For example, if COMBIN(53,21) were evaluated effectively by Prod(k,
k=33,...,53)/Fact(32), an Excel implementation (i.e. using 64-bit
intermediate subproducts) results in exactly the correct integer, despite
the fact that Prod(k) results in an incorrect integer. Fact(32) does result
in exactly the correct integer.


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

"Jerry W. Lewis" wrote in message
...
This is intended as an addendum to
http://groups.google.com/group/micro...affa04b5577be3
which I cannot reply to directly because the MS community interface
appears
to no longer support replying to the microsoft.public.excel group, my ISP
no
longer supports NNTP newsgroups at all, and Google does not support posts
without displaying my real e-mail address.

An obvious calculation for Combin(n,r) is
EXP( GAMMALN(n+1)-GAMMALN(r+1)-GAMMALN(n-r+1) )

For large n, accuracy can be reduced due to cancellation problems. Ian
Smith discussed how to avoid these cancellation problems through a simple
auxilary function; unfortunately, AOL stopped hosting his web page.

In Excel, accuracy is also lost because the Excel implementation of
GAMMALN
only gives about 10-figure accuracy, which is curious, because COMBIN's
results seem consistent with an underlying machine precision
implementation
of GAMMALN.

Since Excel does not support the (mathematically and statistically useful)
analyitic continuation of COMBIN to non-integers, it is sloppy that they
did
not round the result to an integer when that result is <= 2^53-1 =
9007199254740991; but it is an easy matter for the user to rectify this in
practice.

Jerry