Calculating the probability of a specific number of successful results out of a number of attempts, each with independent odds

Solution 1:

As you correctly identify there are 924 possibilities that result in 6 successes and 6 failures. Had the probabilities of success for each scenario been identical then you could have used the Binomial distribution (function BINOM.DIST) to calculate your required probability values. (Incidentally, your use of the term "odds" is incorrect. Technically, the odds of an event happening is the ratio of the probability that the event happens to the probability that it does not happen.)

Unfortunately, the fact that each of your scenarios has a different probability of success means that there is no easy way to calculate your probabilities using a built-in function. However, it is not too difficult to calculate your required values using Excel.

Possibly the simplest way is to recognise that there are 4096 distinct possibilities, ranging all 12 scenarios being failures to all 12 being successes. The 4096 arises because each of the 12 scenarios can be a success or a failure meaning that there are 2^12 = 4096 distinct outcomes possible. These 4096 possibilities can be represented by the integers from 0 to 4095 and each integer can be represented as a 12 digit binary number, with the 12 0's and 1's representing failure and success respectively of the 12 scenarios. The first challenge, therefore, is to find a way of converting each integer to a set of 12 values of 0's and 1's. Any integer (say I1) in the range 0 to 4095 can be converted to 12 0/1 values as follows

• Calculate the first 0/1 value as MOD(I1,2), call result B1
• Calculate a new integer (say I2) as (I1 - B1)/2
• Calculate the second 0/1 value as MOD(I2, 2) and call this result B2
• Calculate a new integer (say I3) as (I2-B2)/2 ... (continue these pairs of calculation steps)
• Calculate a new integer (say I12) as (I11-B11)/2
• Calculate the twelfth 0/1 value as MOD(I12/2), call result B12

For each integer I1 (taking possible values between 0 and 4095), the 12 values B1, B2,...,B12 represent the binary digits of I1 in reverse order.

It is fairly straightforward to represent this as a rectangular array of numbers in Excel with 4096 rows and 24 columns. The rows represent the integers 0 to 4095, the first 12 columns represent the values I1 to I12 and the second 12 the 0/1 values of B1 to B12, with 1's representing success and 0's representing failure.

A 25th column can be added with the value in each row calculated as the sum of the 12 0/1 values B1,...,B12 in that row. This 25th column simply identifies the number of "successes" corresponding to the row.

A further 12 columns (columns 26 to 37) can now be added representing the probabilities of each scenarios outcome. If Pi and Qi are the probabilities of success and failure for the i'th scenario then the probability value (say Vi) is simply (Pi×Bi)+(Qi×(1-Bi)). A 38th column can now be calculated for each row as the product (function PRODUCT) of the 12 values V1, V2, ..., V12 in that row. This calculated value represents the probability of the particular pattern of successes and failures arising.

The final part is simply to calculate the required probabilities of n successes (and 12-n failures) where n=0,1,2,...,12. This is simply the sum of the values in column 38 for those rows where column 25 has a value of n (function SUMIF or SUMIFS).

If I have entered the probability data correctly into my workbook, I calculate that to 5 significant figures the probability of 6 successes (the original question) is 0.23788.