If function greater than and less with multiple tests

Kearnsy

New Member
Joined
Jul 8, 2008
Messages
10
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
    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
Joined
May 26, 2009
Messages
17,596
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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, "")))
 
Solution

Kearnsy

New Member
Joined
Jul 8, 2008
Messages
10
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
Joined
Jun 12, 2014
Messages
64,110
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jul 8, 2008
Messages
10

ADVERTISEMENT

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
Joined
Jun 12, 2014
Messages
64,110
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

alz

Board Regular
Joined
Jul 17, 2020
Messages
130
Office Version
  1. 2019
Platform
  1. Windows
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
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top