# If function greater than and less with multiple tests

#### Kearnsy

##### New Member
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

#### Attachments

• Capture.PNG
3.4 KB · Views: 7

### Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

#### JoeMo

##### MrExcel MVP
Try:
Excel Formula:
``=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

##### New Member
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

##### MrExcel MVP, Moderator
How about replacing that long nested if with
Excel Formula:
``=CEILING(F71,0.25)``
I haven't looked fully at you formula, but they seem to do the same thing.

#### Kearnsy

##### New Member

How about replacing that long nested if with
Excel Formula:
``=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

##### MrExcel MVP, Moderator
Glad we could help & thanks for the feedback.

#### alz

##### Board Regular
Here is Different approch!

Excel Message board.xlsm
ABCDEFGHIJKLM
1>=<=ResultGap
210.010.250.250.24
3TEST 1TESTOver gap allow by > Test 1Over gap allow by < testUpper bound spill allowWithin Criteria only20.260.50.50.24
4><TRUETRUETRUETRUE30.510.750.750.24
51.131.371.251.251.25Over Gap40.76110.24
61.381.671.51.51.5Over Gap51.011.251.250.24
71.681.871.751.751.751.7561.261.51.50.24
84.274.54.54.54.54.571.511.751.750.24
94.274.514.54.54.5Over Gap81.71220.29
104.254.54.254.5Over Gap4.592.012.252.250.24
114.264.554.54.54.5Over Gap102.262.52.50.24
124.014.54.254.5Over Gap4.5112.512.752.750.24
13122.76330.24
14133.013.253.250.24
15143.263.53.50.24
16153.513.753.750.24
17163.76440.24
18174.014.254.250.24
19184.264.54.50.24
20194.514.754.750.24
21204.76550.24
22
Sheet1
Cell Formulas
RangeFormula
C5:C12C5=VLOOKUP(A5,\$I\$2:\$K\$21,3)
D5:D12D5=VLOOKUP(B5,\$J\$2:\$K\$21,1)
E5E5=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:F12F5=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:E12E6=IF(MATCH(A6,\$I\$2:\$I\$21)=MATCH(B6,\$J\$2:\$J\$21),VLOOKUP(B6,\$J\$2:\$K\$21,2),"Over Gap")
L2:L21L2=J2-I2

Replies
3
Views
242
Replies
5
Views
67
Replies
3
Views
325
Replies
2
Views
280
Replies
10
Views
737

### Forum statistics

1,144,615
Messages
5,725,321
Members
422,613
Latest member
salim9696 ### 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.

### Which adblocker are you using?    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

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