# 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!

#1
February 3, 2017 at 10:29:24
 See how this works for you:=IF(A4>=12,3,IF(A4>=8,2,IF(A4>=4,1,0)))MIKEhttp://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.message edited by DerbyDad03

Report •

#3
February 3, 2017 at 10:46:47
 This formula will return a blank cell for:A4<4A4 between 7.5 and 8, exclusiveA4 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,"")))

Report •

Related Solutions

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

Report •