| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
