How do I use the Gamma Function in Excel

[Guest]

Hi!
I'm trying to use a formula that contains the Gamma function in Excel
but this does not seem to be under the list of functions. If I type its
definition:
Gamma(n)=(n-1)! it gives an error message when n-1 is not
an integer.
Has anyone got any ideas how I can get past this?
Thanks

 

[Bob Phillips]

There is no intrinsic GAMMA function in Excel, although there are GAMMADIST,
GAMMAINV, GAMMALN. I suggest that you look these up in help to see what they
do.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Based on Bob's suggestion, try this:

Put the number you want Gamma of in A1.

Put some other number in A2. "1" seems to work fine.

Put =GAMMADIST(A2,A1,1,FALSE) in A3

Then you can get Gamma(A1) with =EXP(-A2)*A2^(A1-1)/A3

This appears to work for positive A1.


Art

 

[Guest]

This may be easier than my previous post:

use =EXP(-1)/GAMMADIST(1,A1,1,FALSE) for Gamma(A1)

Again, this doesn't appear to work for negative A1.

Art

 

[Dana DeLouis]

This is one way to get Gamma(A1):

=EXP(GAMMALN(A1))

It's been noted before that it's only good to about 9 or 10 digits of
accuracy.

 

[Guest]

Thanks to all of you for your help! I think it's simplest to take the
exponential of the log of the Gamma function as Dana suggested, the degree of
accuarcy is sufficient for my project, thanks again!

 

[hrlngrv - ExcelForums.com]

Dana DeLouis wrote..

This is one way to get Gamma(A1)

It's been noted before that it's only good to about 9 or 10 digit o
accuracy

GAMMALN's poorer around 1 and 2, where it evaluates negative

FWIW, the lngamma udf in the .xls file in following linked .zip fil
does a reasonable job, accurate to 14+ decimal digits

ftp://members.aol.com/hrlngrv/gammaln8.zi

Perhaps in a few more versions Microsoft might actually use th
publicly available (and not even GPLed) netlib code in all th
'engineering' and stats functions in Excel