Excel's COMBIN and integers
On 4/8/2010 10:36 PM, Jerry W. Lewis wrote:
"Joe User" wrote:
I would be surprised to see that kind of accuracy from a Gamma
approximation, much less Exp and GammaLn approximations.
You don't get it from Excel's 10-figure approximation to GammaLn, but that
does not mean that the approach is bad.
I would be interested in seeing the exact results for either the GammaLn or
Gamma formula from an independent math program.
My statement that COMBIN's results seemed consistent with an underlying
machine precision implementation of GAMMALN was based on comparing results
with
exp(lgamma(n+1)-lgamma(r+1)-lgamma(n-r+1))
in R, which you can access from Excel via the RExcel COM server
Here are some specific resuls:
COMBIN(9,3) COMBIN(53,21)
COMBIN 8.3999999999999986E1 3.1798644182805512E14
R lgamma 8.3999999999999986E1 3.1798644182804919E14
R lchoose 8.3999999999999986E1 3.1798644182805375E14
DP 8.3999999999999986E1 3.1798644182805375E14
EP 8.4000000000000000E1 3.1798644182805500E14
DP and EP are hypothetical implementations operations performed in the order
of my original post with a lnGAMMA that is exact to the last bit in double
precision (8-byte real) and extended precision (10-byte real), but no extra
steps to minimize cancellation errors.
Jerry
Hi. If anyone is interested...
If one were to calculate Combin(100,36) via the basic equation, then the
numerator alone (ie 100!) has 158 digits.
Here's an alternative for integer inputs.
Sub Demo()
Debug.Print dCombin(53, 21)
Debug.Print dCombin(100, 36)
Debug.Print dCombin(120, 28)
End Sub
Returns:
317,986,441,828,055
1,977,204,582,144,932,989,443,770,175
1,763,957,085,749,372,402,201,417,160
Function dCombin(x, y)
Dim t As Variant
Dim J
x = x - y
t = CDec(x + 1)
For J = 2 To y
t = t * (x + J) / J
Next J
dCombin = FormatNumber(t, 0, , , vbTrue)
End Function
= = = = = = =
HTH :)
Dana DeLouis
|