## Excel Add-Ins Incomplete Gamma Function

MS EXCEL

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

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

```"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

```

### Re: Excel Add-Ins Incomplete Gamma Function

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

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

```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) -
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

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

```

### Re: Excel Add-Ins Incomplete Gamma Function

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

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

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

```Pleased to hear that, Vicky !
Thanks for the feedback

```

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

```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
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.

Kevin

```

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

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

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

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?