Permutations With Probabilities

**stevenightingale** · 09-25-2012, 12:08 PM

Hi everyone,

I am relatively new to complex Excel modelling as a heads up, but I have a pretty in depth question and this seemed like a great resource to use. My desire is to create a permutation of a bunch of binary events, each with their own distinct probability of A or B, and then calculate the probability of that specific permutation. For example:

Event 1
A: 75%
B: 25%

Event 2
A: 10%
B: 90%

Event 3
A: 40%
B: 60%

Possibilites & Probability of that event occuring
ABA (27%)
ABB (40.5%)
AAA (3%)
AAB (4.5%)
BBA (9%)
BBB (13.5%)
BAA (1%)
BAB (1.5%)

I want to do this for 15 events, and am looking for a way to do 15-16 events, each with their own distinct probability. Basically what I am envisioning is creating some sort of huge list of all the possible permutations, and then having a table where I could input the various probabilities and have all of the probabilites spit out. I don't think this is too complicated, but any help would be appreciated!

Thanks,
Steve

**shg** · 09-25-2012, 12:46 PM

Please Login or Register  to view this content.

In B3 and copy right and down:

=IF(MOD(INT(2*(ROWS($B$3:B3) - 1) / 2 ^ COLUMNS($B$3:B3)), 2), "B","A")

In A3, confirmed with Ctrl+Shift+Enter instead of just Enter, then copied down:

=PRODUCT((B3:D3="A")*$B$1:$D$1 + (B3:D3<>"A")*(1-$B$1:$D$1))

**david.herrera1** · 12-06-2012, 02:28 AM

shg

Thanks for this solution. I have been working on this problem for about two weeks. I needed to generate a 2^10 two factor permutation table and was at a complete loss as to how to do it.

Thanks Again