proper way to nest AND & OR with IF?
 

cells a1,b1,c1,d1 have the numbers 1,2,3,4 in them

I want to enter an IF statement that says:

IF (sum of a1 + b1 3 AND the value in c1=3) OR the value in c1 = 4,
show this_val, if not show this_other_val


thats simplifying it,my actual formula is more like:

=IF(AND(M2448-L2448=1,G2448Q2448),OR(A2448P2448),DATEDIF(Q2448 ,G2448,"yd"))

and it shows TRUE, but if I try and get it to show a value such as 1 or
0 I get an error.

Obviously I am missing something here, but I cant see it!

I used to do this in 123 all the time, and I thought the proper syntax
in excel was IF(OR(AND(arg1,arg2),arg3),do_this,or_this) - but that
doesnt work!


all suggestions appreciated!


TIA

proper way to nest AND & OR with IF?
 

=IF(OR(AND(M2448-L2448=1,G2448Q2448),A2448P2448),DATEDIF(Q2448,G2 448,"yd")
,"")


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"TG" wrote in message news:6cU_f.3492$Zl.376@edtnps89...
cells a1,b1,c1,d1 have the numbers 1,2,3,4 in them

I want to enter an IF statement that says:

IF (sum of a1 + b1 3 AND the value in c1=3) OR the value in c1 = 4,
show this_val, if not show this_other_val


thats simplifying it,my actual formula is more like:


=IF(AND(M2448-L2448=1,G2448Q2448),OR(A2448P2448),DATEDIF(Q2448 ,G2448,"yd")
)

and it shows TRUE, but if I try and get it to show a value such as 1 or
0 I get an error.

Obviously I am missing something here, but I cant see it!

I used to do this in 123 all the time, and I thought the proper syntax
in excel was IF(OR(AND(arg1,arg2),arg3),do_this,or_this) - but that
doesnt work!


all suggestions appreciated!


TIA

proper way to nest AND & OR with IF?
 

=IF(or(AND(M2448-L2448=1,G2448Q2448),A2448P2448),DATEDIF(Q2448,G2 448,"yd"),
false_result)


"TG" wrote:

cells a1,b1,c1,d1 have the numbers 1,2,3,4 in them

I want to enter an IF statement that says:

IF (sum of a1 + b1 3 AND the value in c1=3) OR the value in c1 = 4,
show this_val, if not show this_other_val


thats simplifying it,my actual formula is more like:

=IF(AND(M2448-L2448=1,G2448Q2448),OR(A2448P2448),DATEDIF(Q2448 ,G2448,"yd"))

and it shows TRUE, but if I try and get it to show a value such as 1 or
0 I get an error.

Obviously I am missing something here, but I cant see it!

I used to do this in 123 all the time, and I thought the proper syntax
in excel was IF(OR(AND(arg1,arg2),arg3),do_this,or_this) - but that
doesnt work!


all suggestions appreciated!


TIA

proper way to nest AND & OR with IF?
 

Duke Carey wrote:
=IF(or(AND(M2448-L2448=1,G2448Q2448),A2448P2448),DATEDIF(Q2448,G2 448,"yd"),
false_result)


"TG" wrote:


cells a1,b1,c1,d1 have the numbers 1,2,3,4 in them

I want to enter an IF statement that says:

IF (sum of a1 + b1 3 AND the value in c1=3) OR the value in c1 = 4,
show this_val, if not show this_other_val


thats simplifying it,my actual formula is more like:

=IF(AND(M2448-L2448=1,G2448Q2448),OR(A2448P2448),DATEDIF(Q2448 ,G2448,"yd"))

and it shows TRUE, but if I try and get it to show a value such as 1 or
0 I get an error.

Obviously I am missing something here, but I cant see it!

I used to do this in 123 all the time, and I thought the proper syntax
in excel was IF(OR(AND(arg1,arg2),arg3),do_this,or_this) - but that
doesnt work!


all suggestions appreciated!


TIA

OK, that works on its own fine, but when I try to nest this into an
existing IF statement, I get an error, here is the original statement:

=IF(R2448="calc",
IF(AND(M2448-L2448=0,G2448<P2448),DATEDIF(A2448,G2448,"yd"),
IF(AND(M2448-L2448=0,G2448P2448),DATEDIF(A2448,P2448,"yd"),
IF(AND(M2448-L2448=1,G2448Q2448),DATEDIF(A2448,P2448,"yd")+DAT EDIF(Q2448,G2448,"yd"),
IF(AND(M2448-L2448=1,G2448<Q2448,A2448<O2448),DATEDIF(A2448,P24 48,"yd"),
IF(AND(M2448-L24481,A2448<P2448),DATEDIF(A2448,P2448,"yd")+368 ,
IF(AND(M2448-L24481,A2448P2448),500)))))),0)

