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

[NunoAMFF]

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

 

[lrobbo314]

Using Power Query

Sunrise Sunset (version 1).xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Event	Probability		A	Probability A	B	Probability B	Not A	Not B	AandB	AorB	AorBnotBoth	nAnB	AnotB	BnotA
2	A	30%		A	30%	B	60%	70%	40%	18%	72%	54%	28%	12%	42%
3	B	60%		A	30%	C	20%	70%	80%	6%	44%	38%	56%	24%	14%
4	C	20%		A	30%	D	45%	70%	55%	14%	62%	48%	39%	17%	32%
5	D	45%		A	30%	E	15%	70%	85%	5%	41%	36%	60%	26%	11%
6	E	15%		A	30%	F	70%	70%	30%	21%	79%	58%	21%	9%	49%
7	F	70%		B	60%	A	30%	40%	70%	18%	72%	54%	28%	42%	12%
8				B	60%	C	20%	40%	80%	12%	68%	56%	32%	48%	8%
9				B	60%	D	45%	40%	55%	27%	78%	51%	22%	33%	18%
10				B	60%	E	15%	40%	85%	9%	66%	57%	34%	51%	6%
11				B	60%	F	70%	40%	30%	42%	88%	46%	12%	18%	28%
12				C	20%	A	30%	80%	70%	6%	44%	38%	56%	14%	24%
13				C	20%	B	60%	80%	40%	12%	68%	56%	32%	8%	48%
14				C	20%	D	45%	80%	55%	9%	56%	47%	44%	11%	36%
15				C	20%	E	15%	80%	85%	3%	32%	29%	68%	17%	12%
16				C	20%	F	70%	80%	30%	14%	76%	62%	24%	6%	56%
17				D	45%	A	30%	55%	70%	14%	62%	48%	39%	32%	17%
18				D	45%	B	60%	55%	40%	27%	78%	51%	22%	18%	33%
19				D	45%	C	20%	55%	80%	9%	56%	47%	44%	36%	11%
20				D	45%	E	15%	55%	85%	7%	53%	47%	47%	38%	8%
21				D	45%	F	70%	55%	30%	32%	84%	52%	17%	14%	39%
22				E	15%	A	30%	85%	70%	5%	41%	36%	60%	11%	26%
23				E	15%	B	60%	85%	40%	9%	66%	57%	34%	6%	51%
24				E	15%	C	20%	85%	80%	3%	32%	29%	68%	12%	17%
25				E	15%	D	45%	85%	55%	7%	53%	47%	47%	8%	38%
26				E	15%	F	70%	85%	30%	11%	75%	64%	26%	5%	60%
27				F	70%	A	30%	30%	70%	21%	79%	58%	21%	49%	9%
28				F	70%	B	60%	30%	40%	42%	88%	46%	12%	28%	18%
29				F	70%	C	20%	30%	80%	14%	76%	62%	24%	56%	6%
30				F	70%	D	45%	30%	55%	32%	84%	52%	17%	39%	14%
31				F	70%	E	15%	30%	85%	11%	75%	64%	26%	60%	5%
Sheet2

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]

Or using formulas.

