combining AND with IF 2

[YewNork]

In my mind the test "IF(VAR1>1 AND VAR2=0,TRUE,FALSE)" makes sense, but I find that Excel won't accept the AND command within the logical test of IF. Instead I've worked around it by nesting the them "IF(VAR1>1,IF(VAR2=0,TRUE,FALSE),FALSE)".

After the 8 or so AND's that are required in one test it becomes very messy.

Is there a better way?

 

[xlbo]

YewNork - yup there certainly is:
IF(VAR1>1 AND VAR2=0,TRUE,FALSE)
would be
=IF(AND(VAR1>1,VAR2=0),TRUE,FALSE)
the syntax would be the same for an OR statement
so to test for either of 2 conditions where each consition needs 2 tests, something like this would be in order

=IF(OR(AND(VAR1>0,VAR2=0),AND(VAR3<0,VAR4=0)),TRUE,FALSE)
This will evaluate to TRUE if either VAR1>0 AND VAR2=0 OR Var3<0 AND VAR4=0

Logical nesting....very very powerful but will mess with your head ;-) Rgds
Geoff
&quot;Some cause happiness wherever they go; others whenever they go.&quot;
-Oscar Wilde

 

[mikevh]

You don't even need the &quot;IF's&quot; in the above, since the
AND and OR functions by themselves already return TRUE or FALSE values.
For example,

=IF(AND(VAR1>1,VAR2=0),TRUE,FALSE)
can be rewritten as simply
=AND(VAR1>1,VAR2=0)

and =IF(OR(AND(VAR1>0,VAR2=0),AND VAR3<0,VAR4=0)),TRUE,FALSE)
can be rewritten as simply
=OR(AND(VAR1>0,VAR2=0),AND(VAR3<0,VAR4=0))

 

[xlbo]

yeh but I'm assuming that some kinda function will be utilised depending on a TRUE or FALSE answer so the IFs are necessary to enable that ;-)
The TRU,FALSE just represents the 2nd (test=TRUE) and 3rd (Test = FALSE) arguments in the IF statement Rgds
Geoff
&quot;Some cause happiness wherever they go; others whenever they go.&quot;
-Oscar Wilde

 

[mikevh]

Okay, fair enough.