Excel Add-Ins Incomplete Gamma Function
vickyho1008 <>
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
---
Message posted from http://www.ExcelForum.com/
Harlan Grove
>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
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)
vickyho1008 <>
0.9604748394. From the formula you were given above, which number
should I use as alpha and what number should I use for x? thanks
Dana DeLouis
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. :>)
--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =
"vickyho1008 >" <<vickyho10...@excelforum-nospam.com> wrote in message
news:vickyho10...@excelforum-nospam.com...
Dana DeLouis
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" <del...@bellsouth.net> wrote in message
news:%23cOTek9...@TK2MSFTNGP09.phx.gbl...
Harlan Grove
..
>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))
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'.
--
To top-post is human, to bottom-post and snip is sublime.
vickyho1008 <>
into under Excel?
Dana DeLouis
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. :>)
--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =
"Harlan Grove" <hrl...@aol.com> wrote in message
news:vwAec.1153$H4...@www.newsranger.com...
Harlan Grove
..
>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.
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.
Max
> ..By the way, where I can put these coding
>into under Excel?
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)]
--
Rgds
Max
xl 97
---
Please respond, in newsgroup
xdemechanik <at>yahoo<dot>com
----
vickyho1008 <>
C1, it comes up with #NAME! ?? something is wrong? or excel can't
calculate?
vickyho1008 <>
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:
Max
Thanks for the feedback
--
Rgds
Max
xl 97
---
Please respond, in newsgroup
xdemechanik <at>yahoo<dot>com
----