software

Take the following formula:

=IF(R2450=quot;calcquot;,
IF((M2450-L2450=0)*(G2450lt;P2450),DATEDIF(A2450,G2450,quot;ydquot;),
IF((M2450-L2450=0)*(G2450gt;P2450),DATEDIF(A2450,P2450,quot;ydquot;),
IF((M2450-L2450=1)*(G2450gt;Q2450)*(A2450gt;P2450),DATEDIF(Q2450 ,G2450,quot;ydquot;),
IF((M2450-L2450=1)*(G2450gt;Q2450),DATEDIF(A2450,P2450,quot;ydquot;) D ATEDIF(Q2450,G2450,quot;ydquot;),
IF((M2450-L2450=1)*(G2450lt;Q2450)*(A2450lt;O2450),DATEDIF(A2450 ,P2450,quot;ydquot;),
IF((M2450-L2450gt;1)*(A2450lt;P2450),DATEDIF(A2450,P2450,quot;ydquot;) 3 68)))))),0)

This does what I want except there is one more variable I need to meet,
namely:
=IF((M2448-L2448=1)*(G2448gt;Q2448)*(A2448gt;P2448),DATEDIF(Q2448 ,G2448,quot;ydquot;))

but when I add this I go over the 7 limit, so after some research I
found the Spreadsheet Page by John Walkenbach where he suggested using
the concatenate function or operator to overcome this limit, so I tried
this:
=IF(R2448=quot;calcquot;,
IF((M2448-L2448=0)*(G2448lt;P2448),DATEDIF(A2448,G2448,quot;ydquot;)amp;
IF((M2448-L2448=0)*(G2448gt;P2448),DATEDIF(A2448,P2448,quot;ydquot;)amp;
IF((M2448-L2448=1)*(G2448gt;Q2448)*(A2448gt;P2448),DATEDIF(Q2448 ,G2448,quot;ydquot;)amp;
IF((M2448-L2448=1)*(G2448gt;Q2448),DATEDIF(A2448,P2448,quot;ydquot;) D ATEDIF(Q2448,G2448,quot;ydquot;)amp;
IF((M2448-L2448=1)*(G2448lt;Q2448)*(A2448lt;O2448),DATEDIF(A2448 ,P2448,quot;ydquot;)amp;
IF((M2448-L2448gt;1)*(A2448lt;P2448),DATEDIF(A2448,P2448,quot;ydquot;) 3 68amp;
IF((M2448-L2448gt;1)*(A2448gt;P2448),500)))))),0))

Now this returns a 0, whereas
=IF((M2448-L2448=1)*(G2448gt;Q2448)*(A2448gt;P2448),DATEDIF(Q2448 ,G2448,quot;ydquot;))
by itself returns the correct number, so since I have never done this
this way I am thinking perhaps this will work:

=IF(R2449=quot;calcquot;amp;
IF((M2449-L2449=0)*(G2449lt;P2449),DATEDIF(A2449,G2449,quot;ydquot;),
IF((M2449-L2449=0)*(G2449gt;P2449),DATEDIF(A2449,P2449,quot;ydquot;),
IF((M2449-L2449=1)*(G2449gt;Q2449)*(A2449gt;P2449),DATEDIF(Q2449 ,G2449,quot;ydquot;),
IF((M2449-L2449=1)*(G2449gt;Q2449),DATEDIF(A2449,P2449,quot;ydquot;) D ATEDIF(Q2449,G2449,quot;ydquot;),
IF((M2449-L2449=1)*(G2449lt;Q2449)*(A2449lt;O2449),DATEDIF(A2449 ,P2449,quot;ydquot;),
IF((M2449-L2449gt;1)*(A2449lt;P2449),DATEDIF(A2449,P2449,quot;ydquot;) 3 68)))))),0)

but this returns quot;FALSEquot; so I play a bit more and enter this:

