Nested IF AND functions.

[Deb]

I have not used Excel functions for a while and apparently can't figure it
out now. I
need to have 5 logical tests to be true in an IF statement. Does anyone know
the correct format with commas and parenthesis in the right place? Thanks, Deb

 

[Dave Peterson]

=if(and(a1="ok",b1=13,c42="xxy"),"all true","at least one false")

(I stopped before I got to 5 tests.)

 

[ShaneDevenshire]

Hi,

This is actually and interesting issue, you could do this at least two way
and there are advantages to both:

As Dave has suggested
IF(AND(test1,test2,test3,test4,test5),"all true")

where a test is something like A1>10

Another solution would be
=IF(test1,IF(test2,IF(test3,IF(test4,IF(test5,"all true")))))

My question to the newsgroup - In both cases I have left the formulas so the
will evaluate to FALSE if not all conditions are met. If test1 fails in the
first case will test2 be executed? In the second case if test1 fails the
calculation if over. So will form 1 run faster than form 2 at least in the
scenario where the test1 fails?