# Function to return combinations of n independent event occurrences and their probabilities

#### NunoAMFF

##### New Member
Hi, everyone.

I’ve got n independent events, each of them with a known probability, and I was wondering if EXCEL, given this input, could return a table with all possible combinations of event occurrences and their respective probability.

This is pretty straightforward to do up to a limited number of events (actually, it can be done easily up to any number of events, following a simple procedure), but I would rather use a function, provided there’s one available.

Here’s the simplest example of what I’m looking for:

INPUT:

Event A: probability 30%

Event B: probability: 60%

OUTPUT:

A and B: 18%

A and NOT B: 12%

NOT A and B: 42%

NOT A and NOT B: 28%

Do you know if anything similar exists in EXCEL?

Regards,

Nuno

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

#### lrobbo314

##### Well-known Member
Using Power Query

Sunrise Sunset (version 1).xlsb
ABCDEFGHIJKLMNO
1EventProbabilityAProbability ABProbability BNot ANot BAandBAorBAorBnotBothnAnBAnotBBnotA
2A30%A30%B60%70%40%18%72%54%28%12%42%
3B60%A30%C20%70%80%6%44%38%56%24%14%
4C20%A30%D45%70%55%14%62%48%39%17%32%
5D45%A30%E15%70%85%5%41%36%60%26%11%
6E15%A30%F70%70%30%21%79%58%21%9%49%
7F70%B60%A30%40%70%18%72%54%28%42%12%
8B60%C20%40%80%12%68%56%32%48%8%
9B60%D45%40%55%27%78%51%22%33%18%
10B60%E15%40%85%9%66%57%34%51%6%
11B60%F70%40%30%42%88%46%12%18%28%
12C20%A30%80%70%6%44%38%56%14%24%
13C20%B60%80%40%12%68%56%32%8%48%
14C20%D45%80%55%9%56%47%44%11%36%
15C20%E15%80%85%3%32%29%68%17%12%
16C20%F70%80%30%14%76%62%24%6%56%
17D45%A30%55%70%14%62%48%39%32%17%
18D45%B60%55%40%27%78%51%22%18%33%
19D45%C20%55%80%9%56%47%44%36%11%
20D45%E15%55%85%7%53%47%47%38%8%
21D45%F70%55%30%32%84%52%17%14%39%
22E15%A30%85%70%5%41%36%60%11%26%
23E15%B60%85%40%9%66%57%34%6%51%
24E15%C20%85%80%3%32%29%68%12%17%
25E15%D45%85%55%7%53%47%47%8%38%
26E15%F70%85%30%11%75%64%26%5%60%
27F70%A30%30%70%21%79%58%21%49%9%
28F70%B60%30%40%42%88%46%12%28%18%
29F70%C20%30%80%14%76%62%24%56%6%
30F70%D45%30%55%32%84%52%17%39%14%
31F70%E15%30%85%11%75%64%26%60%5%
Sheet2

Power Query:
``````let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Expand = Table.ExpandTableColumn(Merge, "Added Custom", {"Event", "Probability"}, {"Event.1", "Probability.1"}),
SR = Table.SelectRows(Expand, each [Event] <> [Event.1]),
Rename = Table.RenameColumns(RC,{{"Event", "A"}, {"Event.1", "B"}, {"Probability", "Probability A"}, {"Probability.1", "Probability B"}}),
NotA = Table.AddColumn(Rename, "Not A", each 1-[Probability A]),
NotB = Table.AddColumn(NotA, "Not B", each 1-[Probability B]),
AandB = Table.AddColumn(NotB, "AandB", each [Probability A]*[Probability B]),
AorB = Table.AddColumn(AandB, "AorB", each [Probability A]+[Probability B]-[AandB]),
ABnotBoth = Table.AddColumn(AorB, "AorBnotBoth", each [Probability A]+[Probability B]-2*[AandB]),
nAnB = Table.AddColumn(ABnotBoth, "nAnB", each 1-[AorB]),
AnotB = Table.AddColumn(nAnB, "AnotB", each [Probability A]*(1-[Probability B])),
BnotA = Table.AddColumn(AnotB, "BnotA", each (1-[Probability A])*[Probability B]),
Type = Table.TransformColumnTypes(BnotA,{{"Probability A", Percentage.Type}, {"Probability B", Percentage.Type}, {"Not A", Percentage.Type}, {"Not B", Percentage.Type}, {"AandB", Percentage.Type}, {"AorB", Percentage.Type}, {"AorBnotBoth", Percentage.Type}, {"nAnB", Percentage.Type}, {"AnotB", Percentage.Type}, {"BnotA", Percentage.Type}, {"B", type text}, {"A", type text}})
in
Type``````

#### lrobbo314

##### Well-known Member
Or using formulas.

