IF logic test help

[jdelahoussaye3]

Good Morning
I need help determining my misstep in creating this formula: it returns the same response for failure as excellent. I need it to answer 5 points for excellent and 0 points for failure. All the others work and respond with appropriate responses. thanks for the help


=IF(H20="failure",0,IF(H20="poor",1,IF(H20="satisfactory",2,IF(H20="good",3,IF(H20="very good",4,IF(H20="excellent",5,0))))))

 

[Joe4]

Welcome to the Board!

it returns the same response for failure as excellent.

It sounds like your entry in H20 is not exactly equal to "excellent". Note that if you have any extra spaces in H20 anywhere, it will not be the same as "excellent".

 

[jdelahoussaye3]

Welcome to the Board!


It sounds like your entry in H20 is not exactly equal to "excellent". Note that if you have any extra spaces in H20 anywhere, it will not be the same as "excellent".

Thanks Joe4
I check for spacing issues and spelling, however it doesn't appear to be the issue, thanks for the advice.

 

[Joe4]

Try entering these formulas in any cell in your workbook and let me know what they return when H20 equals "excellent":

=LEN(H20)

=H20="excellent"

 

[AkaTrouble]

can you test if single if works

IF(H20="excellent",5,0)

if this returns correctly then maybe try changing the order of the IF to look for excellent =5 first and then 4 then 3 etc


edit was making my post before above reply came

 

[Joyner]

The formula looks like it should work. I would think the same as Joe4, there has to be an issue between "excellent" and what is in the cell. Can you try Formula Evaluate to see why it is returning the 0 (zero)?

 

[jdelahoussaye3]

Try entering these formulas in any cell in your workbook and let me know what they return when H20 equals "excellent":

=LEN(H20)

=H20="excellent"

In cell with formula =LEN(H20) the return is 10
in cell with formula =H20="excellent" the return is False

I have evaluated the formula, I have re-written the entire formula, I have written the formula in another cell just for excellent it is correct. I am at a lost for this string formula. It is still returning 0 for both excellent and failure.

Thanks you guys the help!!!! I will continue trying

 

[Joyner]

Since the LEN Function is returning 10, there is an extra space or other non-printing character being including with "excellent" in cell H20

Try this and see what you get (added TRIM for the "excellent" criteria):

=IF(H20="failure",0,IF(H20="poor",1,IF(H20="satisfactory",2,IF(H20="good",3,IF(H20="very good",4,IF(TRIM(H20)="excellent",5,0))))))

 

[jdelahoussaye3]

Since the LEN Function is returning 10, there is an extra space or other non-printing character being including with "excellent" in cell H20

Try this and see what you get (added TRIM for the "excellent" criteria):

=IF(H20="failure",0,IF(H20="poor",1,IF(H20="satisfactory",2,IF(H20="good",3,IF(H20="very good",4,IF(TRIM(H20)="excellent",5,0))))))

Thanks Joyner that helped, in my list format there was a space behind excellent after I used the formula I located the error. This forum is compiled of excel smarts thanks again for the help.

 

[Joe4]

in my list format there was a space behind excellent after I used the formula I located the error.

Ah, just as I expected in my first reply!

Note that if you have any extra spaces in H20 anywhere, it will not be the same as "excellent".

Glad you got it all working now!