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.

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(B100;B10<11);"High";IF(AND(B1010;B10<16) ;"Medium";IF(AND(B1015;B10<19);"Low";""))) converts the number to 3 category of High, Medium and Low
--
R. Khoshravan
Please click "Yes" if it is helpful.


"Sri Harsha" wrote:

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.

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:D$3)



On Feb 4, 11:19*am, Sri Harsha <Sri
wrote:
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.

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

"Sri Harsha" <Sri wrote in message
...
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.