If function greater than and less with multiple tests

[Kearnsy]

Hey People,

I am trying to create a formula whereby, if a cell falls within a number range i.e. greater than shall we say >1.13 and less than <1.37 then the cell will either give me a number if its true i.e. 1.25 or it remains blank "".

So I managed to get that right and my formula equals =IF(AND(A3>1.13,A3<1.373),1.25,"")

However, I have multiple tests that I need to perform in that cell and this is kind of what I'm trying to achieve

TEST 1​	TEST​
>​	<​	TRUE​
1.13​	1.37​	1.25​
1.38​	1.67​	1.50​
1.68​	1.87​	1.75​

I have tried the following formula but it doesn't seem to work, I am sure I am doing something wrong here;

=IF(AND(A2>1.13,A2<1.373),1.25,""), IF(AND(A2>1.38,A2<1.67),1.5,""), IF(AND(A2>1.68,A2<1.87),1.75, "")

Any help would be much appreciated.

Kind regards
David

 

[JoeMo]

Try:

=IF(AND(A2>1.13,A2<1.373),1.25, IF(AND(A2>1.38,A2<1.67),1.5,IF(AND(A2>1.68,A2<1.87),1.75, "")))

 

[Kearnsy]

Wow thank you so much that has really helped me tremendously.

I have now taken my spreadsheet one step further but have run into one more problem, and i've been on this hours and so close to finishing it. I have taken your formula one step further and have it working and this is what the formula looks like;

=IF(AND(F71>=0.01,F71<=0.25),0.25,IF(AND(F71>=0.26,F71<=0.5),0.5,IF(AND(F71>=0.51,F71<=0.75),0.75,IF(AND(F71>=0.76,F71<=1),1,IF(AND(F71>=1.01,F71<=1.25),1.25,IF(AND(F71>=1.26,F71<=1.5),1.5,IF(AND(F71>=1.51,F71<=1.75),1.75,IF(AND(F71>=1.71,F71<=2),2,IF(AND(F71>=2.01,F71<=2.25),2.25,IF(AND(F71>=2.26,F71<=2.5),2.5,IF(AND(F71>=2.51,F71<=2.75),2.75,IF(AND(F71>=2.76,F71<=3),3,IF(AND(F71>=3.01,F71<=3.25),3.25,IF(AND(F71>=3.26,F71<=3.5),3.5,IF(AND(F71>=3.51,F71<=3.75),3.75,IF(AND(F71>=3.76,F71<=4),4,IF(AND(F71>=4.01,F71<=4.25),4.25,IF(AND(F71>=4.26,F71<=4.5),4.5,IF(AND(F71>=4.51,F71<=4.75),4.75,IF(AND(F71>=4.76,F71<=5),5,""))))))))))))))))))))


But broken down it looks like this;

