Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    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 lala-land

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

  3. #3
    Platinum Lounger
    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.jkp-ads.com
    Professional Office Developers Association

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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 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. #5
    4 Star Lounger
    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.

  6. #6
    2 Star Lounger
    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 lala-land

Posting Permissions

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