=IF(R2448=quot;calcquot;,
IF((M2448-L2448=0)*(G2448lt;P2448),DATEDIF(A2448,G2448,quot;ydquot;))amp;
IF((M2448-L2448=0)*(G2448gt;P2448),DATEDIF(A2448,P2448,quot;ydquot;))amp;
IF((M2448-L2448=1)*(G2448gt;Q2448)*(A2448gt;P2448),DATEDIF(Q2448 ,G2448,quot;ydquot;))amp;
IF((M2448-L2448=1)*(G2448gt;Q2448),DATEDIF(A2448,P2448,quot;ydquot;) D ATEDIF(Q2448,G2448,quot;ydquot;))amp;
IF((M2448-L2448=1)*(G2448lt;Q2448)*(A2448lt;O2448),DATEDIF(A2448 ,P2448,quot;ydquot;))amp;
IF((M2448-L2448gt;1)*(A2448lt;P2448),DATEDIF(A2448,P2448,quot;ydquot;)) 368amp;
IF((M2448-L2448gt;1)*(A2448gt;P2448),500),0)

and this returns a #NUM! error

Will the amp; operator work for what I want, or am I just missing something
(again)cheers amp; TIA!

I think this is what he means

=IF(R2448lt;gt;quot;calcquot;,0,
IF((M2448-L2448=0)*(G2448lt;P2448),DATEDIF(A2448,G2448,quot;ydquot;),quot; quot;)amp;
IF((M2448-L2448=0)*(G2448gt;P2448),DATEDIF(A2448,P2448,quot;ydquot;),quot; quot;)amp;
IF((M2448-L2448=1)*(G2448gt;Q2448)*(A2448gt;P2448),DATEDIF(Q2448 ,G2448,quot;ydquot;),quot;quot;)
amp;
IF((M2448-L2448=1)*(G2448gt;Q2448),DATEDIF(A2448,P2448,quot;ydquot;) D ATEDIF(Q2448,G24
48,quot;ydquot;),quot;quot;)amp;
IF((M2448-L2448=1)*(G2448lt;Q2448)*(A2448lt;O2448),DATEDIF(A2448 ,P2448,quot;ydquot;),quot;quot;)
amp;
IF((M2448-L2448gt;1)*(A2448lt;P2448),DATEDIF(A2448,P2448,quot;ydquot;) 3 68,quot;quot;)amp;
IF((M2448-L2448gt;1)*(A2448gt;P2448),500,quot;quot;))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;TGquot; gt; wrote in message news:iee%f.49359$K11.16463@clgrps12...
gt; Take the following formula:
gt;
gt; =IF(R2450=quot;calcquot;,
gt; IF((M2450-L2450=0)*(G2450lt;P2450),DATEDIF(A2450,G2450,quot;ydquot;),
gt; IF((M2450-L2450=0)*(G2450gt;P2450),DATEDIF(A2450,P2450,quot;ydquot;),
gt; IF((M2450-L2450=1)*(G2450gt;Q2450)*(A2450gt;P2450),DATEDIF(Q2450 ,G2450,quot;ydquot;),
gt;
IF((M2450-L2450=1)*(G2450gt;Q2450),DATEDIF(A2450,P2450,quot;ydquot;) D ATEDIF(Q2450,G24
50,quot;ydquot;),
gt; IF((M2450-L2450=1)*(G2450lt;Q2450)*(A2450lt;O2450),DATEDIF(A2450 ,P2450,quot;ydquot;),
gt; IF((M2450-L2450gt;1)*(A2450lt;P2450),DATEDIF(A2450,P2450,quot;ydquot;) 3 68)))))),0)
gt;
gt; This does what I want except there is one more variable I need to meet,
gt; namely:
gt; =IF((M2448-L2448=1)*(G2448gt;Q2448)*(A2448gt;P2448),DATEDIF(Q2448 ,G2448,quot;ydquot;))
gt;
gt; but when I add this I go over the 7 limit, so after some research I
gt; found the Spreadsheet Page by John Walkenbach where he suggested using
gt; the concatenate function or operator to overcome this limit, so I tried
gt; this:
gt; =IF(R2448=quot;calcquot;,
gt; IF((M2448-L2448=0)*(G2448lt;P2448),DATEDIF(A2448,G2448,quot;ydquot;)amp;
gt; IF((M2448-L2448=0)*(G2448gt;P2448),DATEDIF(A2448,P2448,quot;ydquot;)amp;
gt; IF((M2448-L2448=1)*(G2448gt;Q2448)*(A2448gt;P2448),DATEDIF(Q2448 ,G2448,quot;ydquot;)amp;
gt;
IF((M2448-L2448=1)*(G2448gt;Q2448),DATEDIF(A2448,P2448,quot;ydquot;) D ATEDIF(Q2448,G24
48,quot;ydquot;)amp;
gt; IF((M2448-L2448=1)*(G2448lt;Q2448)*(A2448lt;O2448),DATEDIF(A2448 ,P2448,quot;ydquot;)amp;
gt; IF((M2448-L2448gt;1)*(A2448lt;P2448),DATEDIF(A2448,P2448,quot;ydquot;) 3 68amp;
gt; IF((M2448-L2448gt;1)*(A2448gt;P2448),500)))))),0))
gt;
gt; Now this returns a 0, whereas
gt; =IF((M2448-L2448=1)*(G2448gt;Q2448)*(A2448gt;P2448),DATEDIF(Q2448 ,G2448,quot;ydquot;))
gt; by itself returns the correct number, so since I have never done this
gt; this way I am thinking perhaps this will work:
gt;
gt; =IF(R2449=quot;calcquot;amp;
gt; IF((M2449-L2449=0)*(G2449lt;P2449),DATEDIF(A2449,G2449,quot;ydquot;),
gt; IF((M2449-L2449=0)*(G2449gt;P2449),DATEDIF(A2449,P2449,quot;ydquot;),
gt; IF((M2449-L2449=1)*(G2449gt;Q2449)*(A2449gt;P2449),DATEDIF(Q2449 ,G2449,quot;ydquot;),
gt;
IF((M2449-L2449=1)*(G2449gt;Q2449),DATEDIF(A2449,P2449,quot;ydquot;) D ATEDIF(Q2449,G24
49,quot;ydquot;),
gt; IF((M2449-L2449=1)*(G2449lt;Q2449)*(A2449lt;O2449),DATEDIF(A2449 ,P2449,quot;ydquot;),
gt; IF((M2449-L2449gt;1)*(A2449lt;P2449),DATEDIF(A2449,P2449,quot;ydquot;) 3 68)))))),0)
gt;
gt; but this returns quot;FALSEquot; so I play a bit more and enter this:
gt;
gt; =IF(R2448=quot;calcquot;,
gt; IF((M2448-L2448=0)*(G2448lt;P2448),DATEDIF(A2448,G2448,quot;ydquot;))amp;
gt; IF((M2448-L2448=0)*(G2448gt;P2448),DATEDIF(A2448,P2448,quot;ydquot;))amp;
gt; IF((M2448-L2448=1)*(G2448gt;Q2448)*(A2448gt;P2448),DATEDIF(Q2448 ,G2448,quot;ydquot;))amp;
gt;
IF((M2448-L2448=1)*(G2448gt;Q2448),DATEDIF(A2448,P2448,quot;ydquot;) D ATEDIF(Q2448,G24
48,quot;ydquot;))amp;
gt; IF((M2448-L2448=1)*(G2448lt;Q2448)*(A2448lt;O2448),DATEDIF(A2448 ,P2448,quot;ydquot;))amp;
gt; IF((M2448-L2448gt;1)*(A2448lt;P2448),DATEDIF(A2448,P2448,quot;ydquot;)) 368amp;
gt; IF((M2448-L2448gt;1)*(A2448gt;P2448),500),0)
gt;
gt; and this returns a #NUM! error
gt;
gt; Will the amp; operator work for what I want, or am I just missing something
gt; (again)
gt;
gt;
gt; cheers amp; TIA!