0

I have the following function:

=IF(B4<5, "Excellent", IF(OR(B4>5, B4<7), "Good", IF(B4>=7, "Satisfactory")))

I want to to give excellent if the cell value is less than 5%, given Good if it is between 5 and 7 percent and give satisfactory if the cell value is bigger than 7 %.

The cell values (B4) that the function refers to, is in percentages. does anybody see what is wrong?

2

Displaying as % is formatting; the value is stored as the actual (decimal) value, not the whole number of percent. To test for <5%, use <.05, etc.

BTW, if you want to test for a value between 5% and 7%, use AND instead of OR. OR will be true for, say, 3% because that is <7%, or 8% because that's more than 5%.

Also, you will have holes in your ranges if you test for only < and >; you need to provide for when the values are exactly equal. Figure out whether you want <= vs. >, or < vs. >=.

You can also save an IF. Your first two IFs test for everything <.07, so the false condition will be >=.07. The general form for IF is IF(condition, true result, false result). So your formula would look like this:

=IF(B4<.05, "Excellent", IF(AND(B4>=.05, B4<=.07), "Good", "Satisfactory"))
0

As percentage form are between 0 and 1,You should rewrite if function like B4<0.05 ... It should be like this:

=IF(B4<0.05, "Excellent", IF(OR(B4>0.05, B4<0.07), "Good", IF(B4>=0.07, "Satisfactory")))

As I see your IF function, you forgot to define 5% in your function

  • Welcome to Super User. Two thoughts on your answer. One is that it misses some obvious errors in the OP's formula. The other is that the intention is for each answer to provide a solution that hasn't already been contributed. This one duplicates what is already in the other answer. But do continue to participate; and thanks for taking the time to try to help on this question. – fixer1234 Dec 5 '18 at 19:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.