software

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 gt; 3 AND the value in c1=3) OR the value in c1 = 4,
show this_val, if not show this_other_valthats simplifying it,my actual formula is more like:

=IF(AND(M2448-L2448=1,G2448gt;Q2448),OR(A2448gt;P2448),DATEDIF(Q2448 ,G2448,quot;ydquot;))

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

=IF(OR(AND(M2448-L2448=1,G2448gt;Q2448),A2448gt;P2448),DATEDIF(Q2448,G2 448,quot;ydquot;)
,quot;quot;)--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;TGquot; gt; wrote in message news:6cU_f.3492$Zl.376@edtnps89...
gt; cells a1,b1,c1,d1 have the numbers 1,2,3,4 in them
gt;
gt; I want to enter an IF statement that says:
gt;
gt; IF (sum of a1 b1 gt; 3 AND the value in c1=3) OR the value in c1 = 4,
gt; show this_val, if not show this_other_val
gt;
gt;
gt; thats simplifying it,my actual formula is more like:
gt;
gt;
=IF(AND(M2448-L2448=1,G2448gt;Q2448),OR(A2448gt;P2448),DATEDIF(Q2448 ,G2448,quot;ydquot;)
)
gt;
gt; and it shows TRUE, but if I try and get it to show a value such as 1 or
gt; 0 I get an error.
gt;
gt; Obviously I am missing something here, but I cant see it!
gt;
gt; I used to do this in 123 all the time, and I thought the proper syntax
gt; in excel was IF(OR(AND(arg1,arg2),arg3),do_this,or_this) - but that
gt; doesnt work!
gt;
gt;
gt; all suggestions appreciated!
gt;
gt;
gt; TIA
=IF(or(AND(M2448-L2448=1,G2448gt;Q2448),A2448gt;P2448),DATEDIF(Q2448,G2 448,quot;ydquot;),
false_result)quot;TGquot; wrote:

gt; cells a1,b1,c1,d1 have the numbers 1,2,3,4 in them
gt;
gt; I want to enter an IF statement that says:
gt;
gt; IF (sum of a1 b1 gt; 3 AND the value in c1=3) OR the value in c1 = 4,
gt; show this_val, if not show this_other_val
gt;
gt;
gt; thats simplifying it,my actual formula is more like:
gt;
gt; =IF(AND(M2448-L2448=1,G2448gt;Q2448),OR(A2448gt;P2448),DATEDIF(Q2448 ,G2448,quot;ydquot;))
gt;
gt; and it shows TRUE, but if I try and get it to show a value such as 1 or
gt; 0 I get an error.
gt;
gt; Obviously I am missing something here, but I cant see it!
gt;
gt; I used to do this in 123 all the time, and I thought the proper syntax
gt; in excel was IF(OR(AND(arg1,arg2),arg3),do_this,or_this) - but that
gt; doesnt work!
gt;
gt;
gt; all suggestions appreciated!
gt;
gt;
gt; TIA
gt;

Duke Carey wrote:
gt; =IF(or(AND(M2448-L2448=1,G2448gt;Q2448),A2448gt;P2448),DATEDIF(Q2448,G2 448,quot;ydquot;),
gt; false_result)
gt;
gt;
gt; quot;TGquot; wrote:
gt;
gt;
gt;gt;cells a1,b1,c1,d1 have the numbers 1,2,3,4 in them
gt;gt;
gt;gt;I want to enter an IF statement that says:
gt;gt;
gt;gt;IF (sum of a1 b1 gt; 3 AND the value in c1=3) OR the value in c1 = 4,
gt;gt;show this_val, if not show this_other_val
gt;gt;
gt;gt;
gt;gt;thats simplifying it,my actual formula is more like:
gt;gt;
gt;gt;=IF(AND(M2448-L2448=1,G2448gt;Q2448),OR(A2448gt;P2448),DATEDIF(Q2448 ,G2448,quot;ydquot;))
gt;gt;
gt;gt;and it shows TRUE, but if I try and get it to show a value such as 1 or
gt;gt;0 I get an error.
gt;gt;
gt;gt;Obviously I am missing something here, but I cant see it!
gt;gt;
gt;gt;I used to do this in 123 all the time, and I thought the proper syntax
gt;gt;in excel was IF(OR(AND(arg1,arg2),arg3),do_this,or_this) - but that
gt;gt;doesnt work!
gt;gt;
gt;gt;
gt;gt;all suggestions appreciated!
gt;gt;
gt;gt;
gt;gt;TIA
gt;gt;
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=quot;calcquot;,
IF(AND(M2448-L2448=0,G2448lt;P2448),DATEDIF(A2448,G2448,quot;ydquot;),
IF(AND(M2448-L2448=0,G2448gt;P2448),DATEDIF(A2448,P2448,quot;ydquot;),
IF(AND(M2448-L2448=1,G2448gt;Q2448),DATEDIF(A2448,P2448,quot;ydquot;) DAT EDIF(Q2448,G2448,quot;ydquot;),
IF(AND(M2448-L2448=1,G2448lt;Q2448,A2448lt;O2448),DATEDIF(A2448,P24 48,quot;ydquot;),
IF(AND(M2448-L2448gt;1,A2448lt;P2448),DATEDIF(A2448,P2448,quot;ydquot;) 368 ,
IF(AND(M2448-L2448gt;1,A2448gt;P2448),500)))))),0)

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

