Using Concatenate operator to overcome 7 nested IF's
 

Take the following formula:

=IF(R2450="calc",
IF((M2450-L2450=0)*(G2450<P2450),DATEDIF(A2450,G2450,"yd"),
IF((M2450-L2450=0)*(G2450P2450),DATEDIF(A2450,P2450,"yd"),
IF((M2450-L2450=1)*(G2450Q2450)*(A2450P2450),DATEDIF(Q2450 ,G2450,"yd"),
IF((M2450-L2450=1)*(G2450Q2450),DATEDIF(A2450,P2450,"yd")+D ATEDIF(Q2450,G2450,"yd"),
IF((M2450-L2450=1)*(G2450<Q2450)*(A2450<O2450),DATEDIF(A2450 ,P2450,"yd"),
IF((M2450-L24501)*(A2450<P2450),DATEDIF(A2450,P2450,"yd")+3 68)))))),0)

This does what I want except there is one more variable I need to meet,
namely:
=IF((M2448-L2448=1)*(G2448Q2448)*(A2448P2448),DATEDIF(Q2448 ,G2448,"yd"))

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="calc",
IF((M2448-L2448=0)*(G2448<P2448),DATEDIF(A2448,G2448,"yd")&
IF((M2448-L2448=0)*(G2448P2448),DATEDIF(A2448,P2448,"yd")&
IF((M2448-L2448=1)*(G2448Q2448)*(A2448P2448),DATEDIF(Q2448 ,G2448,"yd")&
IF((M2448-L2448=1)*(G2448Q2448),DATEDIF(A2448,P2448,"yd")+D ATEDIF(Q2448,G2448,"yd")&
IF((M2448-L2448=1)*(G2448<Q2448)*(A2448<O2448),DATEDIF(A2448 ,P2448,"yd")&
IF((M2448-L24481)*(A2448<P2448),DATEDIF(A2448,P2448,"yd")+3 68&
IF((M2448-L24481)*(A2448P2448),500)))))),0))

Now this returns a 0, whereas
=IF((M2448-L2448=1)*(G2448Q2448)*(A2448P2448),DATEDIF(Q2448 ,G2448,"yd"))
by itself returns the correct number, so since I have never done this
this way I am thinking perhaps this will work:

=IF(R2449="calc"&
IF((M2449-L2449=0)*(G2449<P2449),DATEDIF(A2449,G2449,"yd"),
IF((M2449-L2449=0)*(G2449P2449),DATEDIF(A2449,P2449,"yd"),
IF((M2449-L2449=1)*(G2449Q2449)*(A2449P2449),DATEDIF(Q2449 ,G2449,"yd"),
IF((M2449-L2449=1)*(G2449Q2449),DATEDIF(A2449,P2449,"yd")+D ATEDIF(Q2449,G2449,"yd"),
IF((M2449-L2449=1)*(G2449<Q2449)*(A2449<O2449),DATEDIF(A2449 ,P2449,"yd"),
IF((M2449-L24491)*(A2449<P2449),DATEDIF(A2449,P2449,"yd")+3 68)))))),0)

but this returns "FALSE" so I play a bit more and enter this:

=IF(R2448="calc",
IF((M2448-L2448=0)*(G2448<P2448),DATEDIF(A2448,G2448,"yd"))&
IF((M2448-L2448=0)*(G2448P2448),DATEDIF(A2448,P2448,"yd"))&
IF((M2448-L2448=1)*(G2448Q2448)*(A2448P2448),DATEDIF(Q2448 ,G2448,"yd"))&
IF((M2448-L2448=1)*(G2448Q2448),DATEDIF(A2448,P2448,"yd")+D ATEDIF(Q2448,G2448,"yd"))&
IF((M2448-L2448=1)*(G2448<Q2448)*(A2448<O2448),DATEDIF(A2448 ,P2448,"yd"))&
IF((M2448-L24481)*(A2448<P2448),DATEDIF(A2448,P2448,"yd"))+ 368&
IF((M2448-L24481)*(A2448P2448),500),0)

and this returns a #NUM! error

Will the & operator work for what I want, or am I just missing something
(again)


cheers & TIA!

Using Concatenate operator to overcome 7 nested IF's
 

I think this is what he means

=IF(R2448<"calc",0,
IF((M2448-L2448=0)*(G2448<P2448),DATEDIF(A2448,G2448,"yd")," ")&
IF((M2448-L2448=0)*(G2448P2448),DATEDIF(A2448,P2448,"yd")," ")&
IF((M2448-L2448=1)*(G2448Q2448)*(A2448P2448),DATEDIF(Q2448 ,G2448,"yd"),"")
&
IF((M2448-L2448=1)*(G2448Q2448),DATEDIF(A2448,P2448,"yd")+D ATEDIF(Q2448,G24
48,"yd"),"")&
IF((M2448-L2448=1)*(G2448<Q2448)*(A2448<O2448),DATEDIF(A2448 ,P2448,"yd"),"")
&
IF((M2448-L24481)*(A2448<P2448),DATEDIF(A2448,P2448,"yd")+3 68,"")&
IF((M2448-L24481)*(A2448P2448),500,""))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"TG" wrote in message news:iee%f.49359$K11.16463@clgrps12...
Take the following formula:

=IF(R2450="calc",
IF((M2450-L2450=0)*(G2450<P2450),DATEDIF(A2450,G2450,"yd"),
IF((M2450-L2450=0)*(G2450P2450),DATEDIF(A2450,P2450,"yd"),
IF((M2450-L2450=1)*(G2450Q2450)*(A2450P2450),DATEDIF(Q2450 ,G2450,"yd"),

IF((M2450-L2450=1)*(G2450Q2450),DATEDIF(A2450,P2450,"yd")+D ATEDIF(Q2450,G24
50,"yd"),
IF((M2450-L2450=1)*(G2450<Q2450)*(A2450<O2450),DATEDIF(A2450 ,P2450,"yd"),
IF((M2450-L24501)*(A2450<P2450),DATEDIF(A2450,P2450,"yd")+3 68)))))),0)

This does what I want except there is one more variable I need to meet,
namely:
=IF((M2448-L2448=1)*(G2448Q2448)*(A2448P2448),DATEDIF(Q2448 ,G2448,"yd"))

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="calc",
IF((M2448-L2448=0)*(G2448<P2448),DATEDIF(A2448,G2448,"yd")&
IF((M2448-L2448=0)*(G2448P2448),DATEDIF(A2448,P2448,"yd")&
IF((M2448-L2448=1)*(G2448Q2448)*(A2448P2448),DATEDIF(Q2448 ,G2448,"yd")&

IF((M2448-L2448=1)*(G2448Q2448),DATEDIF(A2448,P2448,"yd")+D ATEDIF(Q2448,G24
48,"yd")&
IF((M2448-L2448=1)*(G2448<Q2448)*(A2448<O2448),DATEDIF(A2448 ,P2448,"yd")&
IF((M2448-L24481)*(A2448<P2448),DATEDIF(A2448,P2448,"yd")+3 68&
IF((M2448-L24481)*(A2448P2448),500)))))),0))

Now this returns a 0, whereas
=IF((M2448-L2448=1)*(G2448Q2448)*(A2448P2448),DATEDIF(Q2448 ,G2448,"yd"))
by itself returns the correct number, so since I have never done this
this way I am thinking perhaps this will work:

=IF(R2449="calc"&
IF((M2449-L2449=0)*(G2449<P2449),DATEDIF(A2449,G2449,"yd"),
IF((M2449-L2449=0)*(G2449P2449),DATEDIF(A2449,P2449,"yd"),
IF((M2449-L2449=1)*(G2449Q2449)*(A2449P2449),DATEDIF(Q2449 ,G2449,"yd"),

IF((M2449-L2449=1)*(G2449Q2449),DATEDIF(A2449,P2449,"yd")+D ATEDIF(Q2449,G24
49,"yd"),
IF((M2449-L2449=1)*(G2449<Q2449)*(A2449<O2449),DATEDIF(A2449 ,P2449,"yd"),
IF((M2449-L24491)*(A2449<P2449),DATEDIF(A2449,P2449,"yd")+3 68)))))),0)

but this returns "FALSE" so I play a bit more and enter this:

=IF(R2448="calc",
IF((M2448-L2448=0)*(G2448<P2448),DATEDIF(A2448,G2448,"yd"))&
IF((M2448-L2448=0)*(G2448P2448),DATEDIF(A2448,P2448,"yd"))&
IF((M2448-L2448=1)*(G2448Q2448)*(A2448P2448),DATEDIF(Q2448 ,G2448,"yd"))&

IF((M2448-L2448=1)*(G2448Q2448),DATEDIF(A2448,P2448,"yd")+D ATEDIF(Q2448,G24
48,"yd"))&
IF((M2448-L2448=1)*(G2448<Q2448)*(A2448<O2448),DATEDIF(A2448 ,P2448,"yd"))&
IF((M2448-L24481)*(A2448<P2448),DATEDIF(A2448,P2448,"yd"))+ 368&
IF((M2448-L24481)*(A2448P2448),500),0)

and this returns a #NUM! error

Will the & operator work for what I want, or am I just missing something
(again)


cheers & TIA!