Nested Ifs

[Learn_VBA]

I have sales areas of various stores, I need to classify them in to the following bandings:

2000sqft
2000-3000sqft
3000-5000sqft
5000-8000sqft
8000-20000sqft
20000-30000sqft
30000sqft

I've been using the following formual, but it doesn't seem to work. It just returns "30000sqft+"
=IF(K2<2000,"<2000sqft",IF(2000>=K3<3000,"2000-3000sqft",IF(3000>=K3<5000,"3000-5000sqft",IF(5000>=K3<8000,"5000-8000sqft",IF(8000>=K3<20000,"8000-20000sqft",IF(20000>=K3<30000,"20000-30000sqft","30000sqft+"))))))

 

[hennahairgel]

Try: =IF(K3<2000,"<2000sqft",IF(K3<3000,"2000-3000sqft",IF(K3<5000,"3000-5000sqft",IF(K3<8000,"5000-8000sqft",IF(K3<20000,"8000-20000sqft",IF(K3<30000,"20000-30000sqft","30000sqft+"))))))

 

[FormR]

Hi, this syntax "2000>=K3<3000" is not correct - you'd need to use something like AND(K3>=2000,K3<3000) - you also seem to be mixing K3 and K2 -

Here is another alternative you can try, assumes K2 contains the square footage.

=LOOKUP(K3,{0,2000,3000,5000,8000,20000,30000},{"2000","2000-3000","3000-5000","5000-8000","8000-20000","20000-30000","30000+"})&"sqft"

 

[arthurbr]

You cannot use a condition like IF(2000>=K3<3000.. but replace with IF(and(K3>=2000,K3>3000)
Easiest is to create a small table. in the first column (G1:G8,say) enter 0 2000 3000 5000 8000 20000 30000 and in the second the range with 2000sqft...( H1:H8,say)
Then use =LOOKUP(K3,G1:G7,H1:H7) to retrieve the information