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]

This version doesn't use Base. It even runs quicker, only about 3 seconds.

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
Dim ix(20) As Byte
    
    probs = Range("B3:C22").Value
    
    For i = 0 To 2 ^ 20 - 1
        ctr = 0
        wk = 1
        ctr = 0
        For j = 1 To 20
            If ix(j) = 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
        
        For j = 1 To 20
            ix(j) = ix(j) + 1
            If ix(j) = 1 Then Exit For
            ix(j) = 0
        Next j
        
    Next i
    
    Range("F2:F22").Value = outprobs

End Sub

 

[dbelliott]

Thank you for this helpful macro. I have confirmed that it works perfectly on my spreadsheet. Can you please explain how this macro directs thousands of multiplication functions and then the addition of those products to populate Row F?
What does "x" represent? I am trying to understand when ix(j) = 1.
Ultimately, I'd like to convert this VBA to GAS for use in sheets. Thank you in advance for any help.

 

[Eric W]

This is a brute force approach, meaning that it examines every possible combination of 20 games, figures out the probability of that combination happening, and adds that probability to a bucket for the number of wins.

I represent a win by a 1, and a loss by a 0. So this: 10101010101010101010 would represent 10 wins and 10 losses. So we need to figure out every combination of 20 1's and 0's. I don't know how much you know about binary, but that's equivalent to counting from 0 to 2^20-1, hence the size of the For i= loop.

The ix array holds each of those binary digits, and it's initialized to all 0s. (ix is my shorthand for index.)

The first "For j" loop looks at each bit, and if it's a 1 multiplies the running percent by the probability of a win for that game. If it's a 0, it multiplies by the probability of a loss for that game. At the end of checking all 20 bits, we know the probability of that exact combination of games. And we add it to the bucket for the number of wins we have.

The second "For j" loop is responsible for incrementing the indexes. It essentially is just counting in binary. It starts by adding 1 to the leftmost bit. The only options for that bit are 0 and 1, so the results of adding 1 can be 1 or 2. If it's 1, we're ok, and we can just exit the loop (the "If ix(j) = 1 Then Exit For" line). If it's 2, that means we need to set that bit to 0, and carry the 1 to the next bit, which we do by continuing in the loop. Think of that loop as an odometer, only changing as many digits as necessary.

So that's the gist of it. Hope this helps!

 

[dbelliott]

Eric,
Thanks so much for this explanation! I've never written a loop of loops, so seeing this is awesome. Now that I know that "ix" is an array, it lends itself to being defined as a variable in GAS: var ix = Array(20).fill(0).
Thanks again!
Dustin