Excel Add-Ins Incomplete Gamma Function

MS EXCEL

    Sponsored Links

    Next

  • 1. Count the number of names in a column
    I have a worksheet that lists names in one column. Is there a formula that'll calculate the number of people represented? For example, column A lists: Fred Tony Fred Fred Max The five cells represent 3 people. I'm looking for a formula that'll tell me the list represents 3 people. Thanks for your help!
  • 2. help with sum if function
    How can i use sum if function to sum numbers in columns O20:O33 only if cell C14 has a "N' in it.
  • 3. Creating Formulas from Filtered Data
    I am trying to create a formula, say in cell B1 that would return the same info that is returned from my filter. For example if i filter my data on colors and I return a 'red' result, I would like cell B2 to read 'Colors - Red'
  • 4. Making a chart to track entries across timeframes
    So I have a tough one to figure out. I have a few columns of data, two with dates, the start date and the finish date, the other with a description. I need to create a graph that tracks each week and tallies how many items started in a week, then a second column representing which items finished in that week. There would be a timeline along the x-axis, and a value for the y-axis to track the numbers of items that occured in that week.
  • 5. vlookup / index for a large spreadsheet
    Hi I am trying to lookup a name for an employee ID. I have tried vlookup and the index functions. The initial setup works; however, when I copy / paste the function, the subsequent values for the array changes. originial function =INDEX(EIDs!A1:B41,MATCH(G113,EIDs!A1:A41,0),2) function further down in spreadsheet =INDEX(EIDs!A13:B53,MATCH(G125,EIDs!A13:A53,0),2) I have about 6000 records I'm trying to update, it would be nice if I could somehow keep the range the same. Does anyone have any suggestions? Thank you Cynthia

Excel Add-Ins Incomplete Gamma Function

Postby vickyho1008 » Sun, 11 Apr 2004 23:01:45 GMT

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


Re: Excel Add-Ins Incomplete Gamma Function

Postby Harlan Grove » Mon, 12 Apr 2004 02:02:42 GMT

"vickyho1008 >" << XXXX@XXXXX.COM > wrote...

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)



Re: Excel Add-Ins Incomplete Gamma Function

Postby vickyho1008 » Mon, 12 Apr 2004 22:49:32 GMT

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


Re: Excel Add-Ins Incomplete Gamma Function

Postby Dana DeLouis » Tue, 13 Apr 2004 00:24:17 GMT

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.  :>)


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









Re: Excel Add-Ins Incomplete Gamma Function

Postby Dana DeLouis » Tue, 13 Apr 2004 01:52:08 GMT

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
= = = = = = = = = = = = = = = = =






message





Re: Excel Add-Ins Incomplete Gamma Function

Postby vickyho1008 » Wed, 14 Apr 2004 04:50:18 GMT

thanks for all the replies.  By the way, where I can put these codin
into under Excel


Re: Excel Add-Ins Incomplete Gamma Function

Postby Dana DeLouis » Wed, 14 Apr 2004 04:52:06 GMT

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. :>)

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






respect to
'Alpha'.



Re: Excel Add-Ins Incomplete Gamma Function

Postby vickyho1008 » Sat, 17 Apr 2004 01:38:15 GMT

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


Re: Excel Add-Ins Incomplete Gamma Function

Postby vickyho1008 » Sat, 17 Apr 2004 01:58:49 GMT

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


Re: Excel Add-Ins Incomplete Gamma Function

Postby Max » Sat, 17 Apr 2004 05:30:16 GMT

Pleased to hear that, Vicky !
Thanks for the feedback


Similar Threads:

1.Add-ins do not show up in the Add-Ins Ribbon

I am using Excel 2007 on Windows XP.  I have several Add-ins selected and 
they show up under Active Application Add-ins, form the Excel Options, 
Add-Ins Dialog but they do not show up on the Add-Ins Ribbon or the View 
Ribbon.

I can I find them?

Thanks
Trebormac 

2.Solver in add-ins folder, but not in add-ins under tools menu

I've been trying to use the solver function in excel and ive been
scouring the internet for solutions but cannot find one yet.

I have office 2004 11.2.3 on a well equipped mac with os 10.3.9. I
checked the add-ins folder (Office 2004>Office>Add-Ins) and I have
Solver Library and Solver.xla in the folder. When I go to Tools>Add-
Ins in the Excel program, I don't have any option to click solver.

I appreciate your help.

Kevin

3.How do I use the Gamma Function in Excel

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

4.Add-ins function in Excel 2003


  How do I find, load and install the add-in features on the Excel 2003 
office program ?

5.HTML Help Workshop and Custom VBA Excel Functions/Add-Ins

Folks,

I am using the HTML Help Workshop to create custom help files of the
.chm variety.  I am including context IDs in the file, and it is our
hope to be able to call the custom help file from custom VBA functions
and Add-ins in Excel 2000.  The following is an example of what I
might want to do:
--------------------
Option Explicit
--------------------
Public Function AddTwo(c As Double, d As Double) As Double

'Application.Assistant.Visible = False
Application.Help ActiveWorkbook.Path & "\HelpFile.chm", 2001

AddTwo = c + d

End Function
--------------------
Public Function SubtractTwo(c As Double, d As Double) As Double

'Application.Assistant.Visible = False
Application.Help ActiveWorkbook.Path & "\HelpFile.chm", 3001

SubtractTwo = c - d

End Function
--------------------

I am setting the help file in the project properties with a context ID
of 1001 (the default page.)  When I input our custom functions from
the formula palette and choose the question mark button, the office
assistant fires (attempting to lock him/her out has proven pointless
to date!)  If I choose "Help with this feature" and then "Help on
selected function" then wew get the default page of our help file -
the one for the project with context ID 1001.  Obviously, we would
like to pull up the page with the context ID associated directly with
the function, i.e. context ID 2001 or context ID 3001 depending.

In addition, I am having difficulty getting my custom help file to
fire at all when my code is stored in an add-in.  Is there a secret to
referencing a custom help file that will allow me to access my
preferred potions based on context ID from an add-in?

Thanks for your help, everyone.

David

6. Better Graph Add-ins/Plug-ins

7. Disabling Add-Ins, plug ins

8. VBA function for XL add-ins



Return to MS EXCEL

 

Who is online

Users browsing this forum: No registered users and 75 guest