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

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

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

Code:

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

Last edited: