Bernoulli trials macro give's me an error when greater value is introduced

[Lehoi]

Hi

From this web I use this macro for calculate the probability of observing a minimum of K consecutive successes in N binomial trials, where p is the probability of success (in this example 0,5):

Public Function pStreak(nTries, nHits, p)


Dim c As Collection


Set c = New Collection
pStreak = pS(nTries, nHits, p, c)
Set c = Nothing


End Function


Private Function pS(nTries, nHits, p, c As Collection)


On Error GoTo err
pS = c(CStr(nTries)) 'if nonexisting the exception jumps to err label
Exit Function


err:


If nHits > nTries Or nTries <= 0 Then
result = 0
Else
result = p ^ nHits
For j = 1 To nHits
px = pS(nTries - j, nHits, p, c)
result = result + ((p ^ (j - 1)) * (1 - p) * px)
Next
End If
c.Add result, CStr(nTries) 'we take the string of nTries so the collection index is hashed
pS = result
End Function

To check the results i use this calculator and the results are ok.
The problem is when i want to use higher values of Number of trials (N) like 1000, the function give's me an #VALUE! error.

Any solution please?

regards

 

[Lehoi]

Hi shg

Works perfect too
Is the code of this last function better (more efficient, more trustable, etc.) than the code of the first function, or it is another way of coding to make the same calculation?
Thanks!
Lehoi

edit: I think understand the cleanup of the code that you referred.

 

[shg]

Just a little faster.

 

[Lehoi]

Great, thanks again shg

 

[shg]

You're welcome.