I have formula that looks like this:"IF(A2>89;"5";IF(A2>79;"4";IF(A2>69;"3";IF(A2>59;"2";"1"))))" What I need is addition to formula that will negate all statements in the formula in case that in C4 field is written DISQUALIFY.

So, when I write DISQUALIFY it should show 0.

Many thanks

First off, are you aware that when you enclose a number in quotation marks it will be placed in the cell as Text, not as a number? Depending on what you are doing with the value that the IF function returns, that may or may not be a problem.

In any case, you should be aware that a "1" is not the same as a 1.

As far as

"when I write DISQUALIFY it should show 0", you've already written a Nested IF that's 4 IF's deep so I would think that you would know how to add a 5th condition.Just start your formula with...

=IF(A2="Disqualify",0,IF(...etc....

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

So it would not be a problem if a part of the formula refers on A2 and other part on C4 cell. Thank you.

Sorry, I missed the fact that DISQUALIFY would be in C4, not A2, but that doesn't matter. As long as the value in C4 takes priority over the other values, using it as the first IF condition will determine whether the formula will evaluate what is in A2 or not.

The key factor is that a Nested IF "stops" evaluating once a TRUE condition is encountered. You can refer to as many different cells as you like.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Thank you for your unselfish help. Everything works like a charm. All the best,

Ummagumma

Ask Your Question

Weekly Poll

Do you trust smart speakers to not spy on you?

Discuss in The Lounge

Poll History