Sunrise Sunset (version 1).xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Event	Prob		Combo	Prob A	Prob B	P(A')	P(B')	P(A∩B)	P(A∪B)	P(AΔB)	P((A∪B)')	A Not B	B Not A
2	A	30%		A-A	30%	30%	70%	70%	9%	51%	42%	49%	21%	21%
3	B	60%		A-B	30%	60%	70%	40%	18%	72%	54%	28%	12%	42%
4	C	20%		A-C	30%	20%	70%	80%	6%	44%	38%	56%	24%	14%
5	D	45%		A-D	30%	45%	70%	55%	14%	62%	48%	39%	17%	32%
6	E	15%		A-E	30%	15%	70%	85%	5%	41%	36%	60%	26%	11%
7	F	70%		A-F	30%	70%	70%	30%	21%	79%	58%	21%	9%	49%
8				B-A	60%	30%	40%	70%	18%	72%	54%	28%	42%	12%
9				B-B	60%	60%	40%	40%	36%	84%	48%	16%	24%	24%
10				B-C	60%	20%	40%	80%	12%	68%	56%	32%	48%	8%
11				B-D	60%	45%	40%	55%	27%	78%	51%	22%	33%	18%
12				B-E	60%	15%	40%	85%	9%	66%	57%	34%	51%	6%
13				B-F	60%	70%	40%	30%	42%	88%	46%	12%	18%	28%
14				C-A	20%	30%	80%	70%	6%	44%	38%	56%	14%	24%
15				C-B	20%	60%	80%	40%	12%	68%	56%	32%	8%	48%
16				C-C	20%	20%	80%	80%	4%	36%	32%	64%	16%	16%
17				C-D	20%	45%	80%	55%	9%	56%	47%	44%	11%	36%
18				C-E	20%	15%	80%	85%	3%	32%	29%	68%	17%	12%
19				C-F	20%	70%	80%	30%	14%	76%	62%	24%	6%	56%
20				D-A	45%	30%	55%	70%	14%	62%	48%	39%	32%	17%
21				D-B	45%	60%	55%	40%	27%	78%	51%	22%	18%	33%
22				D-C	45%	20%	55%	80%	9%	56%	47%	44%	36%	11%
23				D-D	45%	45%	55%	55%	20%	70%	50%	30%	25%	25%
24				D-E	45%	15%	55%	85%	7%	53%	47%	47%	38%	8%
25				D-F	45%	70%	55%	30%	32%	84%	52%	17%	14%	39%
26				E-A	15%	30%	85%	70%	5%	41%	36%	60%	11%	26%
27				E-B	15%	60%	85%	40%	9%	66%	57%	34%	6%	51%
28				E-C	15%	20%	85%	80%	3%	32%	29%	68%	12%	17%
29				E-D	15%	45%	85%	55%	7%	53%	47%	47%	8%	38%
30				E-E	15%	15%	85%	85%	2%	28%	26%	72%	13%	13%
31				E-F	15%	70%	85%	30%	11%	75%	64%	26%	5%	60%
32				F-A	70%	30%	30%	70%	21%	79%	58%	21%	49%	9%
33				F-B	70%	60%	30%	40%	42%	88%	46%	12%	28%	18%
34				F-C	70%	20%	30%	80%	14%	76%	62%	24%	56%	6%
35				F-D	70%	45%	30%	55%	32%	84%	52%	17%	39%	14%
36				F-E	70%	15%	30%	85%	11%	75%	64%	26%	60%	5%
37				F-F	70%	70%	30%	30%	49%	91%	42%	9%	21%	21%
Sheet5

Cell Formulas
Range	Formula
D2:D37	D2	=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:E37	E2	=LET(e,B2:B7,r,COUNTA(e)^2,INDEX(e,INT(SEQUENCE(r,,0)/COUNTA(e)+1)))
F2:F37	F2	=LET(e,B2:B7,r,COUNTA(e)^2,INDEX(e,MOD(SEQUENCE(r,,0),COUNTA(e))+1))
G2:H37,L2:L37	G2	=1-E2
I2:I37	I2	=E2*F2
J2:J37	J2	=SUM(E2:F2)-I2
K2:K37	K2	=SUM(E2:F2)-2*I2
M2:M37	M2	=E2*(1-F2)
N2:N37	N2	=(1-E2)*F2
Dynamic array formulas.

 

[NunoAMFF]

Thank you so much, lrobbo. This works great.

Cheers.

 

[lrobbo314]

No problemo. Happy to help.

 

[lrobbo314]

Can also be done with a single cell formula.

MXL Groups (version 1).xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Event	Prob		Combo	Prob A	Prob B	P(A')	P(B')	P(A∩B)	P(A∪B)	P(AΔB)	P((A∪B)')	A Not B	B Not A
2	A	30.0%		A-A	30.0%	30.0%	70.0%	70.0%	9.0%	51.0%	42.0%	49.0%	21.0%	21.0%
3	B	60.0%		A-B	30.0%	60.0%	70.0%	40.0%	18.0%	72.0%	54.0%	28.0%	12.0%	42.0%
4	C	20.0%		A-C	30.0%	20.0%	70.0%	80.0%	6.0%	44.0%	38.0%	56.0%	24.0%	14.0%
5	D	45.0%		A-D	30.0%	45.0%	70.0%	55.0%	13.5%	61.5%	48.0%	38.5%	16.5%	31.5%
6	E	15.0%		A-E	30.0%	15.0%	70.0%	85.0%	4.5%	40.5%	36.0%	59.5%	25.5%	10.5%
7	F	70.0%		A-F	30.0%	70.0%	70.0%	30.0%	21.0%	79.0%	58.0%	21.0%	9.0%	49.0%
8				B-A	60.0%	30.0%	40.0%	70.0%	18.0%	72.0%	54.0%	28.0%	42.0%	12.0%
9				B-B	60.0%	60.0%	40.0%	40.0%	36.0%	84.0%	48.0%	16.0%	24.0%	24.0%
10				B-C	60.0%	20.0%	40.0%	80.0%	12.0%	68.0%	56.0%	32.0%	48.0%	8.0%
11				B-D	60.0%	45.0%	40.0%	55.0%	27.0%	78.0%	51.0%	22.0%	33.0%	18.0%
12				B-E	60.0%	15.0%	40.0%	85.0%	9.0%	66.0%	57.0%	34.0%	51.0%	6.0%
13				B-F	60.0%	70.0%	40.0%	30.0%	42.0%	88.0%	46.0%	12.0%	18.0%	28.0%
14				C-A	20.0%	30.0%	80.0%	70.0%	6.0%	44.0%	38.0%	56.0%	14.0%	24.0%
15				C-B	20.0%	60.0%	80.0%	40.0%	12.0%	68.0%	56.0%	32.0%	8.0%	48.0%
16				C-C	20.0%	20.0%	80.0%	80.0%	4.0%	36.0%	32.0%	64.0%	16.0%	16.0%
17				C-D	20.0%	45.0%	80.0%	55.0%	9.0%	56.0%	47.0%	44.0%	11.0%	36.0%
18				C-E	20.0%	15.0%	80.0%	85.0%	3.0%	32.0%	29.0%	68.0%	17.0%	12.0%
19				C-F	20.0%	70.0%	80.0%	30.0%	14.0%	76.0%	62.0%	24.0%	6.0%	56.0%
20				D-A	45.0%	30.0%	55.0%	70.0%	13.5%	61.5%	48.0%	38.5%	31.5%	16.5%
21				D-B	45.0%	60.0%	55.0%	40.0%	27.0%	78.0%	51.0%	22.0%	18.0%	33.0%
22				D-C	45.0%	20.0%	55.0%	80.0%	9.0%	56.0%	47.0%	44.0%	36.0%	11.0%
23				D-D	45.0%	45.0%	55.0%	55.0%	20.3%	69.8%	49.5%	30.3%	24.8%	24.8%
24				D-E	45.0%	15.0%	55.0%	85.0%	6.8%	53.3%	46.5%	46.8%	38.3%	8.3%
25				D-F	45.0%	70.0%	55.0%	30.0%	31.5%	83.5%	52.0%	16.5%	13.5%	38.5%
26				E-A	15.0%	30.0%	85.0%	70.0%	4.5%	40.5%	36.0%	59.5%	10.5%	25.5%
27				E-B	15.0%	60.0%	85.0%	40.0%	9.0%	66.0%	57.0%	34.0%	6.0%	51.0%
28				E-C	15.0%	20.0%	85.0%	80.0%	3.0%	32.0%	29.0%	68.0%	12.0%	17.0%
29				E-D	15.0%	45.0%	85.0%	55.0%	6.8%	53.3%	46.5%	46.8%	8.3%	38.3%
30				E-E	15.0%	15.0%	85.0%	85.0%	2.3%	27.8%	25.5%	72.3%	12.8%	12.8%
31				E-F	15.0%	70.0%	85.0%	30.0%	10.5%	74.5%	64.0%	25.5%	4.5%	59.5%
32				F-A	70.0%	30.0%	30.0%	70.0%	21.0%	79.0%	58.0%	21.0%	49.0%	9.0%
33				F-B	70.0%	60.0%	30.0%	40.0%	42.0%	88.0%	46.0%	12.0%	28.0%	18.0%
34				F-C	70.0%	20.0%	30.0%	80.0%	14.0%	76.0%	62.0%	24.0%	56.0%	6.0%
35				F-D	70.0%	45.0%	30.0%	55.0%	31.5%	83.5%	52.0%	16.5%	38.5%	13.5%
36				F-E	70.0%	15.0%	30.0%	85.0%	10.5%	74.5%	64.0%	25.5%	59.5%	4.5%
37				F-F	70.0%	70.0%	30.0%	30.0%	49.0%	91.0%	42.0%	9.0%	21.0%	21.0%
Sheet9

Cell Formulas
Range	Formula
D2:N37	D2	=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]

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]

