+ Reply to Thread
Results 1 to 23 of 23

Formula for at least 3 of 5 outcomes happening

  1. #1
    Valued Forum Contributor
    Join Date
    03-03-2009
    Location
    UK
    MS-Off Ver
    MS365 Subscription Excel for Mac
    Posts
    1,017

    Formula for at least 3 of 5 outcomes happening

    Can't get my head around this at all but I have 5 matchups and lets say the probabilities of each happening are:

    Matchup 1 = 40%
    Matchup 2 = 70%
    Matchup 3 = 50%
    Matchup 4 = 55%
    Matchup 5 = 72%

    Need a formula that will tell me what is the percentage chance that at least 3 of those matchups will occur. Any help would be amazing!

  2. #2
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Formula for at least 3 of 5 outcomes happening

    Assuming these are independent probabilities ... would it not be the product of the three largest % ?

    So, if above % were in A1:A5 the greatest probability of at least 3 happening would be:

    =PRODUCT(LARGE(A1:A5,{1,2,3})) -> ~28%

    I suspect you might want something a little more sophisticated... if so, post back -- Mr Shorty, and the like, would no doubt have a better / more robust answer for you.

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Formula for at least 3 of 5 outcomes happening

    Is't the answer not one number, but a range? From the lowest:

    =PRODUCT(SMALL(A1:A5,{1,2,3}))

    to the highest - as given by XLent? I think we need to know more about what you're trying to do...
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  4. #4
    Valued Forum Contributor
    Join Date
    03-03-2009
    Location
    UK
    MS-Off Ver
    MS365 Subscription Excel for Mac
    Posts
    1,017

    Re: Formula for at least 3 of 5 outcomes happening

    Yeah I want the % chance that at least 3 of the 5 matchups happen.

    Thanks for both replies so far but the answers given give me the highest percentage chance along with the range. It doesn't answer the question of what % chance that at least 3 of the 5 happen.

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Formula for at least 3 of 5 outcomes happening

    How will you know if a matchup could happen???

    I'd need my crystal ball for this.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  6. #6
    Valued Forum Contributor
    Join Date
    03-03-2009
    Location
    UK
    MS-Off Ver
    MS365 Subscription Excel for Mac
    Posts
    1,017

    Re: Formula for at least 3 of 5 outcomes happening

    It states it in the original post. They are all percentage chances of happening. Matchup 1 has a 40% chance of happening for example.

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Formula for at least 3 of 5 outcomes happening

    I'd say this, then:

    =AVERAGE(LARGE(A1:A5,{1,2,3}))

  8. #8
    Valued Forum Contributor
    Join Date
    03-03-2009
    Location
    UK
    MS-Off Ver
    MS365 Subscription Excel for Mac
    Posts
    1,017

    Re: Formula for at least 3 of 5 outcomes happening

    Thanks but it won't be that because for example if I change the percentages to say

    29%
    37%
    0%
    0%
    0%

    then that formula returns 22% chance which is obviously wrong as the correct answer at this point would be 0%.

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Formula for at least 3 of 5 outcomes happening

    OK - you see this is what I mean in post #5. You are now drip-feeding criteria that you understand but haven't told us.

    Before I try again, what else haven't you told us?

    Try this:

    =IF(COUNTIF(B5:B9,0)>=3,0,AVERAGE(LARGE(B5:B9,{1,2,3})))

  10. #10
    Valued Forum Contributor
    Join Date
    03-03-2009
    Location
    UK
    MS-Off Ver
    MS365 Subscription Excel for Mac
    Posts
    1,017

    Re: Formula for at least 3 of 5 outcomes happening

    Sorry for the confusion!

    So I have 5 potential outcomes and the probabilities for each outcome can be different. I need to find the percentage chance of AT LEAST 3 of the 5 outcomes happening based on the percentage chance of each individual outcome occurring.

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Formula for at least 3 of 5 outcomes happening

    Try this:

    =IF(COUNTIF(B5:B9,0)>=3,0,AVERAGE(LARGE(B5:B9,{1,2,3})))

  12. #12
    Valued Forum Contributor
    Join Date
    03-03-2009
    Location
    UK
    MS-Off Ver
    MS365 Subscription Excel for Mac
    Posts
    1,017

    Re: Formula for at least 3 of 5 outcomes happening

    I don't think that's the answer. Looking for a binomial distribution to solve it.

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Formula for at least 3 of 5 outcomes happening

    OK, so what do you expect the answer to be?

    Once more, you are drip-feeding information: this is the first time you have mentioned binomial distributions, which ought to be in the thread title.

    What else?

    You are in danger of wasting even more time (ours AND yours) if you don't tell us everything we need to know to be able to provide you with a formula that will work for you.

    Maybe look at this: https://support.office.com/en-us/art...2-509a1480770c

  14. #14
    Valued Forum Contributor
    Join Date
    03-03-2009
    Location
    UK
    MS-Off Ver
    MS365 Subscription Excel for Mac
    Posts
    1,017

    Re: Formula for at least 3 of 5 outcomes happening

    I'm not drip feeding. I just expected the answer to be a formula that was a binomial dist as this is what's required to solve this but using binomial dist in excel is beyond me.

    Let's say all 5 probabilties are 10%,11%,12%,13% and 14%.

    So it'd be 5 Choose 3 all listed out.
    Then from that list you'd multiply the 3 choices each time.
    Then you'd add the probablities together. That would be the answer.

  15. #15
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Formula for at least 3 of 5 outcomes happening

    I just expected the answer to be a formula that was a binomial dist as this is what's required to solve this
    Yes, I know, but read the title of your thread and the opening post: nowhere did you tell us this.

    You always need to disclose everything so that we can provide you with a formula. That means telling us HOW you expect it might be approached.

    The MOST IMPORTANT THING is to provide expected outcomes. With the greatest of respect, this gets me no further forward:

    So it'd be 5 Choose 3 all listed out.
    Then from that list you'd multiply the 3 choices each time.
    Then you'd add the probablities together. That would be the answer.
    I'm out, but I'll put out a call for help to the more mathematically-minded.

  16. #16
    Valued Forum Contributor
    Join Date
    03-03-2009
    Location
    UK
    MS-Off Ver
    MS365 Subscription Excel for Mac
    Posts
    1,017

    Re: Formula for at least 3 of 5 outcomes happening

    Thanks Appreciated

  17. #17
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,803

    Re: Formula for at least 3 of 5 outcomes happening

    The answer to this is NOT knowing EXCEL, but knowing Statistics and Probability. The first question is how would you determine this WITHOUT EXCEL (what's the arithmetic equation to use). I'm not a Probability expert so I don't know. But once this is known, THEN the EXCEL formula will probably be easy.

  18. #18
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Formula for at least 3 of 5 outcomes happening

    Based on what you said in Post 14... is this what you expect the manual calculation procedure to be?

    I'm a scientist... but not a statistician. I use statistics like a drunk man uses a lamp post. For support, not illumination.

    I assumed, by the way, that you wanted combinations, rather than permutations (I assumed that the ORDER of the occurrence of Things 1, 2, 3, 4 & 5 were unimportant). I still think that you need to tell us what you are trying to do in concrete terms, as suggested wayyy back at Post 3.
    Attached Files Attached Files

  19. #19
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Formula for at least 3 of 5 outcomes happening

    There are 32 possible outcomes. Assuming they are independent, you can tally the probability of each and sum the threes:

    A
    B
    C
    D
    E
    F
    G
    H
    I
    1
    Event1
    Event2
    Event3
    Event4
    Event5
    Prob
    Num
    2
    40%
    70%
    50%
    55%
    72%
    3
    4
    0
    0
    0
    0
    0
    1.13%
    0
    F4: {=PRODUCT(IF(A4:E4 = 0, 1 - $A$2:$E$2, $A$2:$E$2))}
    5
    0
    0
    0
    0
    1
    2.92%
    1
    G4: =SUM(A4:E4)
    6
    0
    0
    0
    1
    0
    1.39%
    1
    7
    0
    0
    0
    1
    1
    3.56%
    2
    8
    0
    0
    1
    0
    0
    1.13%
    1
    9
    0
    0
    1
    0
    1
    2.92%
    2
    10
    0
    0
    1
    1
    0
    1.39%
    2
    11
    0
    0
    1
    1
    1
    3.56%
    3
    12
    0
    1
    0
    0
    0
    2.65%
    1
    13
    0
    1
    0
    0
    1
    6.80%
    2
    14
    0
    1
    0
    1
    0
    3.23%
    2
    15
    0
    1
    0
    1
    1
    8.32%
    3
    16
    0
    1
    1
    0
    0
    2.65%
    2
    17
    0
    1
    1
    0
    1
    6.80%
    3
    18
    0
    1
    1
    1
    0
    3.23%
    3
    19
    0
    1
    1
    1
    1
    8.32%
    4
    20
    1
    0
    0
    0
    0
    0.76%
    1
    21
    1
    0
    0
    0
    1
    1.94%
    2
    22
    1
    0
    0
    1
    0
    0.92%
    2
    23
    1
    0
    0
    1
    1
    2.38%
    3
    24
    1
    0
    1
    0
    0
    0.76%
    2
    25
    1
    0
    1
    0
    1
    1.94%
    3
    26
    1
    0
    1
    1
    0
    0.92%
    3
    27
    1
    0
    1
    1
    1
    2.38%
    4
    28
    1
    1
    0
    0
    0
    1.76%
    2
    29
    1
    1
    0
    0
    1
    4.54%
    3
    30
    1
    1
    0
    1
    0
    2.16%
    3
    31
    1
    1
    0
    1
    1
    5.54%
    4
    32
    1
    1
    1
    0
    0
    1.76%
    3
    33
    1
    1
    1
    0
    1
    4.54%
    4
    34
    1
    1
    1
    1
    0
    2.16%
    4
    35
    1
    1
    1
    1
    1
    5.54%
    5
    36
    100.00%
    37
    38
    0
    1.13%
    H38: =SUMIF($G$4:$G$35, G38, $F$4:$F$35)
    39
    1
    8.84%
    40
    2
    25.94%
    41
    3
    35.62%
    << your answer
    42
    4
    22.93%
    43
    5
    5.54%
    44
    100.00%
    Last edited by shg; 08-10-2019 at 02:08 PM.
    Entia non sunt multiplicanda sine necessitate

  20. #20
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Formula for at least 3 of 5 outcomes happening

    If you want "or more", change the formula to

    =SUMIF($G$4:$G$35, ">=" & G38, $F$4:$F$35)

  21. #21
    Valued Forum Contributor
    Join Date
    03-03-2009
    Location
    UK
    MS-Off Ver
    MS365 Subscription Excel for Mac
    Posts
    1,017

    Re: Formula for at least 3 of 5 outcomes happening

    Can't make myself any clearer tbh.

    The above answer is CORRECT shg. It's 64.09% chance of at least 3 of the 5 occurring.

    There are 16 possible outcomes:
    (10 outcomes with exactly 3 of 5 which is 5C3)
    (5 outcomes with exactly 4 of 5 which is 5C4)
    (1 outcome with all 5)

    Once you do the probabilities of those 3 scenarios for each possible outcome you add them all up and that is at least 3 of 5 and is 64.09%.

    I was just wondering whether a simple excel formula existed for such thing. I ended up just writing it all down the long way and working it out based on that.

    Thanks for your efforts anyway!
    Last edited by ScabbyDog; 08-11-2019 at 06:37 AM.

  22. #22
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Formula for at least 3 of 5 outcomes happening

    Shg's answer is correct, save for a slight misinterpretation corrected in post #20.

    Look at the last 3 results, bottom right of the table in post #19. 35.62 + 22.93 + 5.54 = 64.09

    Changing the formula to the one in post #20 will give you the 'correct' result.

    Naturally, it relies on the table to generate the result, it might be possible to do the whole thing with a single formula, but it would be anything but simple.

  23. #23
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Formula for at least 3 of 5 outcomes happening

    Quote Originally Posted by ScabbyDog View Post
    I was just wondering whether a simple excel formula existed for such thing
    One does not.

    You could create a user-defined function with that functionality.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Same formula 2 different outcomes
    By OGAngryHulk in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-04-2018, 10:24 AM
  2. [SOLVED] I need a logical formula with 3 outcomes
    By Charken in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 04-27-2018, 01:23 PM
  3. If or formula help for 3 possible outcomes
    By pembr0ke in forum Excel General
    Replies: 7
    Last Post: 02-08-2016, 07:41 AM
  4. [SOLVED] IF Formula with 3 outcomes
    By adamheon in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-08-2013, 10:13 AM
  5. Formula that presents three outcomes
    By KENNYS in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-11-2013, 03:54 PM
  6. [SOLVED] Formula for Event happening every X Years
    By Rozay in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-03-2013, 05:17 PM
  7. If formula used for multiple outcomes
    By Scottiexcel in forum Excel General
    Replies: 1
    Last Post: 07-14-2009, 10:58 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1