The Longest formula ever

A

AciD

New Member
Joined
May 2, 2002
Messages
16
Hi, here it is :

=IF(IF(INDEX(temp.xls!A:F;SUM(MATCH("EENU";temp.xls!A:A;0);MATCH("BF304";OFFSET(temp.xls!A1;MATCH("EENU";temp.xls!A:A;0)-1;0;MATCH("FCLI";temp.xls!A:A;0)-MATCH("EENU";temp.xls!A:A;0)+1;1);0))-1;5)<>0;5;6)=5;INDEX(temp.xls!A:F;SUM(MATCH("EENU";temp.xls!A:A;0);MATCH("BF304";OFFSET(temp.xls!A1;EQUIV("EENU";temp.xls!A:A;0)-1;0;MATCH("FCLI";temp.xls!A:A;0)-MATCH("EENU";temp.xls!A:A;0)+1;1);0))-1;IF(INDEX(temp.xls!A:F;SUM(MATCH("EENU";temp.xls!A:A;0);MATCH("BF304";OFFSET(temp.xls!A1;MATCH("EENU";temp.xls!A:A;0)-1;0;MATCH("FCLI";temp.xls!A:A;0)-MATCH("EENU";temp.xls!A:A;0)+1;1);0))-1;5)<>0;5;6));-INDEX(temp.xls!A:F;SUM(MATCH("EENU";temp.xls!A:A;0);MATCH("BF304";OFFSET(temp.xls!A1;MATCH("EENU";temp.xls!A:A;0)-1;0;MATCH("FCLI";temp.xls!A:A;0)-MATCH("EENU";temp.xls!A:A;0)+1;1);0))-1;IF(INDEX(temp.xls!A:F;SUM(MATCH("EENU";temp.xls!A:A;0);MATCH("BF304";OFFSET(temp.xls!A1;MATCH("EENU";temp.xls!A:A;0)-1;0;MATCH("FCLI";temp.xls!A:A;0)-MATCH("EENU";temp.xls!A:A;0)+1;1);0))-1;5)<>0;5;6)))

My pb is i'm searching the value "BF304" with this formula but when it can't find it, it print #N/A.. so i've found a formula to convert #N/A into "0" which is more better (sorry i haven't found the exact translation for type.erreur) :

=IF(TYPE.ERREUR(all_the_upper_formula)="7";"0";"all_the_upper_formula")

the pb is the overall formula is way to big for excel :/

any idea ?

thx.
 
Sort by date Sort by votes
On 2002-05-12 08:11, AciD wrote:
"It seems you're looking for the value bf304 in column A. It also seems you have 2 other columns for debit and credit: Which columns are these?

What is the significance/relevance of bf304 being in between EENU and FCLI, apparently also values that occur in A?"

yes i'm looking for bf304 in column A, the 2 others columns are : Debit in E and Credit in F.

in fact, in my column A i have several part : management, production etc...(named like MNGT, PROD, EENU, etc..). In each part, i can have some account, like bf304, bf514.

That's why i can't do a global search in the column A, i need to define some borders. In this case, EENU and FCLI are my borders.

My pb is, i can't divide the formula in other column coze i need to print the whole thing at the end :/

and the *real* pb is, when i search for some reference like bf344 (or whatever), and when this reference doesn't exist (it happen merely often), it return "#N/A" error instead of a "0"...

How can i replace thoses errors by 0, because i need to sum some of thoses result...
Click to expand...

Summing a range that also includes #N/A's is not a problem.

Given that B2:B7 houses:

{#N/A;
60;
80;
#N/A;
"";
90}

=SUMIF(B:B,"<>#N/A")

or simply

=SUMIF(B2:B7,"<>#N/A")

will produce the desired total.

Howver, I'm in particular interested whether a simplified formula for retrieval can be devised.

