Excel Add-Ins Incomplete Gamma Function

  • Thread starter Thread starter vickyho1008
  • Start date Start date
V

vickyho1008

Does anyone know where I can download an excel add-in to calculat
Incomplete gamma function? I have already found an add-in on the we
to calculate the Gamma function, but it cannot do Incomplete Gamm
function which takes in 2 parameters rather than one. Help please~~
kinda urgen
 
vickyho1008 > said:
Does anyone know where I can download an excel add-in to calculate
Incomplete gamma function? I have already found an add-in on the web
to calculate the Gamma function, but it cannot do Incomplete Gamma
function which takes in 2 parameters rather than one. Help please~~!
kinda urgent
Click to expand...

No add-in needed. You just need to adapt the results from Excel's own
GAMMADIST function. Since you're mentioning two parameters, try

=EXP(GAMMALN(alpha))*GAMMADIST(x,alpha,1,1)
 
If I type GAMMA(1.0345, 0.0247) in Maple, it returns resul
0.9604748394. From the formula you were given above, which numbe
should I use as alpha and what number should I use for x? thank
 
Here's a Custom function that I use. I combined the Gamma & Incomplete
Gamma together. I tried to keep it similar to Mathematica. I haven't
incorporated too much error checking though.

Function Gamma(z, Optional Alpha As Double = 0)
'// Dana DeLouis
Dim n As Double
With WorksheetFunction

If Alpha = 0 Then
'Gamma Function

'If it's close to an Integer, try to use Factorial
n = Round(z, 12) ' Your limit here!
If n - Int(n) = 0 Then
Gamma = .Fact(z - 1)
Else
Gamma = Exp(.GammaLn(z))
End If
ElseIf Alpha > 0 Then
'Incomplete Gamma function
Gamma = Exp(.GammaLn(z)) * (1 - .GammaDist(Alpha, z, 1, True))
Else
' An error
Gamma = "Alpha < 0"
End If

End With
End Function

Sub TestIt()
Debug.Print Gamma(5)
Debug.Print Gamma(1.0345, 0.0247)
End Sub

Returns...

24
0.960474839401151

HTH. :>)
 
I was thinking of adding the "Generalized Incomplete Gamma function"

Function Gamma(z, a, b)
'// Dana DeLouis
'// Generalized Incomplete Gamma function
With WorksheetFunction
Gamma = Exp(.GammaLn(z)) * (.GammaDist(b, z, 1, True) -
..GammaDist(a, z, 1, True))
End With
End Function

Sub TestIt()
Debug.Print Gamma(5, 2, 3)
End Sub

returns...
3.17000964098241


Here's Mathematica's (You know...the 'other' program! :>)

Gamma[5, 2, 3.]
3.1700097151804023

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


Dana DeLouis said:
Here's a Custom function that I use. I combined the Gamma & Incomplete
Gamma together. I tried to keep it similar to Mathematica. I haven't
incorporated too much error checking though.

Function Gamma(z, Optional Alpha As Double = 0)
'// Dana DeLouis
Dim n As Double
With WorksheetFunction

If Alpha = 0 Then
'Gamma Function

'If it's close to an Integer, try to use Factorial
n = Round(z, 12) ' Your limit here!
If n - Int(n) = 0 Then
Gamma = .Fact(z - 1)
Else
Gamma = Exp(.GammaLn(z))
End If
ElseIf Alpha > 0 Then
'Incomplete Gamma function
Gamma = Exp(.GammaLn(z)) * (1 - .GammaDist(Alpha, z, 1, True))
Else
' An error
Gamma = "Alpha < 0"
End If

End With
End Function

Sub TestIt()
Debug.Print Gamma(5)
Debug.Print Gamma(1.0345, 0.0247)
End Sub

Returns...

24
0.960474839401151

HTH. :>)
Click to expand...
 
...
...
Function Gamma(z, Optional Alpha As Double = 0) ...
ElseIf Alpha > 0 Then
'Incomplete Gamma function
Gamma = Exp(.GammaLn(z)) * (1 - .GammaDist(Alpha, z, 1, True))
Click to expand...
...

First off, you're using z as the gamma function parameter and alpha as the
incomplete gamma function's independent variable/integration bound. Seems
exactly backwards and rather antimnemonic.

Next, isn't the incomplete gamma function an increasing function with respect to
its integration bound? The expression above is a decreasing function of 'Alpha'.
 
thanks for all the replies. By the way, where I can put these codin
into under Excel
 
Thanks Harlan! I think you are absolutely correct. It would be better to
reverse everything. It is confusing. It's been a while, but I now see the
problem I had from a while ago.

Here is Mma info on Gamma:

Information["Gamma"]

"Gamma[z] is the Euler gamma function. Gamma[a, z] is the incomplete gamma
function. Gamma[a, z0, z1] is the generalized incomplete gamma function..."

What threw me off was "z" being used in just Gamma[z], but now I see that
"z" becomes an integration limit in Gamma[a,z]. Add to this having to use
the Gamma Distribution in Excel, and I had it backwards.
In addition, mma also mentions that "...Note that the arguments in the
incomplete form of Gamma are arranged differently from those in the
incomplete form of Beta." Coupled together, I thought the "other way" was
correct.

