Using Concatenate operator to overcome > 7 nested IF's

[TG]

Take the following formula:

=IF(R2450="calc",
IF((M2450-L2450=0)*(G2450<P2450),DATEDIF(A2450,G2450,"yd"),
IF((M2450-L2450=0)*(G2450>P2450),DATEDIF(A2450,P2450,"yd"),
IF((M2450-L2450=1)*(G2450>Q2450)*(A2450>P2450),DATEDIF(Q2450,G2450,"yd"),
IF((M2450-L2450=1)*(G2450>Q2450),DATEDIF(A2450,P2450,"yd")+DATEDIF(Q2450,G2450,"yd"),
IF((M2450-L2450=1)*(G2450<Q2450)*(A2450<O2450),DATEDIF(A2450,P2450,"yd"),
IF((M2450-L2450>1)*(A2450<P2450),DATEDIF(A2450,P2450,"yd")+368)))))),0)

This does what I want except there is one more variable I need to meet,
namely:
=IF((M2448-L2448=1)*(G2448>Q2448)*(A2448>P2448),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)*(G2448>P2448),DATEDIF(A2448,P2448,"yd")&
IF((M2448-L2448=1)*(G2448>Q2448)*(A2448>P2448),DATEDIF(Q2448,G2448,"yd")&
IF((M2448-L2448=1)*(G2448>Q2448),DATEDIF(A2448,P2448,"yd")+DATEDIF(Q2448,G2448,"yd")&
IF((M2448-L2448=1)*(G2448<Q2448)*(A2448<O2448),DATEDIF(A2448,P2448,"yd")&
IF((M2448-L2448>1)*(A2448<P2448),DATEDIF(A2448,P2448,"yd")+368&
IF((M2448-L2448>1)*(A2448>P2448),500)))))),0))

Now this returns a 0, whereas
=IF((M2448-L2448=1)*(G2448>Q2448)*(A2448>P2448),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)*(G2449>P2449),DATEDIF(A2449,P2449,"yd"),
IF((M2449-L2449=1)*(G2449>Q2449)*(A2449>P2449),DATEDIF(Q2449,G2449,"yd"),
IF((M2449-L2449=1)*(G2449>Q2449),DATEDIF(A2449,P2449,"yd")+DATEDIF(Q2449,G2449,"yd"),
IF((M2449-L2449=1)*(G2449<Q2449)*(A2449<O2449),DATEDIF(A2449,P2449,"yd"),
IF((M2449-L2449>1)*(A2449<P2449),DATEDIF(A2449,P2449,"yd")+368)))))),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)*(G2448>P2448),DATEDIF(A2448,P2448,"yd"))&
IF((M2448-L2448=1)*(G2448>Q2448)*(A2448>P2448),DATEDIF(Q2448,G2448,"yd"))&
IF((M2448-L2448=1)*(G2448>Q2448),DATEDIF(A2448,P2448,"yd")+DATEDIF(Q2448,G2448,"yd"))&
IF((M2448-L2448=1)*(G2448<Q2448)*(A2448<O2448),DATEDIF(A2448,P2448,"yd"))&
IF((M2448-L2448>1)*(A2448<P2448),DATEDIF(A2448,P2448,"yd"))+368&
IF((M2448-L2448>1)*(A2448>P2448),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!

 

[Bob Phillips]

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)*(G2448>P2448),DATEDIF(A2448,P2448,"yd"),"")&
IF((M2448-L2448=1)*(G2448>Q2448)*(A2448>P2448),DATEDIF(Q2448,G2448,"yd"),"")
&
IF((M2448-L2448=1)*(G2448>Q2448),DATEDIF(A2448,P2448,"yd")+DATEDIF(Q2448,G24
48,"yd"),"")&
IF((M2448-L2448=1)*(G2448<Q2448)*(A2448<O2448),DATEDIF(A2448,P2448,"yd"),"")
&
IF((M2448-L2448>1)*(A2448<P2448),DATEDIF(A2448,P2448,"yd")+368,"")&
IF((M2448-L2448>1)*(A2448>P2448),500,""))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)