Problem with IF(AND formula

February 11, 2011 at 14:30:11
Specs: Windows 7
I am getting a FALSE result when using this formula. Do you know what I am doing wrong?


See More: Problem with IF(AND formula

February 11, 2011 at 16:53:29
It's tough to say what you are doing wrong since we don't know what you are trying to do.

However, I can say that whatever it is that you are trying to do, your formula makes no sense.

The structure of an IF function is:

IF(logical_test, value_if_true, value_if_false)

Let's look at a very simple example. (Bear with me, I assume you already know this)

=IF(S2="Time", "S2 Equals Time", G2" )

logical test - Does S2 equal time?

value_if_true - S2 Equals Time

value_if_false - G2

Makes sense, right?

Now let's look at your formula:


logical test - Does S2 equal time?

value_if_true - AND(G2="NNR","NNR",IF(S2="TIme",AND(G2="RRR","RRR"),G2))

value_if_false - You don't have a value_if_false so the formula will default to returning FALSE if S2 <> "Time"

In addition, let's look at your value_if_true

The AND function will return TRUE if all of the logical arguments are TRUE and return FALSE if one or more of them are FALSE.

Your logical arguments are:

logical1 - G2="NNR"
logical2 - "NNR"
logical3 - IF(S2="TIme",AND(G2="RRR","RRR"),G2)

As soon as you put Time is S2 and the Excel tries to evaluate this AND function it is going to throw up a #VALUE error since your logical2 can not be evaluated as TRUE or FALSE since it is not any kind of logical test.

To be completely honest, I think you got very lucky that this formula was actually accepted by Excel. I'd be willing to bet that you had to try multiple constructions before Excel accepted it.

Don't worry, tell us - in words - what you are trying to do and we'll see if we can help.

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

Report •
Related Solutions

Ask Question