Not sure about the layout of your data, but the following (see the figure) I hope looks like yours. Hope also that the approach might be of some value to you.<CENTER><TABLE ALIGN=CENTER BORDER=1><TR><TD BGCOLOR=#0C266B COLSPAN=11><FONT COLOR=WHITE>Microsoft Excel - aaCreditDebitLookup AciD.xls</FONT></TD></TR><TR><TD BGCOLOR=#D4D0C8 COLSPAN=11>File(<U>F</U>) Edit(<U>E</U>) View(<U>V</U>) Insert(<U>I</U>) Options(<U>O</U>) Tools(<U>T</U>) Data(<U>D</U>) Window(<U>W</U>) Help(<U>H</U>)</TD></TR><TR><TD ALIGN=CENTER COLSPAN=2 BGCOLOR=White>K9</TD><TD ALIGN=CENTER BGCOLOR=#D4D0C8 >=</TD><TD COLSPAN=8 BGCOLOR=White></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER> </TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>A</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>B</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>C</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>D</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>E</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>F</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>G</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>H</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>I</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>J</TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>1</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>Account</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>Credit</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>Debit</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>EENU</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>FCLI</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>bf304</FONT></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>2</TD><TD BGCOLOR=#CCFFFF> </TD><TD BGCOLOR=#CCFFFF> </TD><TD BGCOLOR=#CCFFFF> </TD><TD BGCOLOR=#CCFFFF> </TD><TD BGCOLOR=#CCFFFF> </TD><TD BGCOLOR=#CCFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFF99 ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=MATCH(H1,A:A,0)')><FONT FACE=Arial COLOR=#000000>5</FONT></A></TD><TD BGCOLOR=#FFFF99 ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=MATCH(I1,A:A,0)')><FONT FACE=Arial COLOR=#000000>9</FONT></A></TD><TD BGCOLOR=#FFFF99 ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=IF(COUNTIF(OFFSET($A$2,H2-1,0,I2-(H2+1),1),J1),SUM(VLOOKUP(J1,OFFSET($A$2,H2-1,0,I2-(H2+1),5),5,0),-VLOOKUP(J1,OFFSET($A$2,H2-1,0,I2-(H2+1),6),6,0)),0)')><FONT FACE=Arial COLOR=#000000>-45</FONT></A></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>3</TD><TD BGCOLOR=#CCFFFF> </TD><TD BGCOLOR=#CCFFFF> </TD><TD BGCOLOR=#CCFFFF> </TD><TD BGCOLOR=#CCFFFF> </TD><TD BGCOLOR=#CCFFFF> </TD><TD BGCOLOR=#CCFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>4</TD><TD BGCOLOR=#CCFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>bf304</FONT></TD><TD BGCOLOR=#CCFFFF> </TD><TD BGCOLOR=#CCFFFF> </TD><TD BGCOLOR=#CCFFFF> </TD><TD BGCOLOR=#CCFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>60</FONT></TD><TD BGCOLOR=#CCFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>5</TD><TD BGCOLOR=#CCFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>EENU</FONT></TD><TD BGCOLOR=#CCFFFF> </TD><TD BGCOLOR=#CCFFFF> </TD><TD BGCOLOR=#CCFFFF> </TD><TD BGCOLOR=#CCFFFF> </TD><TD BGCOLOR=#CCFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>6</TD><TD BGCOLOR=#CCFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>ax465</FONT></TD><TD BGCOLOR=#CCFFFF> </TD><TD BGCOLOR=#CCFFFF> </TD><TD BGCOLOR=#CCFFFF> </TD><TD BGCOLOR=#CCFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>70</FONT></TD><TD BGCOLOR=#CCFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>7</TD><TD BGCOLOR=#CCFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>zx506</FONT></TD><TD BGCOLOR=#CCFFFF> </TD><TD BGCOLOR=#CCFFFF> </TD><TD BGCOLOR=#CCFFFF> </TD><TD BGCOLOR=#CCFFFF> </TD><TD BGCOLOR=#CCFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>68</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>8</TD><TD BGCOLOR=#CCFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>bf304</FONT></TD><TD BGCOLOR=#CCFFFF> </TD><TD BGCOLOR=#CCFFFF> </TD><TD BGCOLOR=#CCFFFF> </TD><TD BGCOLOR=#CCFFFF> </TD><TD BGCOLOR=#CCFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>45</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>9</TD><TD BGCOLOR=#CCFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>FCLI</FONT></TD><TD BGCOLOR=#CCFFFF> </TD><TD BGCOLOR=#CCFFFF> </TD><TD BGCOLOR=#CCFFFF> </TD><TD BGCOLOR=#CCFFFF> </TD><TD BGCOLOR=#CCFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>10</TD><TD BGCOLOR=#CCFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>ax465</FONT></TD><TD BGCOLOR=#CCFFFF> </TD><TD BGCOLOR=#CCFFFF> </TD><TD BGCOLOR=#CCFFFF> </TD><TD BGCOLOR=#CCFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>90</FONT></TD><TD BGCOLOR=#CCFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD COLSPAN=11><U>Lookup</U></TD></TR></TABLE><FONT COLOR=#339966>You can see the formula of cells only click each above hyperlinks</FONT><FONT COLOR=#339966 SIZE=1>The above image was automatically written by HtmlMakerVer1.12</FONT><FONT COLOR=#339966 SIZE=1>If you want this code,<A HREF=mailto:corosuke@chan.co.jp>click here</A> and Colo will email the file to you.</FONT></CENTER>

H1 and I1 houses the range markers you described.

Formulas in H2 and I2,

=MATCH(H1,A:A,0)and

=MATCH(I1,A:A,0)

helps to construct the range of interest in which to seek the lookup value that is in J1.

J2 houses the main formula that fetches associated credit and/or debit values:

=IF(COUNTIF(OFFSET($A$2,H2-1,0,I2-(H2+1),1),J1),SUM(VLOOKUP(J1,OFFSET($A$2,H2-1,0,I2-(H2+1),5),5,0),-VLOOKUP(J1,OFFSET($A$2,H2-1,0,I2-(H2+1),6),6,0)),0)

Aladin
This message was edited by Aladin Akyurek on 2002-05-12 09:58
 
Upvote 0

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
How about this bad boy ?

