Solved Possible? IF's greater and less than, and ranges galore!

February 3, 2017 at 10:08:21
Specs: Windows 7
Hello,

I need help from you excel gods.

If A4 (example) has a number from 4-7.5 I need it to show as 1, if it is from 8-11.5 a 2, and 12+ a 3.

I have been toying with IF formulas and I can't seem to make this work.

Any insight?

Thank you all!


See More: Possible? IFs greater and less than, and ranges galore!

Report •

#1
February 3, 2017 at 10:29:24
✔ Best Answer
See how this works for you:

=IF(A4>=12,3,IF(A4>=8,2,IF(A4>=4,1,0)))

MIKE

http://www.skeptic.com/


Report •

#2
February 3, 2017 at 10:34:52
Another option is to use VLOOKUP table and a TRUE range_lookup argument to look for an approximate match.

With a table like this in G1:H4, the following formula should work, but it will return a value for 7.75, 11.82, etc. (as will Mike's). If values between 7.5 and 8 or between 11.5 and 12 will never exist, then both options will work for you.

       G         H
1      0
2      4         1
3      8         2
4     12         3

=VLOOKUP(A4,$G$1:$H$4,2,1)

The advantage of using VLOOKUP is that if your values change (either the upper and lower bounds or the 1,2,3 return values) you can simply change the table and not have to alter any formulas. It may not be advantageous in this case; I'm just pointing it out for those cases where you might have many more clauses, which can make an IF function rather cumbersome.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#3
February 3, 2017 at 10:46:47
This formula will return a blank cell for:

A4<4
A4 between 7.5 and 8, exclusive
A4 between 11.5 and 12, exclusive

=IF(AND(A4>=4,A4<=7.5),1,IF(AND(A4>=8,A4<=11.5),2,IF(A4>=12,3,"")))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
February 3, 2017 at 11:09:29
THANK YOU! I had the order wrong.

Report •

Ask Question