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

Lehoi

Board Regular
Joined
Jan 30, 2016
Messages
88
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:

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Dr. Demento

Well-known Member
Joined
Nov 2, 2010
Messages
573
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

Board Regular
Joined
Jan 30, 2016
Messages
88
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 :rolleyes:).
 

Dr. Demento

Well-known Member
Joined
Nov 2, 2010
Messages
573

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,779
Office Version
  1. 2010
Platform
  1. Windows

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:

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,779
Office Version
  1. 2010
Platform
  1. Windows
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

Board Regular
Joined
Jan 30, 2016
Messages
88

ADVERTISEMENT

Hello shg

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

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

Kind regards both
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,779
Office Version
  1. 2010
Platform
  1. Windows
You're welcome.
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,779
Office Version
  1. 2010
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,233
Messages
5,594,964
Members
413,955
Latest member
FalcoDaz

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

Which adblocker are you using?

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
Top