Probability Sequence Until Success

[malcolmrdj]

Suppose I have success rate probabilities based on years of experience and I am looking to generate random simulations indication what year success is achieved. If not in year one, move to year two, and so forth until success.

Year Success Rate
1 5%
2 15%
3 25%
4 35%

For instance, if year 1 there is 5% success rate, then either a) 5% hit, success, end iteration and note as year 1, or b) 95% chance fail, then move on to year 2 with either a) 15% hit, success, end iteration and note as year 2, or b) on to year 3 and so forth...

I would perform n number of times generating a table with results (say 4,4,3,3,2,1).

Thanks for any help.

 

[malcolmrdj]

Suppose I have success rate probabilities based on years of experience and I am looking to generate random simulations indication what year success is achieved. If not in year one, move to year two, and so forth until success.

Year Success Rate
1 5%
2 15%
3 25%
4 35%

For instance, if year 1 there is 5% success rate, then either a) 5% hit, success, end iteration and note as year 1, or b) 95% chance fail, then move on to year 2 with either a) 15% hit, success, end iteration and note as year 2, or b) on to year 3 and so forth...

I would perform n number of times generating a table with results (say 4,4,3,3,2,1).

Thanks for any help.

Back, making advancements now tht I'm back at office...Assuming "Year" is A1 I can use the below

IF(RAND()<=B2,A2,IF(RAND()<=B3,A3,IF(RAND()<=B4,A4,IF(RAND()<=B5,A5,0))))

Now I need to figure out how to monte carlo this thing with randomized start years (ie formula would start at year 2 or 4 depending on data).

 

[shg]

There's no need for Monte Carlo, is there?

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

Year​

[/td][td="bgcolor:#F3F3F3"]

Succ​

[/td][td="bgcolor:#F3F3F3"]

P(S)​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td][/td][td][/td][td][/td][td]Leave row blank[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

1​

[/td][td]

5%​

[/td][td="bgcolor:#E5E5E5"]

5.0%​

[/td][td]C3: {=PRODUCT(1 - B2:B$2) * B3}[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

2​

[/td][td]

15%​

[/td][td="bgcolor:#E5E5E5"]

14.3%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

3​

[/td][td]

25%​

[/td][td="bgcolor:#E5E5E5"]

20.2%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]

4​

[/td][td]

25%​

[/td][td="bgcolor:#E5E5E5"]

15.1%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]

>4​

[/td][td][/td][td="bgcolor:#E5E5E5"]

45.4%​

[/td][td]C7: =1-SUM(C3:C6)[/td][/tr]
[/table]