Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nested Subtotals in Excel 2002 | Excel Discussion (Misc queries) | |||
Why are my nested sub-totals are displaying incorrectly? | Excel Discussion (Misc queries) | |||
Using Concatenate inside a vlookup | Excel Worksheet Functions | |||
Nested IF statements | Excel Worksheet Functions | |||
Concatenate cells without specifying/writing cell address individually | Excel Discussion (Misc queries) |