John
asked on
Nested IF Statement with Greater Than and Less Than
Hello,
I need to do a nested if statement within Excel 2007. I am working with number values and need to basically say IF value is greater than X do this but IF value is less than X and greater than Y Do That and IF value is less than Y but greater than Z do something else.
Please advise.
Thanks,
John
I need to do a nested if statement within Excel 2007. I am working with number values and need to basically say IF value is greater than X do this but IF value is less than X and greater than Y Do That and IF value is less than Y but greater than Z do something else.
Please advise.
Thanks,
John
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hello John,
If the "ranges" are contiguous then there's usually no need for and, e.g.
IF you want to return "x" for values less than 3, "y" for value greater than or equal to 3 but less than 7, "z" for values >= 7 but < 20 and "a" for >=20 then this nested IF would worl
=IF(A1<3,"x",IF(A1<7,"y",I F(A1<20,"z ","a")))
...or especially for larger numbers of ranges a LOOKUP formula is better. List the lower bound of each range in Y2:Y10 (in ascending order) and the corresponding values to be returned in Z2:Z10 and use
=LOOKUP(A1,Y$2:Z$10)
regards, barry
If the "ranges" are contiguous then there's usually no need for and, e.g.
IF you want to return "x" for values less than 3, "y" for value greater than or equal to 3 but less than 7, "z" for values >= 7 but < 20 and "a" for >=20 then this nested IF would worl
=IF(A1<3,"x",IF(A1<7,"y",I
...or especially for larger numbers of ranges a LOOKUP formula is better. List the lower bound of each range in Y2:Y10 (in ascending order) and the corresponding values to be returned in Z2:Z10 and use
=LOOKUP(A1,Y$2:Z$10)
regards, barry
ASKER
Hello,
Thank you! The only change I made was, rather than just > and <, I used the >= and <=.
John
Thank you! The only change I made was, rather than just > and <, I used the >= and <=.
John
Glad it worked, didn't expect that to do fully do the job.
=IF(A1>X,"this",IF(A1>Y,"T
However, you're ignoring the possibility that your test value (A1, in this example) may be equal to X, Y, or Z.
In those cases, the return result may not be correct.