SOLVED

Excel Formula to return a text value based on different criteria

New Contributor

Hi

I want to create a formula that will return the values: Low, Medium, High or De Facto High based on the value in a field.

For example: low if the score is from 1 to 15, medium if 16 to 33, high if 34 to 60, de facto high if above 79.

Why does the formula below return the error #NAME?

=IF(D23≥34,"High", IF(D23>16,"Medium", IF(D23>1,"Low", IF(D23>79,"De Facto High","Ineligible"))))

Thank you

Claire

2 Replies
best response confirmed by Sergei Baklan (MVP)
Solution

@Claireep 

Excel does not recognize ≥.

For 'greater than or equal to' use >=

For 'less than or equal to to' use <=

For 'not equal to' use <>

Also, you should place the condition D23>79 at the beginning:

 

=IF(D23>=79, "De Facto High", IF(D23>=34, High", IF(D23>=16,"Medium", IF(D23>=1, "Low", "Ineligible"))))

 

You can also use =LOOKUP(D23, {-1000, 1, 16, 34, 79}, {"Ineligible", "Low", "Medium", "High", "De Facto High"}

Thank you Hans, your second option worked like a dream.