# Thread: Probability Sequence Until Success Thanks: 0 Likes: 0

1. ## Probability Sequence Until Success

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.

2. ## Re: Probability Sequence Until Success

Originally Posted by 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).

3. ## Re: Probability Sequence Until Success

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)