If Staements with ranges

[vasto]

I am trying to validate the following data set:

Cell AR2 can have a number or be blank

need if statement to say:

if AR2 is a number and if AR2>0 and if AR2<8 then "SMS" or If AR2>7 and if AR2<15 then "Customer Contact" or if AR2>14 and if AR2<22 then "SNP" or if AR2>21 and if AR2<24 then "Demand Notice" or if AR2>23 and if AR2<31 then "Final Contact" or if AR2>30 and if AR2<41 then "CNP" or if AR2>40 then "W/O" if AR2 not a number then leave blank

This is the formula i used and it returned "SMS when the value in AR2 was 22. It should have returned "demand notice" pLease help

=IF(ISNUMBER(AR2),IF(OR(AR2>0,AR2<8),"sms",IF(OR(AR2>7,AR2<15),"Customer Contact",IF(OR(AR2>14,AR2<22),"SNP",IF(OR(AR2>21,AR2<24),"Demand Notice",IF(OR(AR2>23,AR2<31),"Final Contact",IF(OR(AR2>30,AR2<41),"CNP",IF(AR2>40,"W/O",""))))))))

[Cheeky Charlie]

if AR2 is a number and if AR2>0 and if AR2<8 then "SMS" or If AR2>7 and if AR2<15 then "Customer Contact" or if AR2>14 and if AR2<22 then "SNP" or if AR2>21 and if AR2<24 then "Demand Notice" or if AR2>23 and if AR2<31 then "Final Contact" or if AR2>30 and if AR2<41 then "CNP" or if AR2>40 then "W/O" if AR2 not a number then leave blank

Doesn't make it clear which ands relate to which.

Try stating each 'circumstance' (set of criteria) and the result on a single line - we can then compile the appropriate formula for you

hth

[NBVC]

Try:

=LOOKUP(AR2,{0,8,15,22,24,31,41},("SMS","Customer Contact","SNP","Demand Notice","Final Contact","CNP","W/O"})

Not sure why you have overlapping ranges.. but the first array indicate the lower bounds at which the corresponding element in the right array should result.

Edit: Forgot to include the last W/O option for 41 and over...

[vasto]

sorry.

AR2 ranges: Numbers only
1-7 = SMS
8-14 = Client contact
15 and greater = W/O

[Cheeky Charlie]

Not sure why you have overlapping ranges

I think OP assumes AR2 will only ever contain an integer.

You would be well advised to make a table like:

Please Login or Register  to view this content.

and try the Excel help on VLOOKUP - it will save you heaps of time to learn this very useful function

hth