=(IF(PE8>31,"",(IF(KL8=0,"",(IF(JZ8=0,"",(IF(JN8=0,"",(IF(MW8>0,(IF(MW8=0,"HRS",(IF(JB8=0,"",(IF(MJ8>0,(IF(JN8-KL8=31,((PE8*24)-((24-MW8)*31)),(IF(JN8-KL8=30,((PE8*24)-((24-MW8)*30)),(IF(JN8-KL8=29,((PE8*24)-((24-MW8)*29)),(IF(JN8-KL8=28,((PE8*24)-((24-MW8)*28)),(IF(JN8-KL8=27,((PE8*24)-((24-MW8)*27)),(IF(JN8-KL8=26,((PE8*24)-((24-MW8)*26)),(IF(JN8-KL8=25,((PE8*24)-((24-MW8)*25)),(IF(JN8-KL8=24,((PE8*24)-((24-MW8)*24)),(IF(JN8-KL8=23,((PE8*24)-((24-MW8)*23)),(IF(JN8-KL8=22,((PE8*24)-((24-MW8)*22)),(IF(JN8-KL8=21,((PE8*24)-((24-MW8)*21)),(IF(JN8-KL8=20,((PE8*24)-((24-MW8)*20)),(IF(JN8-KL8=19,((PE8*24)-((24-MW8)*19)),(IF(JN8-KL8=18,((PE8*24)-((24-MW8)*18)),(IF(JN8-KL8=17,((PE8*24)-((24-MW8)*17)),(IF(JN8-KL8=16,((PE8*24)-((24-MW8)*16)),(IF(JN8-KL8=15,((PE8*24)-((24-MW8)*15)),(IF(JN8-KL8=14,((PE8*24)-((24-MW8)*14)),(IF(JN8-KL8=13,((PE8*24)-((24-MW8)*13)),(IF(JN8-KL8=12,((PE8*24)-((24-MW8)*12)),(IF(JN8-KL8=11,((PE8*24)-((24-MW8)*11)),(IF(JN8-KL8=10,((PE8*24)-((24-MW8)*10)),(IF(JN8-KL8=9,((PE8*24)-((24-MW8)*9)),(IF(JN8-KL8=8,((PE8*24)-((24-MW8)*8)),(IF(JN8-KL8=7,((PE8*24)-((24-MW8)*7)),(IF(JN8-KL8=6,((PE8*24)-((24-MW8)*6)),(IF(JN8-KL8=5,((PE8*24)-((24-MW8)*5)),(IF(JN8-KL8=4,((PE8*24)-((24-MW8)*4)),(IF(JN8-KL8=3,((PE8*24)-((24-MW8)*3)),(IF(JN8-KL8=2,((PE8*24)-((24-MW8)*2)),(IF(JN8-KL8=1,((PE8*24)-(24-MW8)),(PE8*24)))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))-(MW8*MJ8),(IF(JN8-KL8=31,((PE8*24)-((24-MW8)*31)),(IF(JN8-KL8=30,((PE8*24)-((24-MW8)*30)),(IF(JN8-KL8=29,((PE8*24)-((24-MW8)*29)),(IF(JN8-KL8=28,((PE8*24)-((24-MW8)*28)),(IF(JN8-KL8=27,((PE8*24)-((24-MW8)*27)),(IF(JN8-KL8=26,((PE8*24)-((24-MW8)*26)),(IF(JN8-KL8=25,((PE8*24)-((24-MW8)*25)),(IF(JN8-KL8=24,((PE8*24)-((24-MW8)*24)),(IF(JN8-KL8=23,((PE8*24)-((24-MW8)*23)),(IF(JN8-KL8=22,((PE8*24)-((24-MW8)*22)),(IF(JN8-KL8=21,((PE8*24)-((24-MW8)*21)),(IF(JN8-KL8=20,((PE8*24)-((24-MW8)*20)),(IF(JN8-KL8=19,((PE8*24)-((24-MW8)*19)),(IF(JN8-KL8=18,((PE8*24)-((24-MW8)*18)),(IF(JN8-KL8=17,((PE8*24)-((24-MW8)*17)),(IF(JN8-KL8=16,((PE8*24)-((24-MW8)*16)),(IF(JN8-KL8=15,((PE8*24)-((24-MW8)*15)),(IF(JN8-KL8=14,((PE8*24)-((24-MW8)*14)),(IF(JN8-KL8=13,((PE8*24)-((24-MW8)*13)),(IF(JN8-KL8=12,((PE8*24)-((24-MW8)*12)),(IF(JN8-KL8=11,((PE8*24)-((24-MW8)*11)),(IF(JN8-KL8=10,((PE8*24)-((24-MW8)*10)),(IF(JN8-KL8=9,((PE8*24)-((24-MW8)*9)),(IF(JN8-KL8=8,((PE8*24)-((24-MW8)*8)),(IF(JN8-KL8=7,((PE8*24)-((24-MW8)*7)),(IF(JN8-KL8=6,((PE8*24)-((24-MW8)*6)),(IF(JN8-KL8=5,((PE8*24)-((24-MW8)*5)),(IF(JN8-KL8=4,((PE8*24)-((24-MW8)*4)),(IF(JN8-KL8=3,((PE8*24)-((24-MW8)*3)),(IF(JN8-KL8=2,((PE8*24)-((24-MW8)*2)),(IF(JN8-KL8=1,((PE8*24)-(24-MW8)),(PE8*24))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))),(IF($JB$4=0,"HRS",(IF(JB8=0,"",(IF(MJ8>0,(IF(JN8-KL8=31,((PE8*24)-((24-$JB$4)*31)),(IF(JN8-KL8=30,((PE8*24)-((24-$JB$4)*30)),(IF(JN8-KL8=29,((PE8*24)-((24-$JB$4)*29)),(IF(JN8-KL8=28,((PE8*24)-((24-$JB$4)*28)),(IF(JN8-KL8=27,((PE8*24)-((24-$JB$4)*27)),(IF(JN8-KL8=26,((PE8*24)-((24-$JB$4)*26)),(IF(JN8-KL8=25,((PE8*24)-((24-$JB$4)*25)),(IF(JN8-KL8=24,((PE8*24)-((24-$JB$4)*24)),(IF(JN8-KL8=23,((PE8*24)-((24-$JB$4)*23)),(IF(JN8-KL8=22,((PE8*24)-((24-$JB$4)*22)),(IF(JN8-KL8=21,((PE8*24)-((24-$JB$4)*21)),(IF(JN8-KL8=20,((PE8*24)-((24-$JB$4)*20)),(IF(JN8-KL8=19,((PE8*24)-((24-$JB$4)*19)),(IF(JN8-KL8=18,((PE8*24)-((24-$JB$4)*18)),(IF(JN8-KL8=17,((PE8*24)-((24-$JB$4)*17)),(IF(JN8-KL8=16,((PE8*24)-((24-$JB$4)*16)),(IF(JN8-KL8=15,((PE8*24)-((24-$JB$4)*15)),(IF(JN8-KL8=14,((PE8*24)-((24-$JB$4)*14)),(IF(JN8-KL8=13,((PE8*24)-((24-$JB$4)*13)),(IF(JN8-KL8=12,((PE8*24)-((24-$JB$4)*12)),(IF(JN8-KL8=11,((PE8*24)-((24-$JB$4)*11)),(IF(JN8-KL8=10,((PE8*24)-((24-$JB$4)*10)),(IF(JN8-KL8=9,((PE8*24)-((24-$JB$4)*9)),(IF(JN8-KL8=8,((PE8*24)-((24-$JB$4)*8)),(IF(JN8-KL8=7,((PE8*24)-((24-$JB$4)*7)),(IF(JN8-KL8=6,((PE8*24)-((24-$JB$4)*6)),(IF(JN8-KL8=5,((PE8*24)-((24-$JB$4)*5)),(IF(JN8-KL8=4,((PE8*24)-((24-$JB$4)*4)),(IF(JN8-KL8=3,((PE8*24)-((24-$JB$4)*3)),(IF(JN8-KL8=2,((PE8*24)-((24-$JB$4)*2)),(IF(JN8-KL8=1,((PE8*24)-(24-$JB$4)),(PE8*24)))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))-($JB$4*MJ8),(IF(JN8-KL8=31,((PE8*24)-((24-$JB$4)*31)),(IF(JN8-KL8=30,((PE8*24)-((24-$JB$4)*30)),(IF(JN8-KL8=29,((PE8*24)-((24-$JB$4)*29)),(IF(JN8-KL8=28,((PE8*24)-((24-$JB$4)*28)),(IF(JN8-KL8=27,((PE8*24)-((24-$JB$4)*27)),(IF(JN8-KL8=26,((PE8*24)-((24-$JB$4)*26)),(IF(JN8-KL8=25,((PE8*24)-((24-$JB$4)*25)),(IF(JN8-KL8=24,((PE8*24)-((24-$JB$4)*24)),(IF(JN8-KL8=23,((PE8*24)-((24-$JB$4)*23)),(IF(JN8-KL8=22,((PE8*24)-((24-$JB$4)*22)),(IF(JN8-KL8=21,((PE8*24)-((24-$JB$4)*21)),(IF(JN8-KL8=20,((PE8*24)-((24-$JB$4)*20)),(IF(JN8-KL8=19,((PE8*24)-((24-$JB$4)*19)),(IF(JN8-KL8=18,((PE8*24)-((24-$JB$4)*18)),(IF(JN8-KL8=17,((PE8*24)-((24-$JB$4)*17)),(IF(JN8-KL8=16,((PE8*24)-((24-$JB$4)*16)),(IF(JN8-KL8=15,((PE8*24)-((24-$JB$4)*15)),(IF(JN8-KL8=14,((PE8*24)-((24-$JB$4)*14)),(IF(JN8-KL8=13,((PE8*24)-((24-$JB$4)*13)),(IF(JN8-KL8=12,((PE8*24)-((24-$JB$4)*12)),(IF(JN8-KL8=11,((PE8*24)-((24-$JB$4)*11)),(IF(JN8-KL8=10,((PE8*24)-((24-$JB$4)*10)),(IF(JN8-KL8=9,((PE8*24)-((24-$JB$4)*9)),(IF(JN8-KL8=8,((PE8*24)-((24-$JB$4)*8)),(IF(JN8-KL8=7,((PE8*24)-((24-$JB$4)*7)),(IF(JN8-KL8=6,((PE8*24)-((24-$JB$4)*6)),(IF(JN8-KL8=5,((PE8*24)-((24-$JB$4)*5)),(IF(JN8-KL8=4,((PE8*24)-((24-$JB$4)*4)),(IF(JN8-KL8=3,((PE8*24)-((24-$JB$4)*3)),(IF(JN8-KL8=2,((PE8*24)-((24-$JB$4)*2)),(IF(JN8-KL8=1,((PE8*24)-(24-$JB$4)),(PE8*24)))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))
 
Upvote 0
That's a symptom of a terminal disease.
 
Upvote 0
=(IF(PE8>31,"",(IF(KL8=0,"",(IF(JZ8=0,"",(IF(JN8=0,"",(IF(MW8>0,(IF(MW8=0,"HRS",(IF(JB8=0,"",(IF(MJ8>0,(IF(JN8-KL8=31,((PE8*24)-((24-MW8)*31)),(IF(JN8-KL8=30,((PE8*24)-((24-MW8)*30)),(IF(JN8-KL8=29,((PE8*24)-((24-MW8)*29)),(IF(JN8-KL8=28,((PE8*24)-((24-MW8)*28)),(IF(JN8-KL8=27,((PE8*24)-((24-MW8)*27)),(IF(JN8-KL8=26,((PE8*24)-((24-MW8)*26)),(IF(JN8-KL8=25,((PE8*24)-((24-MW8)*25)),(IF(JN8-KL8=24,((PE8*24)-((24-MW8)*24)),(IF(JN8-KL8=23,((PE8*24)-((24-MW8)*23)),(IF(JN8-KL8=22,((PE8*24)-((24-MW8)*22)),(IF(JN8-KL8=21,((PE8*24)-((24-MW8)*21)),(IF(JN8-KL8=20,((PE8*24)-((24-MW8)*20)),(IF(JN8-KL8=19,((PE8*24)-((24-MW8)*19)),(IF(JN8-KL8=18,((PE8*24)-((24-MW8)*18)),(IF(JN8-KL8=17,((PE8*24)-((24-MW8)*17)),(IF(JN8-KL8=16,((PE8*24)-((24-MW8)*16)),(IF(JN8-KL8=15,((PE8*24)-((24-MW8)*15)),(IF(JN8-KL8=14,((PE8*24)-((24-MW8)*14)),(IF(JN8-KL8=13,((PE8*24)-((24-MW8)*13)),(IF(JN8-KL8=12,((PE8*24)-((24-MW8)*12)),(IF(JN8-KL8=11,((PE8*24)-((24-MW8)*11)),(IF(JN8-KL8=10,((PE8*24)-((24-MW8)*10)),(IF(JN8-KL8=9,((PE8*24)-((24-MW8)*9)),(IF(JN8-KL8=8,((PE8*24)-((24-MW8)*8)),(IF(JN8-KL8=7,((PE8*24)-((24-MW8)*7)),(IF(JN8-KL8=6,((PE8*24)-((24-MW8)*6)),(IF(JN8-KL8=5,((PE8*24)-((24-MW8)*5)),(IF(JN8-KL8=4,((PE8*24)-((24-MW8)*4)),(IF(JN8-KL8=3,((PE8*24)-((24-MW8)*3)),(IF(JN8-KL8=2,((PE8*24)-((24-MW8)*2)),(IF(JN8-KL8=1,((PE8*24)-(24-MW8)),(PE8*24)))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))-(MW8*MJ8),(IF(JN8-KL8=31,((PE8*24)-((24-MW8)*31)),(IF(JN8-KL8=30,((PE8*24)-((24-MW8)*30)),(IF(JN8-KL8=29,((PE8*24)-((24-MW8)*29)),(IF(JN8-KL8=28,((PE8*24)-((24-MW8)*28)),(IF(JN8-KL8=27,((PE8*24)-((24-MW8)*27)),(IF(JN8-KL8=26,((PE8*24)-((24-MW8)*26)),(IF(JN8-KL8=25,((PE8*24)-((24-MW8)*25)),(IF(JN8-KL8=24,((PE8*24)-((24-MW8)*24)),(IF(JN8-KL8=23,((PE8*24)-((24-MW8)*23)),(IF(JN8-KL8=22,((PE8*24)-((24-MW8)*22)),(IF(JN8-KL8=21,((PE8*24)-((24-MW8)*21)),(IF(JN8-KL8=20,((PE8*24)-((24-MW8)*20)),(IF(JN8-KL8=19,((PE8*24)-((24-MW8)*19)),(IF(JN8-KL8=18,((PE8*24)-((24-MW8)*18)),(IF(JN8-KL8=17,((PE8*24)-((24-MW8)*17)),(IF(JN8-KL8=16,((PE8*24)-((24-MW8)*16)),(IF(JN8-KL8=15,((PE8*24)-((24-MW8)*15)),(IF(JN8-KL8=14,((PE8*24)-((24-MW8)*14)),(IF(JN8-KL8=13,((PE8*24)-((24-MW8)*13)),(IF(JN8-KL8=12,((PE8*24)-((24-MW8)*12)),(IF(JN8-KL8=11,((PE8*24)-((24-MW8)*11)),(IF(JN8-KL8=10,((PE8*24)-((24-MW8)*10)),(IF(JN8-KL8=9,((PE8*24)-((24-MW8)*9)),(IF(JN8-KL8=8,((PE8*24)-((24-MW8)*8)),(IF(JN8-KL8=7,((PE8*24)-((24-MW8)*7)),(IF(JN8-KL8=6,((PE8*24)-((24-MW8)*6)),(IF(JN8-KL8=5,((PE8*24)-((24-MW8)*5)),(IF(JN8-KL8=4,((PE8*24)-((24-MW8)*4)),(IF(JN8-KL8=3,((PE8*24)-((24-MW8)*3)),(IF(JN8-KL8=2,((PE8*24)-((24-MW8)*2)),(IF(JN8-KL8=1,((PE8*24)-(24-MW8)),(PE8*24))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))),(IF($JB$4=0,"HRS",(IF(JB8=0,"",(IF(MJ8>0,(IF(JN8-KL8=31,((PE8*24)-((24-$JB$4)*31)),(IF(JN8-KL8=30,((PE8*24)-((24-$JB$4)*30)),(IF(JN8-KL8=29,((PE8*24)-((24-$JB$4)*29)),(IF(JN8-KL8=28,((PE8*24)-((24-$JB$4)*28)),(IF(JN8-KL8=27,((PE8*24)-((24-$JB$4)*27)),(IF(JN8-KL8=26,((PE8*24)-((24-$JB$4)*26)),(IF(JN8-KL8=25,((PE8*24)-((24-$JB$4)*25)),(IF(JN8-KL8=24,((PE8*24)-((24-$JB$4)*24)),(IF(JN8-KL8=23,((PE8*24)-((24-$JB$4)*23)),(IF(JN8-KL8=22,((PE8*24)-((24-$JB$4)*22)),(IF(JN8-KL8=21,((PE8*24)-((24-$JB$4)*21)),(IF(JN8-KL8=20,((PE8*24)-((24-$JB$4)*20)),(IF(JN8-KL8=19,((PE8*24)-((24-$JB$4)*19)),(IF(JN8-KL8=18,((PE8*24)-((24-$JB$4)*18)),(IF(JN8-KL8=17,((PE8*24)-((24-$JB$4)*17)),(IF(JN8-KL8=16,((PE8*24)-((24-$JB$4)*16)),(IF(JN8-KL8=15,((PE8*24)-((24-$JB$4)*15)),(IF(JN8-KL8=14,((PE8*24)-((24-$JB$4)*14)),(IF(JN8-KL8=13,((PE8*24)-((24-$JB$4)*13)),(IF(JN8-KL8=12,((PE8*24)-((24-$JB$4)*12)),(IF(JN8-KL8=11,((PE8*24)-((24-$JB$4)*11)),(IF(JN8-KL8=10,((PE8*24)-((24-$JB$4)*10)),(IF(JN8-KL8=9,((PE8*24)-((24-$JB$4)*9)),(IF(JN8-KL8=8,((PE8*24)-((24-$JB$4)*8)),(IF(JN8-KL8=7,((PE8*24)-((24-$JB$4)*7)),(IF(JN8-KL8=6,((PE8*24)-((24-$JB$4)*6)),(IF(JN8-KL8=5,((PE8*24)-((24-$JB$4)*5)),(IF(JN8-KL8=4,((PE8*24)-((24-$JB$4)*4)),(IF(JN8-KL8=3,((PE8*24)-((24-$JB$4)*3)),(IF(JN8-KL8=2,((PE8*24)-((24-$JB$4)*2)),(IF(JN8-KL8=1,((PE8*24)-(24-$JB$4)),(PE8*24)))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))-($JB$4*MJ8),(IF(JN8-KL8=31,((PE8*24)-((24-$JB$4)*31)),(IF(JN8-KL8=30,((PE8*24)-((24-$JB$4)*30)),(IF(JN8-KL8=29,((PE8*24)-((24-$JB$4)*29)),(IF(JN8-KL8=28,((PE8*24)-((24-$JB$4)*28)),(IF(JN8-KL8=27,((PE8*24)-((24-$JB$4)*27)),(IF(JN8-KL8=26,((PE8*24)-((24-$JB$4)*26)),(IF(JN8-KL8=25,((PE8*24)-((24-$JB$4)*25)),(IF(JN8-KL8=24,((PE8*24)-((24-$JB$4)*24)),(IF(JN8-KL8=23,((PE8*24)-((24-$JB$4)*23)),(IF(JN8-KL8=22,((PE8*24)-((24-$JB$4)*22)),(IF(JN8-KL8=21,((PE8*24)-((24-$JB$4)*21)),(IF(JN8-KL8=20,((PE8*24)-((24-$JB$4)*20)),(IF(JN8-KL8=19,((PE8*24)-((24-$JB$4)*19)),(IF(JN8-KL8=18,((PE8*24)-((24-$JB$4)*18)),(IF(JN8-KL8=17,((PE8*24)-((24-$JB$4)*17)),(IF(JN8-KL8=16,((PE8*24)-((24-$JB$4)*16)),(IF(JN8-KL8=15,((PE8*24)-((24-$JB$4)*15)),(IF(JN8-KL8=14,((PE8*24)-((24-$JB$4)*14)),(IF(JN8-KL8=13,((PE8*24)-((24-$JB$4)*13)),(IF(JN8-KL8=12,((PE8*24)-((24-$JB$4)*12)),(IF(JN8-KL8=11,((PE8*24)-((24-$JB$4)*11)),(IF(JN8-KL8=10,((PE8*24)-((24-$JB$4)*10)),(IF(JN8-KL8=9,((PE8*24)-((24-$JB$4)*9)),(IF(JN8-KL8=8,((PE8*24)-((24-$JB$4)*8)),(IF(JN8-KL8=7,((PE8*24)-((24-$JB$4)*7)),(IF(JN8-KL8=6,((PE8*24)-((24-$JB$4)*6)),(IF(JN8-KL8=5,((PE8*24)-((24-$JB$4)*5)),(IF(JN8-KL8=4,((PE8*24)-((24-$JB$4)*4)),(IF(JN8-KL8=3,((PE8*24)-((24-$JB$4)*3)),(IF(JN8-KL8=2,((PE8*24)-((24-$JB$4)*2)),(IF(JN8-KL8=1,((PE8*24)-(24-$JB$4)),(PE8*24)))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))
Click to expand...
Simplifies to:
=42
 
Upvote 0
Maybe, but I'd bet it could be reduced to 5% of that.
 
Upvote 0
Wowzers lawprelim,

I think I just seen a glimps into the matrix...



That was like the memo Jerry McGuire wrote, then received applause from his peers, then was promptly canned. :ROFLMAO:
 
Upvote 0
AciD said:
Hi, here it is :

=IF(IF(INDEX(temp.xls!A:F;SUM(MATCH("EENU";temp.xls!A:A;0);MATCH("BF304";OFFSET(temp.xls!A1;MATCH("EENU";temp.xls!A:A;0)-1;0;MATCH("FCLI";temp.xls!A:A;0)-MATCH("EENU";temp.xls!A:A;0)+1;1);0))-1;5)<>0;5;6)=5;INDEX(temp.xls!A:F;SUM(MATCH("EENU";temp.xls!A:A;0);MATCH("BF304";OFFSET(temp.xls!A1;EQUIV("EENU";temp.xls!A:A;0)-1;0;MATCH("FCLI";temp.xls!A:A;0)-MATCH("EENU";temp.xls!A:A;0)+1;1);0))-1;IF(INDEX(temp.xls!A:F;SUM(MATCH("EENU";temp.xls!A:A;0);MATCH("BF304";OFFSET(temp.xls!A1;MATCH("EENU";temp.xls!A:A;0)-1;0;MATCH("FCLI";temp.xls!A:A;0)-MATCH("EENU";temp.xls!A:A;0)+1;1);0))-1;5)<>0;5;6));-INDEX(temp.xls!A:F;SUM(MATCH("EENU";temp.xls!A:A;0);MATCH("BF304";OFFSET(temp.xls!A1;MATCH("EENU";temp.xls!A:A;0)-1;0;MATCH("FCLI";temp.xls!A:A;0)-MATCH("EENU";temp.xls!A:A;0)+1;1);0))-1;IF(INDEX(temp.xls!A:F;SUM(MATCH("EENU";temp.xls!A:A;0);MATCH("BF304";OFFSET(temp.xls!A1;MATCH("EENU";temp.xls!A:A;0)-1;0;MATCH("FCLI";temp.xls!A:A;0)-MATCH("EENU";temp.xls!A:A;0)+1;1);0))-1;5)<>0;5;6)))

My pb is i'm searching the value "BF304" with this formula but when it can't find it, it print #N/A.. so i've found a formula to convert #N/A into "0" which is more better (sorry i haven't found the exact translation for type.erreur) :

=IF(TYPE.ERREUR(all_the_upper_formula)="7";"0";"all_the_upper_formula")

the pb is the overall formula is way to big for excel :/

any idea ?

thx.
Click to expand...
Here is my longest excel formula =IF(COUNTA($C$4),IF(COUNTA(B20),IF(COUNTA(C20),IF(COUNTA($D$16),IF(COUNTA(F20),IF(C20<=(B20+13),IF(AND($D$8>=(B20),$D$8<=(C20)),((NETWORKDAYS($D$8,C20,0)*$D$16/10)*(($C$7/26.0833)/$D$16)*F20),IF(AND($F$8>=(B20),$F$8<=(C20)),((((NETWORKDAYS($F$8,C20,0)*$D$16/10)*($E$7/26.0833)/$D$16)*F20)+(((NETWORKDAYS(B20,($F$8-1),0))*($D$16/10)))*(($C$7/26.0833)/$D$16)*F20),IF(AND($H$8>=(B20),$H$8<=(C20)),((((NETWORKDAYS($H$8,C20,0)*$D$16/10)*($G$7/26.0833)/$D$16)*F20)+(((NETWORKDAYS(B20,($H$8-1),0))*($D$16/10)))*(($E$7/26.0833)/$D$16)*F20),IF(AND($J$8>=(B20),$J$8<=(C20)),((((NETWORKDAYS($J$8,C20,0)*$D$16/10)*($I$7/26.0833)/$D$16)*F20)+(((NETWORKDAYS(B20,($J$8-1),0))*($D$16/10)))*(($G$7/26.0833)/$D$16)*F20),IF(AND($L$8>=(B20),$L$8<=(C20)),((((NETWORKDAYS($L$8,C20,0)*$D$16/10)*($K$7/26.0833)/$D$16)*F20)+(((NETWORKDAYS(B20,($L$8-1),0))*($D$16/10)))*(($I$7/26.0833)/$D$16)*F20),IF(AND($D$11>=(B20),$D$11<=(C20)),((((NETWORKDAYS($D$11,C20,0)*$D$16/10)*($K$7/26.0833)/$D$16)*F20)+(((NETWORKDAYS(B20,($D$11-1),0))*($D$16/10)))*(($K$7/26.0833)/$D$16)*F20),IF(AND($F$11>=(B20),$F$11<=(C20)),((((NETWORKDAYS($F$11,C20,0)*$D$16/10)*($E$10/26.0833)/$D$16)*F20)+(((NETWORKDAYS(B20,($F$11-1),0))*($D$16/10)))*(($C$10/26.0833)/$D$16)*F20),IF(AND($H$11>=(B20),$H$11<=(C20)),((((NETWORKDAYS($H$11,C20,0)*$D$16/10)*($G$10/26.0833)/$D$16)*F20)+(((NETWORKDAYS(B20,($F$11-1),0))*($D$16/10)))*(($E$10/26.0833)/$D$16)*F20),IF(AND(B20>=DATE(2008,7,11),C20<=DATE(2009,6,25)),((NETWORKDAYS(B20,C20,0)*$D$16/10)*(($C$7/26.0833)/$D$16)*F20),IF(AND(B20>=DATE(2009,7,10),C20<=DATE(2010,6,24)),((NETWORKDAYS(B20,C20,0)*$D$16/10)*(($E$7/26.0833)/$D$16)*F20),IF(AND(B20>=DATE(2010,7,9),C20<=DATE(2011,6,23)),((NETWORKDAYS(B20,C20,0)*$D$16/10)*(($G$7/26.0833)/$D$16)*F20),IF(AND(B20>=DATE(2011,7,8),C20<=DATE(2012,6,21)),((NETWORKDAYS(B20,C20,0)*$D$16/10)*(($I$7/26.0833)/$D$16)*F20),IF(AND(B20>=DATE(2012,7,6),C20<=DATE(2013,6,20)),((NETWORKDAYS(B20,C20,0)*$D$16/10)*(($K$7/26.0833)/$D$16)*F20),IF(AND(B20>=DATE(2013,7,5),C20<=DATE(2014,6,19)),((NETWORKDAYS(B20,C20,0)*$D$16/10)*(($C$10/26.0833)/$D$16)*F20),IF(AND(B20>=DATE(2014,7,4),C20<=DATE(2015,6,18)),((NETWORKDAYS(B20,C20,0)*$D$16/10)*(($E$10/26.0833)/$D$16)*F20),IF(AND(B20>=DATE(2015,7,3),C20<=DATE(2016,6,30)),((NETWORKDAYS(B20,C20,0)*$D$16/10)*(($G$10/26.0833)/$D$16)*F20),"")))))))))))))))),""),""),""),""),""),"")
 
Upvote 0
Dangibb62 said:
Here is my longest excel formula =IF(COUNTA($C$4),IF(COUNTA(B20),IF(COUNTA(C20),IF(COUNTA($D$16),IF(COUNTA(F20),IF(C20<=(B20+13),IF(AND($D$8>=(B20),$D$8<=(C20)),((NETWORKDAYS($D$8,C20,0)*$D$16/10)*(($C$7/26.0833)/$D$16)*F20),IF(AND($F$8>=(B20),$F$8<=(C20)),((((NETWORKDAYS($F$8,C20,0)*$D$16/10)*($E$7/26.0833)/$D$16)*F20)+(((NETWORKDAYS(B20,($F$8-1),0))*($D$16/10)))*(($C$7/26.0833)/$D$16)*F20),IF(AND($H$8>=(B20),$H$8<=(C20)),((((NETWORKDAYS($H$8,C20,0)*$D$16/10)*($G$7/26.0833)/$D$16)*F20)+(((NETWORKDAYS(B20,($H$8-1),0))*($D$16/10)))*(($E$7/26.0833)/$D$16)*F20),IF(AND($J$8>=(B20),$J$8<=(C20)),((((NETWORKDAYS($J$8,C20,0)*$D$16/10)*($I$7/26.0833)/$D$16)*F20)+(((NETWORKDAYS(B20,($J$8-1),0))*($D$16/10)))*(($G$7/26.0833)/$D$16)*F20),IF(AND($L$8>=(B20),$L$8<=(C20)),((((NETWORKDAYS($L$8,C20,0)*$D$16/10)*($K$7/26.0833)/$D$16)*F20)+(((NETWORKDAYS(B20,($L$8-1),0))*($D$16/10)))*(($I$7/26.0833)/$D$16)*F20),IF(AND($D$11>=(B20),$D$11<=(C20)),((((NETWORKDAYS($D$11,C20,0)*$D$16/10)*($K$7/26.0833)/$D$16)*F20)+(((NETWORKDAYS(B20,($D$11-1),0))*($D$16/10)))*(($K$7/26.0833)/$D$16)*F20),IF(AND($F$11>=(B20),$F$11<=(C20)),((((NETWORKDAYS($F$11,C20,0)*$D$16/10)*($E$10/26.0833)/$D$16)*F20)+(((NETWORKDAYS(B20,($F$11-1),0))*($D$16/10)))*(($C$10/26.0833)/$D$16)*F20),IF(AND($H$11>=(B20),$H$11<=(C20)),((((NETWORKDAYS($H$11,C20,0)*$D$16/10)*($G$10/26.0833)/$D$16)*F20)+(((NETWORKDAYS(B20,($F$11-1),0))*($D$16/10)))*(($E$10/26.0833)/$D$16)*F20),IF(AND(B20>=DATE(2008,7,11),C20<=DATE(2009,6,25)),((NETWORKDAYS(B20,C20,0)*$D$16/10)*(($C$7/26.0833)/$D$16)*F20),IF(AND(B20>=DATE(2009,7,10),C20<=DATE(2010,6,24)),((NETWORKDAYS(B20,C20,0)*$D$16/10)*(($E$7/26.0833)/$D$16)*F20),IF(AND(B20>=DATE(2010,7,9),C20<=DATE(2011,6,23)),((NETWORKDAYS(B20,C20,0)*$D$16/10)*(($G$7/26.0833)/$D$16)*F20),IF(AND(B20>=DATE(2011,7,8),C20<=DATE(2012,6,21)),((NETWORKDAYS(B20,C20,0)*$D$16/10)*(($I$7/26.0833)/$D$16)*F20),IF(AND(B20>=DATE(2012,7,6),C20<=DATE(2013,6,20)),((NETWORKDAYS(B20,C20,0)*$D$16/10)*(($K$7/26.0833)/$D$16)*F20),IF(AND(B20>=DATE(2013,7,5),C20<=DATE(2014,6,19)),((NETWORKDAYS(B20,C20,0)*$D$16/10)*(($C$10/26.0833)/$D$16)*F20),IF(AND(B20>=DATE(2014,7,4),C20<=DATE(2015,6,18)),((NETWORKDAYS(B20,C20,0)*$D$16/10)*(($E$10/26.0833)/$D$16)*F20),IF(AND(B20>=DATE(2015,7,3),C20<=DATE(2016,6,30)),((NETWORKDAYS(B20,C20,0)*$D$16/10)*(($G$10/26.0833)/$D$16)*F20),"")))))))))))))))),""),""),""),""),""),"")
Click to expand...

Sorry I just added a bit for the current financial year that had an error in it, here is the correct one

=IF(COUNTA($C$4),IF(COUNTA(B29),IF(COUNTA(C29),IF(COUNTA($D$16),IF(COUNTA(F29),IF(C29<=(B29+13),IF(AND($D$8>=(B29),$D$8<=(C29)),((NETWORKDAYS($D$8,C29,0)*$D$16/10)*(($C$7/26.0833)/$D$16)*F29),IF(AND($F$8>=(B29),$F$8<=(C29)),((((NETWORKDAYS($F$8,C29,0)*$D$16/10)*($E$7/26.0833)/$D$16)*F29)+(((NETWORKDAYS(B29,($F$8-1),0))*($D$16/10)))*(($C$7/26.0833)/$D$16)*F29),IF(AND($H$8>=(B29),$H$8<=(C29)),((((NETWORKDAYS($H$8,C29,0)*$D$16/10)*($G$7/26.0833)/$D$16)*F29)+(((NETWORKDAYS(B29,($H$8-1),0))*($D$16/10)))*(($E$7/26.0833)/$D$16)*F29),IF(AND($J$8>=(B29),$J$8<=(C29)),((((NETWORKDAYS($J$8,C29,0)*$D$16/10)*($I$7/26.0833)/$D$16)*F29)+(((NETWORKDAYS(B29,($J$8-1),0))*($D$16/10)))*(($G$7/26.0833)/$D$16)*F29),IF(AND($L$8>=(B29),$L$8<=(C29)),((((NETWORKDAYS($L$8,C29,0)*$D$16/10)*($K$7/26.0833)/$D$16)*F29)+(((NETWORKDAYS(B29,($L$8-1),0))*($D$16/10)))*(($I$7/26.0833)/$D$16)*F29),IF(AND($D$11>=(B29),$D$11<=(C29)),((((NETWORKDAYS($D$11,C29,0)*$D$16/10)*($K$7/26.0833)/$D$16)*F29)+(((NETWORKDAYS(B29,($D$11-1),0))*($D$16/10)))*(($K$7/26.0833)/$D$16)*F29),IF(AND($F$11>=(B29),$F$11<=(C29)),((((NETWORKDAYS($F$11,C29,0)*$D$16/10)*($E$10/26.0833)/$D$16)*F29)+(((NETWORKDAYS(B29,($F$11-1),0))*($D$16/10)))*(($C$10/26.0833)/$D$16)*F29),IF(AND($H$11>=(B29),$H$11<=(C29)),((((NETWORKDAYS($H$11,C29,0)*$D$16/10)*($G$10/26.0833)/$D$16)*F29)+(((NETWORKDAYS(B29,($H$11-1),0))*($D$16/10)))*(($E$10/26.0833)/$D$16)*F29),IF(AND(B29>=DATE(2008,7,11),C29<=DATE(2009,6,25)),((NETWORKDAYS(B29,C29,0)*$D$16/10)*(($E$10/26.0833)/$D$16)*F29),IF(AND(B29>=DATE(2009,7,10),C29<=DATE(2010,6,24)),((NETWORKDAYS(B29,C29,0)*$D$16/10)*(($E$7/26.0833)/$D$16)*F29),IF(AND(B29>=DATE(2010,7,9),C29<=DATE(2011,6,23)),((NETWORKDAYS(B29,C29,0)*$D$16/10)*(($G$7/26.0833)/$D$16)*F29),IF(AND(B29>=DATE(2011,7,8),C29<=DATE(2012,6,21)),((NETWORKDAYS(B29,C29,0)*$D$16/10)*(($I$7/26.0833)/$D$16)*F29),IF(AND(B29>=DATE(2012,7,6),C29<=DATE(2013,6,20)),((NETWORKDAYS(B29,C29,0)*$D$16/10)*(($K$7/26.0833)/$D$16)*F29),IF(AND(B29>=DATE(2013,7,5),C29<=DATE(2014,6,19)),((NETWORKDAYS(B29,C29,0)*$D$16/10)*(($C$10/26.0833)/$D$16)*F29),IF(AND(B29>=DATE(2014,7,4),C29<=DATE(2015,6,18)),((NETWORKDAYS(B29,C29,0)*$D$16/10)*(($E$10/26.0833)/$D$16)*F29),IF(AND(B29>=DATE(2015,7,3),C29<=DATE(2016,6,30)),((NETWORKDAYS(B29,C29,0)*$D$16/10)*(($G$10/26.0833)/$D$16)*F29),"")))))))))))))))),""),""),""),""),""),"")
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question
What formula do i need vlookup to index match or something else?
Replies
8
Views
303
Peter_SSs
Peter_SSs
S
  • Solved
Lookup formula
Replies
4
Views
174
Joe4
Joe4
E
  • Solved
Get a 2D spilled formula to fulfill a distribution table matching the ranges of both dimensions
Replies
3
Views
54
Fluff
Fluff
I
  • Question
Is there an easy way to add formulaR1C1 into VBA arrays to speed up calculation time?
Replies
4
Views
847
ianawwalker
I
C
  • Solved
Multiple criteria Index Match with 1 criteria being within 10 digits and still match
Replies
3
Views
671
KRice
K

Forum statistics

Threads
1,214,386
Messages
6,119,220
Members
448,876
Latest member
Solitario

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top