If you don't mind some helper cells:

Test.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Input Event	Input Probability
2	A	30%	Not A	70%		Event Total	4
3	B	60%	Not B	40%
4	C	10%	Not C	90%		Probability
5	D	50%	Not D	50%		0.9%	A	B	C	D
6						0.9%	A	B	C	Not D
7						8.1%	A	B	Not C	D
8						8.1%	A	B	Not C	Not D
9						0.6%	A	Not B	C	D
10						0.6%	A	Not B	C	Not D
11						5.4%	A	Not B	Not C	D
12						5.4%	A	Not B	Not C	Not D
13						2.1%	Not A	B	C	D
14						2.1%	Not A	B	C	Not D
15						18.9%	Not A	B	Not C	D
16						18.9%	Not A	B	Not C	Not D
17						1.4%	Not A	Not B	C	D
18						1.4%	Not A	Not B	C	Not D
19						12.6%	Not A	Not B	Not C	D
20						12.6%	Not A	Not B	Not C	Not D
21
Sheet1

Cell Formulas
Range	Formula
C2:C5	C2	="Not "&INDEX(A:A,SEQUENCE(G2)+1,0)
D2:D5	D2	=1-INDEX(B:B,SEQUENCE(G2)+1)
G2	G2	=COUNTA(A2:A99)
G5:K20	G5	=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:F20	F5	=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]

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]

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