Cell Formulas
RangeFormula
D2:D37D2=LET(e,A2:A7,r,COUNTA(e)^2,INDEX(e,INT(SEQUENCE(r,,0)/COUNTA(e)+1))&"-"&INDEX(e,MOD(SEQUENCE(r,,0),COUNTA(e))+1))
E2:E37E2=LET(e,B2:B7,r,COUNTA(e)^2,INDEX(e,INT(SEQUENCE(r,,0)/COUNTA(e)+1)))
F2:F37F2=LET(e,B2:B7,r,COUNTA(e)^2,INDEX(e,MOD(SEQUENCE(r,,0),COUNTA(e))+1))
G2:H37,L2:L37G2=1-E2
I2:I37I2=E2*F2
J2:J37J2=SUM(E2:F2)-I2
K2:K37K2=SUM(E2:F2)-2*I2
M2:M37M2=E2*(1-F2)
N2:N37N2=(1-E2)*F2
Dynamic array formulas.

#### NunoAMFF

##### New Member
Thank you so much, lrobbo. This works great.

Cheers.

#### lrobbo314

##### Well-known Member

No problemo. Happy to help.

#### lrobbo314

##### Well-known Member
Can also be done with a single cell formula.

MXL Groups (version 1).xlsb
ABCDEFGHIJKLMN
1EventProbComboProb AProb BP(A')P(B')P(A∩B)P(A∪B)P(AΔB)P((A∪B)')A Not BB Not A
2A30.0%A-A30.0%30.0%70.0%70.0%9.0%51.0%42.0%49.0%21.0%21.0%
3B60.0%A-B30.0%60.0%70.0%40.0%18.0%72.0%54.0%28.0%12.0%42.0%
4C20.0%A-C30.0%20.0%70.0%80.0%6.0%44.0%38.0%56.0%24.0%14.0%
5D45.0%A-D30.0%45.0%70.0%55.0%13.5%61.5%48.0%38.5%16.5%31.5%
6E15.0%A-E30.0%15.0%70.0%85.0%4.5%40.5%36.0%59.5%25.5%10.5%
7F70.0%A-F30.0%70.0%70.0%30.0%21.0%79.0%58.0%21.0%9.0%49.0%
8B-A60.0%30.0%40.0%70.0%18.0%72.0%54.0%28.0%42.0%12.0%
9B-B60.0%60.0%40.0%40.0%36.0%84.0%48.0%16.0%24.0%24.0%
10B-C60.0%20.0%40.0%80.0%12.0%68.0%56.0%32.0%48.0%8.0%
11B-D60.0%45.0%40.0%55.0%27.0%78.0%51.0%22.0%33.0%18.0%
12B-E60.0%15.0%40.0%85.0%9.0%66.0%57.0%34.0%51.0%6.0%
13B-F60.0%70.0%40.0%30.0%42.0%88.0%46.0%12.0%18.0%28.0%
14C-A20.0%30.0%80.0%70.0%6.0%44.0%38.0%56.0%14.0%24.0%
15C-B20.0%60.0%80.0%40.0%12.0%68.0%56.0%32.0%8.0%48.0%
16C-C20.0%20.0%80.0%80.0%4.0%36.0%32.0%64.0%16.0%16.0%
17C-D20.0%45.0%80.0%55.0%9.0%56.0%47.0%44.0%11.0%36.0%
18C-E20.0%15.0%80.0%85.0%3.0%32.0%29.0%68.0%17.0%12.0%
19C-F20.0%70.0%80.0%30.0%14.0%76.0%62.0%24.0%6.0%56.0%
20D-A45.0%30.0%55.0%70.0%13.5%61.5%48.0%38.5%31.5%16.5%
21D-B45.0%60.0%55.0%40.0%27.0%78.0%51.0%22.0%18.0%33.0%
22D-C45.0%20.0%55.0%80.0%9.0%56.0%47.0%44.0%36.0%11.0%
23D-D45.0%45.0%55.0%55.0%20.3%69.8%49.5%30.3%24.8%24.8%
24D-E45.0%15.0%55.0%85.0%6.8%53.3%46.5%46.8%38.3%8.3%
25D-F45.0%70.0%55.0%30.0%31.5%83.5%52.0%16.5%13.5%38.5%
26E-A15.0%30.0%85.0%70.0%4.5%40.5%36.0%59.5%10.5%25.5%
27E-B15.0%60.0%85.0%40.0%9.0%66.0%57.0%34.0%6.0%51.0%
28E-C15.0%20.0%85.0%80.0%3.0%32.0%29.0%68.0%12.0%17.0%
29E-D15.0%45.0%85.0%55.0%6.8%53.3%46.5%46.8%8.3%38.3%
30E-E15.0%15.0%85.0%85.0%2.3%27.8%25.5%72.3%12.8%12.8%
31E-F15.0%70.0%85.0%30.0%10.5%74.5%64.0%25.5%4.5%59.5%
32F-A70.0%30.0%30.0%70.0%21.0%79.0%58.0%21.0%49.0%9.0%
33F-B70.0%60.0%30.0%40.0%42.0%88.0%46.0%12.0%28.0%18.0%
34F-C70.0%20.0%30.0%80.0%14.0%76.0%62.0%24.0%56.0%6.0%
35F-D70.0%45.0%30.0%55.0%31.5%83.5%52.0%16.5%38.5%13.5%
36F-E70.0%15.0%30.0%85.0%10.5%74.5%64.0%25.5%59.5%4.5%
37F-F70.0%70.0%30.0%30.0%49.0%91.0%42.0%9.0%21.0%21.0%
Sheet9
Cell Formulas
RangeFormula
D2:N37D2=LET(e,A2:A7,p,B2:B7,r,COUNTA(e)^2,c,INDEX(e,INT(SEQUENCE(r,,0)/COUNTA(e)+1))&"-"&INDEX(e,MOD(SEQUENCE(r,,0),COUNTA(e))+1),pa,INDEX(p,INT(SEQUENCE(r,,0)/COUNTA(e)+1)),pb,INDEX(p,MOD(SEQUENCE(r,,0),COUNTA(e))+1),pna,1-pa,pnb,1-pb,ab,pa*pb,aob,pa+pb-ab,aabb,pa+pb-2*ab,nab,1-aob,anb,pa*(1-pb),bna,(1-pa)*pb,CHOOSE(SEQUENCE(,11),c,pa,pb,pna,pnb,ab,aob,aabb,nab,anb,bna))
Dynamic array formulas.

#### NunoAMFF

##### New Member

Hi, Irobbo.

Sorry to bother you again.

Instead of a 2 event combo, is there any way we can do the same for all possible combinations of n occurrences?

That is, for a 3 event scenario:

INPUT:

Event A: probability 30%

Event B: probability: 60%

Event C: probability: 10%

OUTPUT:

A and B and C: 1,8%

NOT A and B and C: 4,2%

A and NOT B and C: 1,2%

NOT A and NOT B and C: 2,8%

A and B and NOT C: 16,2%

NOT A and B and NOT C: 37,8%

A and NOT B and NOT C: 10,8%

NOT A and NOT B and not C: 25,2%

For a 4 event scenario:

INPUT:

Event A: probability 30%

Event B: probability: 60%

Event C: probability: 10%

Event D: probability: 50%

OUTPUT:

A and B and C and D: 0,9%

NOT A and B and C and D: 2,1%

A and NOT B and C and D: 0,6%

NOT A and NOT B and C and D: 1,4%

A and B and NOT C and D: 8,1%

NOT A and B and NOT C and D: 18,9%

A and NOT B and NOT C and D: 5,4%

NOT A and NOT B and not C and D: 12,6%

A and B and C and NOT D: 0,9%

NOT A and B and C and NOT D: 2,1%

A and NOT B and C and NOT D: 0,6%

NOT A and NOT B and C and NOT D: 1,4%

A and B and NOT C and NOT D: 8,1%

NOT A and B and NOT C and NOT D: 18,9%

A and NOT B and NOT C and NOT D: 5,4%

NOT A and NOT B and not C and NOT D: 12,6%

And so on, for any n event scenario...

And yes, I know I'm pushing my luck.

Again, thanks a lot for your help.

Regards,
Nuno

#### Habtest

##### Board Regular
If you don't mind some helper cells:

Test.xlsx
ABCDEFGHIJK
1Input EventInput Probability
2A30%Not A70%Event Total4
3B60%Not B40%
4C10%Not C90%Probability
5D50%Not D50%0.9%ABCD
60.9%ABCNot D
78.1%ABNot CD
88.1%ABNot CNot D
90.6%ANot BCD
100.6%ANot BCNot D
115.4%ANot BNot CD
125.4%ANot BNot CNot D
132.1%Not ABCD
142.1%Not ABCNot D
1518.9%Not ABNot CD
1618.9%Not ABNot CNot D
171.4%Not ANot BCD
181.4%Not ANot BCNot D
1912.6%Not ANot BNot CD
2012.6%Not ANot BNot CNot D
21
Sheet1
Cell Formulas
RangeFormula
C2:C5C2="Not "&INDEX(A:A,SEQUENCE(G2)+1,0)
D2:D5D2=1-INDEX(B:B,SEQUENCE(G2)+1)
G2G2=COUNTA(A2:A99)
G5:K20G5=IF(MOD(INT((SEQUENCE(2^\$G\$2)-1)/2^(\$G\$2-COLUMNS(\$F:F))),2),"Not ","")&OFFSET(\$A\$2,COLUMNS(\$F:F)-1,0)
F5:F20F5=IFERROR(PRODUCT(IFERROR(VLOOKUP(OFFSET(G5,,,,\$G\$2),A:B,2,0),VLOOKUP(OFFSET(G5,,,,\$G\$2),C:D,2,0))),"")
Dynamic array formulas.

#### NunoAMFF

##### New Member
Hi, Habtest.
Thanks for the help, it looks great.
I don't seem to have the SEQUENCE function available, even though I'm using Excel for Office 365. I'm updating Office now and it will hopefully be available afterwards.
I'll try your formulas then and give you feedback.
Thanks again.
Regards,
Nuno

#### NunoAMFF

##### New Member
Hi, Habtest.
Just got EXCEL updated with the SEQUENCE function and tried your formulas.
It all works great.
Thanks you so much.
Cheers,
Nuno

Replies
1
Views
816
Replies
2
Views
604
Replies
10
Views
228
Replies
1
Views
255
Replies
1
Views
421

1,141,865
Messages
5,709,076
Members
421,613
Latest member
wyzco

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back