this one works, but when I change it to this:

=IF(R2448="calc",
IF(AND(M2448-L2448=0,G2448<P2448),DATEDIF(A2448,G2448,"yd"),
IF(AND(M2448-L2448=0,G2448P2448),DATEDIF(A2448,P2448,"yd"),
IF(AND(M2448-L2448=1,G2448Q2448),DATEDIF(A2448,P2448,"yd")+DAT EDIF(Q2448,G2448,"yd"),
IF(AND(M2448-L2448=1,G2448<Q2448,A2448<O2448),DATEDIF(A2448,P24 48,"yd"),
IF(or(AND(M2448-L2448=1,G2448Q2448),A2448P2448),DATEDIF(Q2448,G2 448,"yd"),
IF(AND(M2448-L24481,A2448<P2448),DATEDIF(A2448,P2448,"yd")+368 ,
IF(AND(M2448-L24481,A2448P2448),500)))))),0)

I get an error saying that there is an error in the formula and it
highlights the AND word in the last IF statement.


Or am I hitting the limit for nested IF's? I thought it was 7 nested,
not including the starting IF?

proper way to nest AND & OR with IF?
 

I think you have too many nested IF's. You can only have 7.

Also, you can use * as an AND, and + as an or.

IF((M2448-L2448=1)*(G2448Q2448)+(A2448P2448),TRUE,FALSE)

The logical/boolean values TRUE and FALSE are converted to the numbers 1,
and 0 when you apply a mathematical operator to them. Also, any non-zero
number is understood as TRUE.

"TG" wrote:

Duke Carey wrote:
=IF(or(AND(M2448-L2448=1,G2448Q2448),A2448P2448),DATEDIF(Q2448,G2 448,"yd"),
false_result)


"TG" wrote:


cells a1,b1,c1,d1 have the numbers 1,2,3,4 in them

I want to enter an IF statement that says:

IF (sum of a1 + b1 3 AND the value in c1=3) OR the value in c1 = 4,
show this_val, if not show this_other_val


thats simplifying it,my actual formula is more like:

=IF(AND(M2448-L2448=1,G2448Q2448),OR(A2448P2448),DATEDIF(Q2448 ,G2448,"yd"))

and it shows TRUE, but if I try and get it to show a value such as 1 or
0 I get an error.

Obviously I am missing something here, but I cant see it!

I used to do this in 123 all the time, and I thought the proper syntax
in excel was IF(OR(AND(arg1,arg2),arg3),do_this,or_this) - but that
doesnt work!


all suggestions appreciated!


TIA

OK, that works on its own fine, but when I try to nest this into an
existing IF statement, I get an error, here is the original statement:

=IF(R2448="calc",
IF(AND(M2448-L2448=0,G2448<P2448),DATEDIF(A2448,G2448,"yd"),
IF(AND(M2448-L2448=0,G2448P2448),DATEDIF(A2448,P2448,"yd"),
IF(AND(M2448-L2448=1,G2448Q2448),DATEDIF(A2448,P2448,"yd")+DAT EDIF(Q2448,G2448,"yd"),
IF(AND(M2448-L2448=1,G2448<Q2448,A2448<O2448),DATEDIF(A2448,P24 48,"yd"),
IF(AND(M2448-L24481,A2448<P2448),DATEDIF(A2448,P2448,"yd")+368 ,
IF(AND(M2448-L24481,A2448P2448),500)))))),0)

this one works, but when I change it to this:

=IF(R2448="calc",
IF(AND(M2448-L2448=0,G2448<P2448),DATEDIF(A2448,G2448,"yd"),
IF(AND(M2448-L2448=0,G2448P2448),DATEDIF(A2448,P2448,"yd"),
IF(AND(M2448-L2448=1,G2448Q2448),DATEDIF(A2448,P2448,"yd")+DAT EDIF(Q2448,G2448,"yd"),
IF(AND(M2448-L2448=1,G2448<Q2448,A2448<O2448),DATEDIF(A2448,P24 48,"yd"),
IF(or(AND(M2448-L2448=1,G2448Q2448),A2448P2448),DATEDIF(Q2448,G2 448,"yd"),
IF(AND(M2448-L24481,A2448<P2448),DATEDIF(A2448,P2448,"yd")+368 ,
IF(AND(M2448-L24481,A2448P2448),500)))))),0)

I get an error saying that there is an error in the formula and it
highlights the AND word in the last IF statement.


Or am I hitting the limit for nested IF's? I thought it was 7 nested,
not including the starting IF?