1. ## 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?

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

3. ## Re: Hypergeometric Distribution (2000/SR1)

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.

4. ## 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 built-in 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

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

6. ## Re: Hypergeometric Distribution (2000/SR1)

Excellent -- this works. Thank you all for your suggestions!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•