gammadist function

  • Thread starter Thread starter Erwin Kalvelagen
  • Start date Start date
E

Erwin Kalvelagen

Hi:

I probably should get myself a cup of coffee first, but it looks
like Excel 2000 can not calculate

=+GAMMADIST(0.1,0.1,1,TRUE)

It gives #NUM!. I am not sure why this happens. Using
x=0.09 or x=0.12 gives normal results. I don't think the
cdf of the gamma function is particularly difficult there.

Erwin
 
. . . but it looks like Excel 2000 can not calculate

=+GAMMADIST(0.1,0.1,1,TRUE)

It gives #NUM!. . . .
Click to expand...

You're correct. This may have been fixed in more recent versions, but XL2K and
prior have some rather poorly implemented stats functions. If you're using Excel
in a corporate environment, so aren't allowed to upgrade on your own but must
wait for your IT department to get around to it, you'll need to roll your own or
use eomeone else's. I haven't checked whether Ian Smith's VBA stats functions
include a replacement for GAMMADIST, but it's got lots of other good stuff. See
the link in

http://www.google.com/groups?selm=407CAB33.8020801@no_e-mail.com
 
Harlan said:
...

You're correct. This may have been fixed in more recent versions, but
XL2K and prior have some rather poorly implemented stats functions.
Click to expand...

Hi Harlan, Erwin
just as a follow-up. Formula does also return an error for Excel 2003
(German version). So obviously not fixed


Frank
 
...
...
just as a follow-up. Formula does also return an error for Excel 2003
(German version). So obviously not fixed
Click to expand...

Thanks for the follow-up. Pathetic on Microsoft's part. 123 Release 5 (almost 10
years old) has no problem with @GAMMAI(0.1,0.1) which returns

0.82755175874893202

with agrees to 8 decimal places with R's pgamma(0.1, 0.1). Since the folks in
Redmond were so proud of themselves for all their improvements to other stats
functions, I wonder when (if?!) they'll fix this.
 
Harlan said:
...
..

Thanks for the follow-up. Pathetic on Microsoft's part. 123 Release 5
(almost 10 years old) has no problem with @GAMMAI(0.1,0.1) which
returns

0.82755175874893202

with agrees to 8 decimal places with R's pgamma(0.1, 0.1). Since the
folks in Redmond were so proud of themselves for all their
improvements to other stats functions, I wonder when (if?!) they'll
fix this.
Click to expand...

Hi Harlan
first there'll be the essential features like blinking cells ;-)

Frank
 
Looks like it is sensitive to Alpha not being equal to x. (Or something to
that affect)

=GAMMADIST(0.1,0.100000000001,1,TRUE)

returns:
0.827551758677618


Agrees with mma.

CDF[GammaDistribution[1/10, 1], 1/10]
0.8275517595858506
 
I was informed there is a knowledge base article on this:
Microsoft Knowledge Base Article - 215214.

I got quite some replies: most (all?) Excel versions
seem to suffer from this.

Thanks for the feedback.

----------------------------------------------------------------
Erwin Kalvelagen
GAMS Development Corp., http://www.gams.com
(e-mail address removed), http://www.gams.com/~erwin
----------------------------------------------------------------


Dana DeLouis said:
Looks like it is sensitive to Alpha not being equal to x. (Or something to
that affect)

=GAMMADIST(0.1,0.100000000001,1,TRUE)

returns:
0.827551758677618


Agrees with mma.

CDF[GammaDistribution[1/10, 1], 1/10]
0.8275517595858506


--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =



Harlan Grove said:
...
..

Thanks for the follow-up. Pathetic on Microsoft's part. 123 Release 5 (almost 10
years old) has no problem with @GAMMAI(0.1,0.1) which returns

0.82755175874893202

with agrees to 8 decimal places with R's pgamma(0.1, 0.1). Since the folks in
Redmond were so proud of themselves for all their improvements to other stats
functions, I wonder when (if?!) they'll fix this.
Click to expand...
Click to expand...
 
Erwin Kalvelagen said:
Hi:

I probably should get myself a cup of coffee first, but it looks
like Excel 2000 can not calculate

=+GAMMADIST(0.1,0.1,1,TRUE)

It gives #NUM!. I am not sure why this happens. Using
x=0.09 or x=0.12 gives normal results. I don't think the
cdf of the gamma function is particularly difficult there.

Erwin


----------------------------------------------------------------
Erwin Kalvelagen
GAMS Development Corp., http://www.gams.com
(e-mail address removed), http://www.gams.com/~erwin
----------------------------------------------------------------
Click to expand...

This is known see http://support.microsoft.com/default.aspx?scid=kb;en-us;215214#appliesto.

The function cdf_gamma in
http://members.aol.com/iandjmsmith/Examples.xls will evaluate
gammadist accurately.

Ian Smith
 
Looks like it is sensitive to Alpha not being equal to x. (Or something to
that affect)

=GAMMADIST(0.1,0.100000000001,1,TRUE)

returns:
0.827551758677618
Click to expand...
...

But

=GAMMADIST(0.1,0.099999999999999,1,1)

and

=GAMMADIST(0.100000000000002,0.100000000000001,1,1)

both return #NUM!, so it looks like x must be less than alpha for small alpha.
But wait!

=GAMMADIST(0.119628368603,0.099999999999999,1,1)

returns 0.841083433274150 and

=GAMMADIST(0.119628368603,0.100000000000001,1,1)

returns 0.841083433274147, but nuttin' but #NUM! for x between alpha and these
higher x values. Not good. Definitely not good.
 
Back
Top