Forum Discussion
Excel Formula to return a text value based on different criteria
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
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"}
2 Replies
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.
🙂
