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

 

[Dr. Demento]

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.

 

[Lehoi]

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

 

[Dr. Demento]

You know, watching how this code operates (where px recursively calls the function it's in) made me realize that this is beyond me. Sorry

Perhaps this site can assist, as it has good explanations with examples (click on the graphics at the bottom of the page for the download page).
Free Online Monte Carlo Simulation Tutorial for Excel
Additionally, this link might be helpful: http://www.mrexcel.com/forum/excel-...ications-monte-carlo-simulation-question.html

 

[shg]

Give this a try:

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

 

[shg]

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​

 

[Lehoi]

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

 

[shg]

You're welcome.

 

[Dr. Demento]

Glad to help.

 

[shg]

A little cleanup:

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