If logic for text

[Sri Harsha]

Hi,
I am creating a defect log in which i am trying to capture the severity of
the defect based on its type. I have about 18 types of defect. What i want
now is if the user selects 1-10 defects, the defect severity should be High
and from 11-15, it has to be medium and from 16-18, it has to be low.

Can some one help me with this formula please?

Please note that this has to look at a cell with TEXT and not a value.

Regards,
Sri Harsha.

 

[Khoshravan]

Defect Table
First you have to make a table of defects.
Defect Name(Col. A, text), Defect No.(Col. B,number) A$16:B$33 is your
Defect table
Suppose A10 is your defect text for which you want to return the defect
severity
B10 =VLOOKUP(A10;A$16:B$33;2;FALSE) converts the text to a number

If you don't want to have the helping column of No. you can combine 2 live
of function to each other , but I think this way it is easy to understand.

=IF(AND(B10>0;B10<11);"High";IF(AND(B10>10;B10<16);"Medium";IF(AND(B10>15;B10<19);"Low";""))) converts the number to 3 category of High, Medium and Low

 

[muddan madhu]

In col C col D put this values
1 High
11 Medium
16 Low

now A1 put 1 to 18, in Cell B1 put this formula
=LOOKUP(A1,C$1:C$3,D$1$3)

 

[Ashish Mathur]

Hi,

In col B and col C type these values

1 High
11 Medium
16 Low

Now you can use the VLOOKUP formula =vlookup(A5,B2:C4,2)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com