Conditional IF

[sanjog]

Hello Folks,

I have following conditions that I want to check on a cell

1. IF Value >140 then it returns A
2. IF Value between 120-140 it returns B
3. IF Value is between 100-120 it returns C
4. IF Value is between 80-100 it returns D
5. IF Value is <80 it return E

How can I do this in a single formula

Help much appreciated

 

[anglais428]

=IF(A1>140,"A",IF(AND(A1>=120,A1<=140),"B",IF(AND(A1>=100,A1<=120),"C",IF(AND(A1>=80,A1<100),"D",IF(A1<80,"E","Error")))))

You may want to adjust the boundaries (e.g. what if the value is 100 exactly - should it be "B" or "C"?

 

[sanjog]

it is returning an error to me

 

[sanjog]

I think Excel 2007 is bugging me here to force to use logical test on IF. It says formula you typed contains an error. Anglais428, which version of excel are you using ?

 

[sanjog]

It worked ...had to replace all commas with #. Dont know why

 

[Robert Mika]

=if(a1="","",lookup(a1,{0,80,100,120,140},{"e","d","c","b","a"}))