Probabilities for Occurance of Independent Events

[rupophobe]

Greetings; I spent some time searching but did not seem to find the answer I'm looking for, so hopefully this is not a repeat question. Nevertheless, I feel like this should have a simple answer; I just don't know what it is.

Say I have a finite list of multiple independent events (no real limit on the number of such, but for the example I'll use 20), and each event has a known probability of occurrence. While I don't need the odds of every possible permutation of events, I am interested in calculating the odds for the number of events which occur.

Perhaps a clarifying example: if the each event represents a game played, and each game has a known win probability, what are the odds of winning 0 games, 1 game, 2 games, ... 20 games? Obviously the calculation is easy for 0 or 20 games, but there are too many permutations for a brute force approach to the other 19 possibilities.

Any assistance would be greatly appreciated. Thanks!

 

[Eric W]

Welcome to the forum.

This is really more of a probability question than an Excel question, but there is a formula to answer that. If the probability of winning a game is p, and you want to know the probability of winning exactly k games out of n games, the formula is:

C(n,k) * p^k * (1-p)^(n-k)

If A1 = p, A2 = n, and A3 = k, the Excel formula is:

=COMBIN(A2,A3)*A1^A3*(1-A1)^(A2-A3)

 

[rupophobe]

Thank you for the reply, but I'm afraid it doesn't solve my problem. It would seem that I was insufficiently clear that the probability of each 'game' is different; please see the example below:

 

[rupophobe]

Just to follow up with additional clarification. I certainly know I could set up matrices and calculate the probability of each permutation and then sum them up, but there are for the above example, 184,756 different ways to win exactly 10 games (and 167,960 ways to win exactly 9 or 11 games, etc.). Is there a way to automate this in Excel?

 

[AhoyNC]

You might try your question on one of the statistic forums to see if anyone has an Excel solution. You can just use a google search to find a forum.
Here is a link to a problem close to yours, but they use different statistical software such as R (which is free, but has a steep learning curve) to solve the problem.

https://stats.stackexchange.com/questions/9510/probability-distribution-for-different-probabilities

 

[rupophobe]

AhoyNC - thanks for the suggestion; I was able to get satisfactory solution on a stats forum. Appreciate the help.

 

[AhoyNC]

Glad you found a solution. Was it an Excel solution? If so how about posting a link to the answer.

 

[Eric W]

There's a nagging suspicion in my head that there might be a mathematical solution to this, but it would take me a lot of research to get there. So I reverted to a brute force approach, where I compute each permutation, calculate the odds of it occurring, then add that to the appropriate bucket. Given a sheet layout like you showed:

Book1 (version 1).xlsb
	A	B	C	D	E	F
1						Probability
2	Game	Win	Lose			0.00003174290%
3	1	50.00%	50.00%			0.00116800144%
4	2	33.00%	67.00%			0.01792542343%
5	3	65.50%	34.50%			0.15358108858%
6	4	24.00%	76.00%			0.82913849909%
7	5	37.50%	62.50%			3.01914675766%
8	6	78.00%	22.00%			7.74935403275%
9	7	63.00%	37.00%			14.45259862629%
10	8	87.00%	13.00%			20.00154452920%
11	9	13.10%	86.90%			20.83138266459%
12	10	5.50%	94.50%			16.46326030695%
13	11	92.00%	8.00%			9.90463737991%
14	12	46.00%	54.00%			4.52792721322%
15	13	83.00%	17.00%			1.56165822225%
16	14	25.60%	74.40%			0.40090037550%
17	15	51.00%	49.00%			0.07495688568%
18	16	23.00%	77.00%			0.00987088570%
19	17	13.70%	86.30%			0.00086903688%
20	18	42.00%	58.00%			0.00004697705%
21	19	2.30%	97.70%			0.00000133673%
22	20	34.00%	66.00%			0.00000001422%
23
24						100.00000000000%
Sheet20

Cell Formulas
Range	Formula
F24	F24	=SUM(F2:F22)

I used this macro to do the computation:

Sub CountEm()
Dim i As Long, j As Long, str1 As String, wk As Double
Dim probs As Variant, outprobs(0 To 20, 1 To 1) As Double, ctr As Long
   
    probs = Range("B3:C22").Value
   
    For i = 0 To 2 ^ 20 - 1
        ctr = 0
        str1 = WorksheetFunction.Base(i, 2, 20)
        wk = 1
        ctr = 0
        For j = 1 To 20
            If Mid(str1, j, 1) = "1" Then
                wk = wk * probs(j, 1)
                ctr = ctr + 1
            Else
                wk = wk * probs(j, 2)
            End If
        Next j
        outprobs(ctr, 1) = outprobs(ctr, 1) + wk
    Next i
   
    Range("F2:F22").Value = outprobs

End Sub

It took just over 10 seconds on my PC. As a sanity check, the first and last values match your calculated totals, and the sum of all the percentages is 1, as you'd expect. Also, if you plot the percentages, the curve looks roughly normal.

Hope this works for you.

 

[rupophobe]

I was referred to the thread below, which presented a workable solution. Not quite as automated as I was hoping for, but one which simplifies the calculation enough that setting up the matrices is easily doable.

I've tried testing Eric W's macro, but my machine does not seem to respond to it very well.

http://www.talkstats.com/showthread...omial-Distribution-for-Variable-Probabilities

 

[Eric W]

What version of Excel do you have? I just realized that the Base function I used was introduced in Excel 2013. If you have an earlier version, that could explain why the macro didn't work for you. If you're still interested in this, I'll write up a version that doesn't use Base.