IF(AND(F79>=0.01,F79<=0.25),0.25,
IF(AND(F79>=0.26,F79<=0.5),0.5,
IF(AND(F79>=0.51,F79<=0.75),0.75,
IF(AND(F79>=0.76,F79<=1),1,
IF(AND(F79>=1.01,F79<=1.25),1.25,
IF(AND(F79>=1.26,F79<=1.5),1.5,
IF(AND(F79>=1.51,F79<=1.75),1.75,
IF(AND(F79>=1.71,F79<=2),2,
IF(AND(F79>=2.01,F79<=2.25),2.25,
IF(AND(F79>=2.26,F79<=2.5),2.5,
IF(AND(F79>=2.51,F79<=2.75),2.75,
IF(AND(F79>=2.76,F79<=3),3,
IF(AND(F79>=3.01,F79<=3.25),3.25,
IF(AND(F79>=3.26,F79<=3.5),3.5,
IF(AND(F79>=3.51,F79<=3.75),3.75,
IF(AND(F79>=3.76,F79<=4),4,
IF(AND(F79>=4.01,F79<=4.25),4.25,
IF(AND(F79>=4.26,F79<=4.5),4.5,
IF(AND(F79>=4.51,F79<=4.75),4.75,
IF(AND(F79>=4.76,F79<=5),5,""

However, I do have a problem and it comes from the section i have highlighted in red above. When F79 displays 1.26, the cell that the formula is in will not display 1.50 as it should do (IF(AND(F79>=1.26,F79<=1.5),1.5,) . Instead, it remains blank and it is only when I physically write 1.26 then it works.

It works for all other values but this one and then when I go into F79 and physically write 1.26 then the cell with my formula in, will display 1.50.

What I should tell you is that F79's value comes from a calculation within the cell which looks like this =IFERROR((((C79+D79)/2)-E79),""). This calculation is correct and there is nothing wrong with it and works perfectly fine.

So F79 has =IFERROR((((C79+D79)/2)-E79),"") and the cell im working in lets call it H79 has that long IF calculation above which includes (IF(AND(F79>=1.26,F79<=1.5),1.5,)

 

[Fluff]

How about replacing that long nested if with

=CEILING(F71,0.25)

I haven't looked fully at you formula, but they seem to do the same thing.

 

[Kearnsy]

How about replacing that long nested if with

=CEILING(F71,0.25)

I haven't looked fully at you formula, but they seem to do the same thing.

Wow, I wish I asked this earlier, that is so much simpler, thank you very much for replying to my query, i can not tell you how helpful this is.

Thank you.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[alz]

Here is Different approch!

Excel Message board.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1									>=	<=	Result	Gap
2								1	0.01	0.25	0.25	0.24
3	TEST 1	TEST	Over gap allow by > Test 1	Over gap allow by < test	Upper bound spill allow	Within Criteria only		2	0.26	0.5	0.5	0.24
4	>	<	TRUE	TRUE	TRUE	TRUE		3	0.51	0.75	0.75	0.24
5	1.13	1.37	1.25	1.25	1.25	Over Gap		4	0.76	1	1	0.24
6	1.38	1.67	1.5	1.5	1.5	Over Gap		5	1.01	1.25	1.25	0.24
7	1.68	1.87	1.75	1.75	1.75	1.75		6	1.26	1.5	1.5	0.24
8	4.27	4.5	4.5	4.5	4.5	4.5		7	1.51	1.75	1.75	0.24
9	4.27	4.51	4.5	4.5	4.5	Over Gap		8	1.71	2	2	0.29
10	4.25	4.5	4.25	4.5	Over Gap	4.5		9	2.01	2.25	2.25	0.24
11	4.26	4.55	4.5	4.5	4.5	Over Gap		10	2.26	2.5	2.5	0.24
12	4.01	4.5	4.25	4.5	Over Gap	4.5		11	2.51	2.75	2.75	0.24
13								12	2.76	3	3	0.24
14								13	3.01	3.25	3.25	0.24
15								14	3.26	3.5	3.5	0.24
16								15	3.51	3.75	3.75	0.24
17								16	3.76	4	4	0.24
18								17	4.01	4.25	4.25	0.24
19								18	4.26	4.5	4.5	0.24
20								19	4.51	4.75	4.75	0.24
21								20	4.76	5	5	0.24
22
Sheet1

Cell Formulas
Range	Formula
C5:C12	C5	=VLOOKUP(A5,$I$2:$K$21,3)
D5:D12	D5	=VLOOKUP(B5,$J$2:$K$21,1)
E5	E5	=IF(OR(MATCH(A5,$I$2:$I$21)=MATCH(B5,$J$2:$J$21),MATCH(A5,$I$2:$I$21)=MATCH(B5,$I$2:$I$21)),VLOOKUP(B5,$J$2:$K$21,2),"Over Gap")
F5:F12	F5	=IF(OR(MATCH(A5,$I$2:$I$21)>MATCH(B5,$J$2:$J$21),VLOOKUP(B5,$I$2:$I$21,1)>VLOOKUP(B5,$J$2:$J$21,1)),"Over Gap",VLOOKUP(B5,$J$2:$K$21,2))
E6:E12	E6	=IF(MATCH(A6,$I$2:$I$21)=MATCH(B6,$J$2:$J$21),VLOOKUP(B6,$J$2:$K$21,2),"Over Gap")
L2:L21	L2	=J2-I2