I may still have it backwards, but I "think" it is a decreasing function. I
have never gotten it to work without "1 - .GammaDist(..." According to
mma, it's an integration from z to infinity.

Here's the op's Maple problem, along with mma definition of the incomplete
gamma function.

{a = 1.0345, z = 0.0247};

Integrate[t^(a - 1)/E^t,{t, z, Infinity}]
0.9604748394434477

(Same answer as Op's Maple program)

Here's a short table as z increases...
Table[Gamma[4., z], {z, 1, 5}]

5.886071058743077,
5.1427407629912825,
3.883391332693388,
2.6008207222002535,
1.5901554917841703

Anyway, I think you are correct. Switching everything around would be
better. (And would be more in line with mma & Maple.)

Function Gamma(Alpha, z)
With WorksheetFunction
Gamma = Exp(.GammaLn(Alpha)) * (1 - .GammaDist(z, Alpha, 1, True))
End With
End Function

Sub TestIt()
Debug.Print Gamma(1.0345, 0.0247)
End Sub

returns:
0.960474839401151

Thanks again! This cleared up a related problem. :>)
 
...
...
I may still have it backwards, but I "think" it is a decreasing function. I
have never gotten it to work without "1 - .GammaDist(..." According to
mma, it's an integration from z to infinity.
Click to expand...
...

It appears that Maple and Mathematica default to the upper incomplete gamma
function, as defined in

http://mathworld.wolfram.com/IncompleteGammaFunction.html

whereas Numeric Recipes in Whatever defaults to the lower counterpart

http://www.library.cornell.edu/nr/bookcpdf/c6-2.pdf

as does O-Matrix's gammainc function

http://www.omatrix.com/manual/gammainc.htm

and Wikipedia is neutral

http://en.wikipedia.org/wiki/Incomplete_gamma_function

It seems that the unqualified term 'incomplete gamma function' is as ambiguous
as 'drive on the correct side of the road' or 'spell color and behaviour
correctly'. However, I should have clued in from the OP's example.
 
vickyho1008 > said:
..By the way, where I can put these coding
into under Excel?
Click to expand...

Perhaps some steps to ease you in?
--
Press Alt + F11 to go to VBE
Click Insert > Module

Copy > Paste Dana's Function Gamma - viz.
everything within the dotted lines below
[from "begin vba" till "end vba"]
into the empty white space on the right side in VBE

---------begin vba----------
Function Gamma(z, Optional Alpha As Double = 0)
'// Dana DeLouis
Dim n As Double
With WorksheetFunction

If Alpha = 0 Then
'Gamma Function

'If it's close to an Integer, try to use Factorial
n = Application.WorksheetFunction.Round(z, 12) '
Your limit here!
If n - Int(n) = 0 Then
Gamma = .Fact(z - 1)
Else
Gamma = Exp(.GammaLn(z))
End If
ElseIf Alpha > 0 Then
'Incomplete Gamma function
Gamma = Exp(.GammaLn(z)) * (1 - .GammaDist(Alpha,
z, 1, True))
Else
' An error
Gamma = "Alpha < 0"
End If

End With
End Function
---------end vba----------

Press Alt + Q to exit and return to Excel

-------
In Sheet1 (say)
---------
With:

1.0345 in A1
0.0247 in B1

you can call, say in C1: =gamma(A1,B1)
[to give GAMMA(1.0345, 0.0247)]
 
thanks Max, I have followed everything as you said above, but in cel
C1, it comes up with #NAME! ?? something is wrong? or excel can'
calculate
 
sorry, please ignore what I was saying above.

It works actually by using Dana's revised function as follows:

Function Gamma(Alpha, z)
With WorksheetFunction
Gamma = Exp(.GammaLn(Alpha)) * (1 - .GammaDist(z, Alpha, 1, True))
End With
End Function

it works perfectly, thanks for everyone's effort!! I am so happy
:cool
 
Lotus vs. Excel Macro

The information here is great! I have a follow-up question:

The GammaI function in Lotus 1-2-3 gives me a different number than the result I get after I applied the formula described in this forum. The number that I get using the formula you provided here actually consistent with the calculator found here:

http://www.danielsoper.com/statcalc/calc23.aspx


What's the difference between the formulas? What does the Lotus formula actually give me?

I am trying to convert an old Lotus file I had to Excel, but could not reproduce the number I got. Any input would be appreciated.

Thanks,

Ziv
 
Hello,

I am trying to input a generalized gamma curve into excel. We have curve parameters from SAS (shape, scale, and intercept), but are having trouble figuring out how to use them in the formula given for the survival function G'(v) on the SAS website http://support.sas.com/documentation...eg_sect019.htm

We’re unsure of what δ (the free shape parameter) refers to in the SAS outputs. We initially thought it would refer to the ‘shape’ parameter, but that doesn’t seem to work. For the incomplete gamma function, we have been using the GAMMA.DIST function, and for the complete gamma function, we’ve been using EXP(GAMMALN.

This is very urgent
icon_warning.gif
, so I would appreciate any helps as soon as possible. Thank you very much in advance!!
 
Back
Top