Probability Sequence Until Success
Results 1 to 3 of 3

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

  1. #1
    New Member
    Join Date
    May 2016
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Cool 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. #2
    New Member
    Join Date
    May 2016
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Probability Sequence Until Success

    Quote Originally Posted by malcolmrdj View Post
    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. #3
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    21,602
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    14 Thread(s)

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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •