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

NunoAMFF

New Member
Joined
Apr 21, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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?

Thanks for your help.

Regards,

Nuno
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,258
Office Version
  1. 365
Platform
  1. Windows
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],
    Link = Table.AddColumn(Source, "Link", each 1),
    Merge = Table.NestedJoin(Link, {"Link"}, Link, {"Link"}, "Added Custom", JoinKind.LeftOuter),
    Expand = Table.ExpandTableColumn(Merge, "Added Custom", {"Event", "Probability"}, {"Event.1", "Probability.1"}),
    SR = Table.SelectRows(Expand, each [Event] <> [Event.1]),
    RC = Table.RemoveColumns(SR,{"Link"}),
    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
Joined
Jul 14, 2008
Messages
3,258
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Apr 21, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Thank you so much, lrobbo. This works great.

Cheers.
 

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,258
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

No problemo. Happy to help.
 

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,258
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Apr 21, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Jul 30, 2020
Messages
194
Office Version
  1. 365
Platform
  1. Windows
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.
 
Solution

NunoAMFF

New Member
Joined
Apr 21, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Apr 21, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Hi, Habtest.
Just got EXCEL updated with the SEQUENCE function and tried your formulas.
It all works great.
Thanks you so much.
Cheers,
Nuno
 

Forum statistics

Threads
1,141,858
Messages
5,709,039
Members
421,608
Latest member
jking1

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top