Results 1 to 6 of 6

20030610, 04:27 #1
 Join Date
 Sep 2001
 Location
 California
 Posts
 106
 Thanks
 0
 Thanked 0 Times in 0 Posts
Hypergeometric Distribution (2000/SR1)
I'm getting a #NUM error for HYPOGEOMDIST(46,431,160,1350). When I check Excel Help for this function, the Remarks indicate that a #NUM error will appear in 5 situations and according to my calculations my numbers don't meet the criteria. Is my sample population (number_sample) to the total population (number_population) too high? If so, what is the threshold for this Function?
Thanks,
Caroline in lalaland

20030610, 06:59 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Hypergeometric Distribution (2000/SR1)
Your numbers are so high that they cause an overflow at some point during the calculation. I don't think it's easy to give exact tresholds because there are 4 parameters involved. If you really need this, a dedicated mathematics package might be better suited to your purpose.

20030610, 07:35 #3
 Join Date
 Feb 2001
 Location
 Weert, Limburg, Netherlands
 Posts
 4,812
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Hypergeometric Distribution (2000/SR1)
I had a look.
It seems your specific combination of data exceeds the limits of what the HYPGEOMDIST function can handle. Since it uses faculty calcs, I suspect the numbers have become too large for excel to handle whilst trying to resolve the function. If I try to split the function into a combination of COMBIN functions, I get the same result.
Excel's statistical functions are reknown for their lack of precision and inconsistency (this page). If you need to do serious statistical work, leave Excel alone.
I strongly suspect it wouldn't be too hard to envisage a user defined function to solve this though, but it is over my head.Jan Karel Pieterse
Microsoft Excel MVP, WMVP
www.jkpads.com
Professional Office Developers Association

20030610, 11:52 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Hypergeometric Distribution (2000/SR1)
If you still want to do this in Excel, here is a custom function with the same argument order as the builtin function that is able to handle larger numbers:
Function HypGeo(x As Long, y As Long, M As Long, N As Long)
Dim res As Double
Dim i As Long
Dim L1 As Long
Dim L2 As Long
Dim L3 As Long
Dim M1 As Long
Dim M2 As Long
Dim M3 As Long
Dim LTot As Long
On Error GoTo ErrHandler
If x < 0 Or y < 0 Or M < 0 Or N < 0 Or x > M Or (y  x) > (N  M) Or y > N Or _
x > y Or M > N Then
HypGeo = "#VALUE"
Exit Function
End If
M1 = M
L1 = Application.WorksheetFunction.Min(x, M  x)
M2 = N  M
L2 = Application.WorksheetFunction.Min(y  x, (N  M)  (y  x))
M3 = N
L3 = Application.WorksheetFunction.Min(y, N  y)
LTot = Application.WorksheetFunction.Max(L1, L2, L3)
res = 1#
For i = 1 To LTot
If i <= L1 Then
res = res * (i + M1  L1) / i
End If
If i <= L2 Then
res = res * (i + M2  L2) / i
End If
If i <= L3 Then
res = res * i / (i + M3  L3)
End If
Next i
HypGeo = res
Exit Function
ErrHandler:
HypGeo = "#VALUE"
End Function

20030610, 16:14 #5
 Join Date
 Jan 2001
 Location
 Kortrijk, Belgium
 Posts
 571
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Hypergeometric Distribution (2000/SR1)
Depending on what you want to do, but if n/N<0.1 (where n is the sample size and N is the batch size), the binomial distribution is a good approximation to the Hyper Geometrical Distribution, using =BINOMDIST(46;431;160/1350;FALSE). It has the great advantage that it allows you to calculate the cumulative value by just switching FALSE to TRUE.

20030610, 22:18 #6
 Join Date
 Sep 2001
 Location
 California
 Posts
 106
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Hypergeometric Distribution (2000/SR1)
Excellent  this works. Thank you all for your suggestions!
Thanks,
Caroline in lalaland