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

#### Lehoi

##### Board Regular
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):

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:

### Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
The error I get is Out of Stack Space (from MSDN: The stack is a working area of memory that grows and shrinks dynamically with the demands of your executing program. Its limits have been exceeded.). One solution would be to use an array (make sure you dimension it large enough), store the interim values in the array, and then loop thru the array to calculate the result.

Hi Dr. Demento

Thanks for the info, unfortunately my vba knowledge is very limited to do what you suggest (to me arrays are big words ).

ADVERTISEMENT
Give this a try:

Code:
``````Function PStreak(N As Long, M As Long, p As Double) As Double
' shg 2016
' UDF or VBA

' returns the probability of a streak of N heads or greater in M trials
' where probability(heads) = p

' https://www.reddit.com/r/math/comments/4kj27s/probability_of_getting_n_heads_in_a_row_after_m/d3ftvvw/

Dim Pr()          As Double
Dim q             As Double
Dim i             As Long

ReDim Pr(0 To M + 1)
q = 1 - p
Pr(N) = p ^ N

For i = N To M
Pr(i + 1) = Pr(i) + (1 - Pr(i - N)) * q * p ^ N
Next i
PStreak = Pr(M)
End Function``````

Last edited:
For example,

 A​ B​ C​ D​ 1​ M​ N​ P(N, M)​ ​ 2​ 1,000,000​ 10​ 0.999999999999887​ C2: =PStreak(B2,A2,0.5) 3​ 1,000,000​ 11​ 0.999999999999774​ 4​ 1,000,000​ 12​ 0.999999999999546​ 5​ 1,000,000​ 13​ 0.999999999999092​ 6​ 1,000,000​ 14​ 0.999999999998182​ 7​ 1,000,000​ 15​ 0.999999764652045​ 8​ 1,000,000​ 16​ 0.999514459719288​ 9​ 1,000,000​ 17​ 0.977959965363078​ 10​ 1,000,000​ 18​ 0.851531938761876​ 11​ 1,000,000​ 19​ 0.614677964741344​ 12​ 1,000,000​ 20​ 0.379253961388955​ 13​ 1,000,000​ 21​ 0.212124558873635​ 14​ 1,000,000​ 22​ 0.112376156237907​ 15​ 1,000,000​ 23​ 0.057861960660055​ 16​ 1,000,000​ 24​ 0.029361996854594​ 17​ 1,000,000​ 25​ 0.014790356217504​

ADVERTISEMENT
Hello shg

You are a excel jewel maker, works like a charm! thank you very much!! ray:

Thanks also to Dr. Demento for the 2 links, very useful indeed!

Kind regards both

You're welcome.

Glad to help.

A little cleanup:

Code:
``````Function ProbRun(iRun As Long, nTrial As Long, dProb As Double) As Double
' shg 2016
' UDF or VBA

' Returns the probability of iRun or more consecutive heads occurring in nTrial tosses
' of a coin with probability(heads) = dProb

' https://www.reddit.com/r/math/comments/4kj27s/probability_of_getting_n_heads_in_a_row_after_m/

Dim adP()         As Double
Dim qpN           As Double
Dim i             As Long

ReDim adP(0 To nTrial)
qpN = (1 - dProb) * dProb ^ iRun
adP(iRun) = dProb ^ iRun

For i = iRun To nTrial - 1
adP(i + 1) = adP(i) + (1 - adP(i - iRun)) * qpN
Next i

ProbRun = adP(nTrial)
End Function``````

Replies
6
Views
200
Replies
7
Views
345
Replies
3
Views
1K
Replies
23
Views
5K
Replies
1
Views
609

Threads
1,196,374
Messages
6,014,900
Members
441,854
Latest member
Amstaff

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back