=IF(R2448=quot;calcquot;,
IF(AND(M2448-L2448=0,G2448lt;P2448),DATEDIF(A2448,G2448,quot;ydquot;),
IF(AND(M2448-L2448=0,G2448gt;P2448),DATEDIF(A2448,P2448,quot;ydquot;),
IF(AND(M2448-L2448=1,G2448gt;Q2448),DATEDIF(A2448,P2448,quot;ydquot;) DAT EDIF(Q2448,G2448,quot;ydquot;),
IF(AND(M2448-L2448=1,G2448lt;Q2448,A2448lt;O2448),DATEDIF(A2448,P24 48,quot;ydquot;),
IF(or(AND(M2448-L2448=1,G2448gt;Q2448),A2448gt;P2448),DATEDIF(Q2448,G2 448,quot;ydquot;),
IF(AND(M2448-L2448gt;1,A2448lt;P2448),DATEDIF(A2448,P2448,quot;ydquot;) 368 ,
IF(AND(M2448-L2448gt;1,A2448gt;P2448),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?

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)*(G2448gt;Q2448) (A2448gt;P2448),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.

quot;TGquot; wrote:

gt; Duke Carey wrote:
gt; gt; =IF(or(AND(M2448-L2448=1,G2448gt;Q2448),A2448gt;P2448),DATEDIF(Q2448,G2 448,quot;ydquot;),
gt; gt; false_result)
gt; gt;
gt; gt;
gt; gt; quot;TGquot; wrote:
gt; gt;
gt; gt;
gt; gt;gt;cells a1,b1,c1,d1 have the numbers 1,2,3,4 in them
gt; gt;gt;
gt; gt;gt;I want to enter an IF statement that says:
gt; gt;gt;
gt; gt;gt;IF (sum of a1 b1 gt; 3 AND the value in c1=3) OR the value in c1 = 4,
gt; gt;gt;show this_val, if not show this_other_val
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;thats simplifying it,my actual formula is more like:
gt; gt;gt;
gt; gt;gt;=IF(AND(M2448-L2448=1,G2448gt;Q2448),OR(A2448gt;P2448),DATEDIF(Q2448 ,G2448,quot;ydquot;))
gt; gt;gt;
gt; gt;gt;and it shows TRUE, but if I try and get it to show a value such as 1 or
gt; gt;gt;0 I get an error.
gt; gt;gt;
gt; gt;gt;Obviously I am missing something here, but I cant see it!
gt; gt;gt;
gt; gt;gt;I used to do this in 123 all the time, and I thought the proper syntax
gt; gt;gt;in excel was IF(OR(AND(arg1,arg2),arg3),do_this,or_this) - but that
gt; gt;gt;doesnt work!
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;all suggestions appreciated!
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;TIA
gt; gt;gt;
gt; OK, that works on its own fine, but when I try to nest this into an
gt; existing IF statement, I get an error, here is the original statement:
gt;
gt; =IF(R2448=quot;calcquot;,
gt; IF(AND(M2448-L2448=0,G2448lt;P2448),DATEDIF(A2448,G2448,quot;ydquot;),
gt; IF(AND(M2448-L2448=0,G2448gt;P2448),DATEDIF(A2448,P2448,quot;ydquot;),
gt; IF(AND(M2448-L2448=1,G2448gt;Q2448),DATEDIF(A2448,P2448,quot;ydquot;) DAT EDIF(Q2448,G2448,quot;ydquot;),
gt; IF(AND(M2448-L2448=1,G2448lt;Q2448,A2448lt;O2448),DATEDIF(A2448,P24 48,quot;ydquot;),
gt; IF(AND(M2448-L2448gt;1,A2448lt;P2448),DATEDIF(A2448,P2448,quot;ydquot;) 368 ,
gt; IF(AND(M2448-L2448gt;1,A2448gt;P2448),500)))))),0)
gt;
gt; this one works, but when I change it to this:
gt;
gt; =IF(R2448=quot;calcquot;,
gt; IF(AND(M2448-L2448=0,G2448lt;P2448),DATEDIF(A2448,G2448,quot;ydquot;),
gt; IF(AND(M2448-L2448=0,G2448gt;P2448),DATEDIF(A2448,P2448,quot;ydquot;),
gt; IF(AND(M2448-L2448=1,G2448gt;Q2448),DATEDIF(A2448,P2448,quot;ydquot;) DAT EDIF(Q2448,G2448,quot;ydquot;),
gt; IF(AND(M2448-L2448=1,G2448lt;Q2448,A2448lt;O2448),DATEDIF(A2448,P24 48,quot;ydquot;),
gt; IF(or(AND(M2448-L2448=1,G2448gt;Q2448),A2448gt;P2448),DATEDIF(Q2448,G2 448,quot;ydquot;),
gt; IF(AND(M2448-L2448gt;1,A2448lt;P2448),DATEDIF(A2448,P2448,quot;ydquot;) 368 ,
gt; IF(AND(M2448-L2448gt;1,A2448gt;P2448),500)))))),0)
gt;
gt; I get an error saying that there is an error in the formula and it
gt; highlights the AND word in the last IF statement.
gt;
gt;
gt; Or am I hitting the limit for nested IF's? I thought it was 7 nested,
gt; not including the starting IF?
gt;
gt;
gt;
gt;