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?

	A​	B​	C​	D​
1​	Year​	Succ​	P(S)​	​
2​				Leave row blank
3​	1​	5%​	5.0%​	C3: {=PRODUCT(1 - B2:B$2) * B3}
4​	2​	15%​	14.3%​
5​	3​	25%​	20.2%​
6​	4​	25%​	15.1%​
7​	>4​		45.4%​	C7: =1-SUM(C3:C6)