Binomial help - P is different for each trial

[tb440167]

Hi,
Bit of a noob with VBA and a bit limited with it at the moment.

I am trying to build a calc that runs a binomial calculation based on each individual trial having a probability that CAN be different from any other.

I have a table as below for 5

Then outputs the following

My current method is like this, but I intend to go up to 40/50 selections and it's laborious to code, and I think excel is starting to struggle to compute it fast enough and certainly will at the upper end.

Function TrialsPermProb(TrialsWonCount As Integer, eventCount As Integer) As Double


Dim TrialsWon As Integer
Dim TrialsProb As Double
Dim sumPermProb As Double
Dim eventNo As Integer
Dim eventName As String
Dim outcome As Integer
Dim e1, e2, e3, e4, e5, e6, e7, e8, e9, e10, e11, e12, e13, e14, e15, e16 As Integer
ReDim TrialsPermProb2(0 To eventCount) As Double
Dim i As Integer
Dim TrialsCount As Integer
Dim market As Integer
ReDim probArr(1 To eventCount, 1 To 2) As Double


For i = 1 To eventCount
    For outcome = 1 To 2
        probArr(i, 1) = Range("probYesRange").Cells(i, 1).Value
        probArr(i, 2) = 1 - Range("probYesRange").Cells(i, 1).Value
    Next outcome
Next i


If eventCount = 1 Then
    For e1 = 1 To 2
        If e1 = 1 Then
            TrialsCount = TrialsCount + 1
            TrialsProb = probArr(1, 1)
        Else
            TrialsProb = probArr(1, 2)
        End If
        TrialsPermProb2(TrialsCount) = TrialsPermProb2(TrialsCount) + TrialsProb
        TrialsProb = 0
        TrialsCount = 0
    Next e1
    TrialsPermProb = TrialsPermProb2(TrialsWonCount)
    
ElseIf eventCount = 2 Then
    For e1 = 1 To 2
        For e2 = 1 To 2
            If e1 = 1 Then
                TrialsCount = TrialsCount + 1
                TrialsProb = probArr(1, 1)
            Else
                TrialsProb = probArr(1, 2)
            End If
            If e2 = 1 Then
                TrialsCount = TrialsCount + 1
                TrialsProb = TrialsProb * probArr(2, 1)
            Else
                TrialsProb = TrialsProb * probArr(2, 2)
            End If
            TrialsPermProb2(TrialsCount) = TrialsPermProb2(TrialsCount) + TrialsProb
            TrialsProb = 0
            TrialsCount = 0
        Next e2
    Next e1
    TrialsPermProb = TrialsPermProb2(TrialsWonCount)

ElseIf eventCount = 3 Then............... and so on.

Any helping hand would be much appreciated

 

[pgc01]

Hi
Welcome to the board

Are you sure this is possible?
What do your calculations say for the case you posted of the 40/50 elements?

If I understand correctly for 50 elements the probability of 25 successes would be the probability of all the possible combinations of 25 elements.
This means for each of the 126,410,606,437,752 combinations to multiply the 25 probabilities of success by the 25 complementary probabilities of failure and then add all.
Makes no sense to me.

I don't think this is possible, but I may be thinking something wrong.
This is the type of thing that would need some kind of approach other than brute force.
Please comment.

 

[micsza]

Could you please elaborate with more details on what this calculator is supposed to do? Ideally with some math involved ...

 

[pgc01]

Could you please elaborate with more details on what this calculator is supposed to do? Ideally with some math involved ...

Hi micsza

If I understand correctly, it calculates the Poisson binomial distribution.

 

[micsza]

Hi micsza

If I understand correctly, it calculates the Poisson binomial distribution.

Hi pgc01,

thanks for clarity. I agree with you that brute force is impossible here. Generating the set of all subsets alone is non-trivial algorithmically and memory/time complexity for n = 50 is no go - not to mention numerical issues. There might be some smart way or workaround (I see Wiki mentioning some recursive formula but not numerically stable for n > 20) but I can't help here.

Best,
micsza

 

[tb440167]

Hi, apologies for being unclear.
the bottom chart shows the probability of exactly X trials being successful via a binomial distribution.

 

[micsza]

Hi, no problem - I've got your point. The issue with your approach is that you try to follow the Poisson distribution formula explicitly and this way if you have n events and want to compute the probability of k successes among them you need to iterate through all k-element subsets of n-element set of events. This is exponential time algorithm and for your target n=50 not practically possible - you need the other way round. I think you may find this helpful: http://www3.stat.sinica.edu.tw/statistica/oldpdf/A7n44.pdf. It should be easy to follow given you have some basic discrete math knwoledge. In section 2 you have two recursive formulas, method 1 runs in O(n^2) time - voila! This is exactly what you need, as this gonna be quick enough. Bear in mind, there still might be numerical issues but I assume that's minor concern.

Having a look at your code above, I think code-wise this project may be a small challenge for you but looks like an inspiring fun. Good luck!