Table Formulas

[econnell]

Hello,

I am looking for help on a formula that is adaptive to other cell references. For instance in image, if D9 is 25, D10 is 1 and D11 is 10 then I want the cell D12 to return "yes" if the batch size is between 156 and 219 and "no" if it is outside that range. The cells driving the criteria are D9 which can be 25, 50, 100 or 250, then D11 which is how many vessels and then the ratio either @10 or @40.

 

[StephenCrump]

Perhaps something like this?

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2
3
4
5
6
7
8			Batch Size (mg)	400
9			Container Size (ml)	25
10			No vessels	3
11			Coupling ratio (mg/ml	40
12			Within scope?	No
13
14
15				25				50				100				250
16				Min@10	Max@10			Min@10	Max@10			Min@10	Max@10			Min@10	Max@10
17			One	156	219			300	400			300	400			300	400
18			Two	312	438			600	900			600	900			600	900
19			Three	468	656			900	1300			900	1300			900	1300
20
21				Min@40	Max@40			Min@40	Max@40			Min@40	Max@40			Min@40	Max@40
22			One	624	875			1200	1700			1200	1700			1200	1700
23			Two	1248	1750			2500	3500			2500	3500			2500	3500
24			Three	1872	2625			3700	5200			3700	5200			3700	5200
Sheet1

Cell Formulas
Range	Formula
D12	D12	=IF(D8=MEDIAN(D8,OFFSET(INDEX(D17:Q24,D10+IF(D11=10,0,5),MATCH(D9,D15:P15,)),,,,2)),"Yes","No")

If you are using Excel 2016, I'm pretty sure you'll need to array-enter this formula (i.e. CTRL-Shift-Enter).

 

[econnell]

Perhaps something like this?

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2
3
4
5
6
7
8			Batch Size (mg)	400
9			Container Size (ml)	25
10			No vessels	3
11			Coupling ratio (mg/ml	40
12			Within scope?	No
13
14
15				25				50				100				250
16				Min@10	Max@10			Min@10	Max@10			Min@10	Max@10			Min@10	Max@10
17			One	156	219			300	400			300	400			300	400
18			Two	312	438			600	900			600	900			600	900
19			Three	468	656			900	1300			900	1300			900	1300
20
21				Min@40	Max@40			Min@40	Max@40			Min@40	Max@40			Min@40	Max@40
22			One	624	875			1200	1700			1200	1700			1200	1700
23			Two	1248	1750			2500	3500			2500	3500			2500	3500
24			Three	1872	2625			3700	5200			3700	5200			3700	5200
Sheet1

Cell Formulas
Range	Formula
D12	D12	=IF(D8=MEDIAN(D8,OFFSET(INDEX(D17:Q24,D10+IF(D11=10,0,5),MATCH(D9,D15:P15,)),,,,2)),"Yes","No")

If you are using Excel 2016, I'm pretty sure you'll need to array-enter this formula (i.e. CTRL-Shift-Enter).

Amazing! Thank you so much!! The only thing that doesnt work when I tested its limits is the maximum values show No for being within scope.

The lower limit works just fine as that is still within scope.

 

[StephenCrump]

It's impossible to tell what's going on based on just a picture.

But my first guess is that the upper batch size limits are not integer values 2,188 and 219 as displayed, but slightly lower values?

 

[econnell]

Yes that was it! the cell was pulling from a calculation but only displaying a 'false' integer. Thank you!!