Finding the combination of different columns in each cell to not get over the threshold

[rosasellen12]

Suppose I want to compute the maximum probabilities that will not get under the 0.95 limit. The maximum possible combination is 0.95, multiplied in each column displayed in yellow. Therefore, is it possible to calculate it in excel such that each column will be multiplied by different combinations, then it would find the maximum allowable range that would not get under the limit. Also, the numbers on the left would be displayed that it is possible to see the designated numbers in each column that got the lowest number that would not get under the limit.

In this scenario, in order to get the lowest number, x1=5, x2=7 and x3=8 that the yellow cells are multipled to in order to get 0.951, which is not under 0.95.

 

[JamesCanale]

This would be much easier in 365. Here it is with some array formulas that you have to ctrl enter them in.

MrExcelPlayground14.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1		x1	x2	x3			9	12	14			0.95	0.950171	937	5	6	12
2	0	0.1353	0.0498	0.0183		1512	1	1	1		0.000123
3	1	0.406	0.1991	0.0916			1	1	2		0.000617
4	2	0.6767	0.4232	0.2381			1	1	3		0.001604
5	3	0.8571	0.6472	0.4335			1	1	4		0.002921
6	4	0.9473	0.8153	0.6288			1	1	5		0.004237
7	5	0.9834	0.9161	0.7851			1	1	6		0.00529
8	6	0.9955	0.9665	0.8893			1	1	7		0.005992
9	7	0.9989	0.9881	0.9489			1	1	8		0.006394
10	8	0.9998	0.9962	0.9786			1	1	9		0.006594
11	9		0.9989	0.9919			1	1	10		0.006683
12	10		0.9997	0.9972			1	1	11		0.006719
13	11		0.9999	0.9991			1	1	12		0.006732
14	12			0.9997			1	1	13		0.006736
15	13			0.9999			1	1	14		0.006737
16							1	2	1		0.000493
17							1	2	2		0.002468
18							1	2	3		0.006414
19							1	2	4		0.011678
20							1	2	5		0.016939
21							1	2	6		0.021149
22							1	2	7		0.023956
23							1	2	8		0.025562
24							1	2	9		0.026362
25							1	2	10		0.02672
26							1	2	11		0.026863
27							1	2	12		0.026914
28							1	2	13		0.02693
29							1	2	14		0.026936
30							1	3	1		0.001048
31							1	3	2		0.005245
32							1	3	3		0.013633
Sheet26

