Solved Problem with IF function

July 22, 2011 at 11:20:21
Specs: Windows 7
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

See More: Problem with IF function

Report •

July 22, 2011 at 19:43:27
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...


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

Report •

July 23, 2011 at 00:37:34
So it would not be a problem if a part of the formula refers on A2 and other part on C4 cell.

Thank you.

Report •

July 23, 2011 at 02:44:21
✔ Best Answer
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.

Report •

Related Solutions

July 25, 2011 at 13:40:00
Thank you for your unselfish help. Everything works like a charm.

All the best,


Report •

Ask Question