Probability / Permutation matrix / Difficult

[luzbecks]

Hi everyone,

I have a list of - say 5 events - all independents.

Each event has 2 outcomes: failure or sucess with two gains possible : GainFailure and Gainsuccess

I want to get a table giving me the gain for each scenario with the associated probability and then chart it.

EXAMPLE: (with two events):

Event 1: sucess proba = 50%, Gainsucess=2, Gain failure = 0
Event 2: sucess proba = 10%, Gain sucess = 5, Gain failure = 1

Then possible scenarios are

Success 1 / Success 2. Proba = 50%*10%=5%. Gain = 2 + 10 = 12
Success 1 / Failure 2. Proba = 50%*90% = 45% gain = 2+1 = 3
Failure 1 / Sucess 2. Proba = 50%*10% = 5% Gain = 0 + 10 = 10
Failure 1/ Failure 2. Proba = 50% * 90% = 45% Gain = 0 + 1 = 1

Summary:
45% of winning 1
45% of winning 3
5% of winning 10
5% of winning 12

Then I chart this output as follows:

0-45% : 1
45%-90%: 3
90-95%: 10
95%-100%: 12

Conclusion:
2^n scenarios in case n events.

My idea:
Create a table (dimension: n columns, 2^n rows) with all permutations of 0 and 1 amongst n columns.
Then:
1. multiply the table by the gain vector and then sum the rows
2. multiply the table by the probability vector then sum the rows

Problem: I dont know how to generate the table !!!! (I'm bad at VBA..) So thanks a lot if anybody can help !

Tip: For people familiar with binary numbers generating the table is like counting from 0 to 2^n -1 in binar but using one figure per cell (000,001, 010, 011, 100, 101, 110, 111)

Ps: I have an excel file which explains what I wanna do but I'm new so i dont know how to attach it to this post...

 

[luzbecks]

ok found the solution:

Use converter from decimal to binary to generate all possibilities:
DEC2BIN(33)=100001

then count the number of characters in your binary figure:

len(100001)=6

then extract the figure in the appropriate cell by using a the mid function (combined with a If function)<COLUMN p number,0,mid(100001,column number,1))<>


Note: if excel could convert in any base (base 3, base 4, etc...) you could actually generate the permutations for more complicated cases (if you more than two possibilities for each event for instance)

<H$3,0,MID($E7,H$3,1))<H$3,0,MID($E7,H$3,1))< p>