Cell Formulas
Range	Formula
G1:I1	G1	=COUNT(B2:B15)
M1	M1	=MIN(L2:L1513)
N1	N1	=MATCH(M1,L2:L1513,0)
O1:Q1	O1	=INDEX(G2#,$N1)-1
F2	F2	=G1*H1*I1
G2:G1513	G2	=INT((ROW(INDIRECT("1:"&F2))-1)/(H1*I1))+1
H2:H1513	H2	=MOD(INT((ROW(INDIRECT("1:"&F2))-1)/(I1)),H1)+1
I2:I1513	I2	=MOD(ROW(INDIRECT("1:"&F2))-1,I1)+1
K2:K32	K2	=INDEX($B$2:$B$10,G2)*INDEX($C$2:$C$13,H2)*INDEX($D$2:$D$15,I2)
L2:L32	L2	=IF(K2>=$L$1,K2,"")
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:D15	Expression	=ROW(B2)-2=O$1	text	NO

 

[rosasellen12]

This would be much easier in 365. Here it is with some array formulas that you have to ctrl enter them in.

MrExcelPlayground14.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1		x1	x2	x3			9	12	14			0.95	0.950171	937	5	6	12
2	0	0.1353	0.0498	0.0183		1512	1	1	1		0.000123
3	1	0.406	0.1991	0.0916			1	1	2		0.000617
4	2	0.6767	0.4232	0.2381			1	1	3		0.001604
5	3	0.8571	0.6472	0.4335			1	1	4		0.002921
6	4	0.9473	0.8153	0.6288			1	1	5		0.004237
7	5	0.9834	0.9161	0.7851			1	1	6		0.00529
8	6	0.9955	0.9665	0.8893			1	1	7		0.005992
9	7	0.9989	0.9881	0.9489			1	1	8		0.006394
10	8	0.9998	0.9962	0.9786			1	1	9		0.006594
11	9		0.9989	0.9919			1	1	10		0.006683
12	10		0.9997	0.9972			1	1	11		0.006719
13	11		0.9999	0.9991			1	1	12		0.006732
14	12			0.9997			1	1	13		0.006736
15	13			0.9999			1	1	14		0.006737
16							1	2	1		0.000493
17							1	2	2		0.002468
18							1	2	3		0.006414
19							1	2	4		0.011678
20							1	2	5		0.016939
21							1	2	6		0.021149
22							1	2	7		0.023956
23							1	2	8		0.025562
24							1	2	9		0.026362
25							1	2	10		0.02672
26							1	2	11		0.026863
27							1	2	12		0.026914
28							1	2	13		0.02693
29							1	2	14		0.026936
30							1	3	1		0.001048
31							1	3	2		0.005245
32							1	3	3		0.013633
Sheet26

Cell Formulas
Range	Formula
G1:I1	G1	=COUNT(B2:B15)
M1	M1	=MIN(L2:L1513)
N1	N1	=MATCH(M1,L2:L1513,0)
O1:Q1	O1	=INDEX(G2#,$N1)-1
F2	F2	=G1*H1*I1
G2:G1513	G2	=INT((ROW(INDIRECT("1:"&F2))-1)/(H1*I1))+1
H2:H1513	H2	=MOD(INT((ROW(INDIRECT("1:"&F2))-1)/(I1)),H1)+1
I2:I1513	I2	=MOD(ROW(INDIRECT("1:"&F2))-1,I1)+1
K2:K32	K2	=INDEX($B$2:$B$10,G2)*INDEX($C$2:$C$13,H2)*INDEX($D$2:$D$15,I2)
L2:L32	L2	=IF(K2>=$L$1,K2,"")
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:D15	Expression	=ROW(B2)-2=O$1	text	NO

hmmm....seems like some of the functions are not possible to do in my current version of excel. Is it possible to convert some of these functions to put it in the older version of excel?


also, I think I gave the wrong wording about the solution; it should be that the lowest number possible (like x1=5, x2=7, and x3=8, the lowest, which gives the total of 20) instead of the lowest probability. So the goal is to minimize the sum of the left side of the number.


Thanks for the 365 version of the computation, but seems like I can't put it in my excel.

 

[JamesCanale]

I had left an array formula in O1:Q1... This should clean it up. Now it gives the lowest multiplicative total for sets that add to 20.

MrExcelPlayground14.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1		x1	x2	x3			9	12	14				0.04974	1190	7	0	13
2	0	0.1353	0.0498	0.0183		1512	1	1	1		0.000123
3	1	0.406	0.1991	0.0916			1	1	2		0.000617
4	2	0.6767	0.4232	0.2381			1	1	3		0.001604
5	3	0.8571	0.6472	0.4335			1	1	4		0.002921
6	4	0.9473	0.8153	0.6288			1	1	5		0.004237
7	5	0.9834	0.9161	0.7851			1	1	6		0.00529
8	6	0.9955	0.9665	0.8893			1	1	7		0.005992
9	7	0.9989	0.9881	0.9489			1	1	8		0.006394
10	8	0.9998	0.9962	0.9786			1	1	9		0.006594
11	9		0.9989	0.9919			1	1	10		0.006683
12	10		0.9997	0.9972			1	1	11		0.006719
13	11		0.9999	0.9991			1	1	12		0.006732
14	12			0.9997			1	1	13		0.006736
15	13			0.9999			1	1	14		0.006737
16							1	2	1		0.000493
17							1	2	2		0.002468
18							1	2	3		0.006414
19							1	2	4		0.011678
20							1	2	5		0.016939
21							1	2	6		0.021149
22							1	2	7		0.023956
23							1	2	8		0.025562
24							1	2	9		0.026362
25							1	2	10		0.02672
26							1	2	11		0.026863
27							1	2	12		0.026914
28							1	2	13		0.02693
29							1	2	14		0.026936
30							1	3	1		0.001048
31							1	3	2		0.005245
32							1	3	3		0.013633
33							1	3	4		0.024822
34							1	3	5		0.036004
35							1	3	6		0.044954
Sheet26

Cell Formulas
Range	Formula
G1:I1	G1	=COUNT(B2:B15)
M1	M1	=MIN(L2:L1513)
N1	N1	=MATCH(M1,L2:L1513,0)
O1:Q1	O1	=INDEX(G2:G1513,$N1)-1
F2	F2	=G1*H1*I1
G2:G1513	G2	=INT((ROW(INDIRECT("1:"&F2))-1)/(H1*I1))+1
H2:H1513	H2	=MOD(INT((ROW(INDIRECT("1:"&F2))-1)/(I1)),H1)+1
I2:I1513	I2	=MOD(ROW(INDIRECT("1:"&F2))-1,I1)+1
K2:K35	K2	=INDEX($B$2:$B$10,G2)*INDEX($C$2:$C$13,H2)*INDEX($D$2:$D$15,I2)
L2:L35	L2	=IF(SUM(G2:I2)-3=20,K2,"")
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:D15	Expression	=ROW(B2)-2=O$1	text	NO