Thanks:  0
Likes:  0

# Thread: The Longest formula ever

1. 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...
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.

 Microsoft Excel - aaCreditDebitLookup AciD.xls File(F) Edit(E) View(V) Insert(I) Options(O) Tools(T) Data(D) Window(W) Help(H) K9 = A B C D E F G H I J 1 Account Credit Debit EENU FCLI bf304 2 :alert('=MATCH(H1,A:A,0)')>5 :alert('=MATCH(I1,A:A,0)')>9 :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)')>-45 3 4 bf304 60 5 EENU 6 ax465 70 7 zx506 68 8 bf304 45 9 FCLI 10 ax465 90 Lookup

You can see the formula of cells only click each above hyperlinks

The above image was automatically written by HtmlMakerVer1.12
If you want this code, click here and Colo will email the file to you.

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)

[ This Message was edited by: Aladin Akyurek on 2002-05-12 09:58 ]

2. thx aladin, it's working perfectly now

3. ## Re: The Longest formula ever

=(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)))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))

4. ## Re: The Longest formula ever

That's a bona fide long formula.

5. ## Re: The Longest formula ever

That's a symptom of a terminal disease.

6. ## Re: The Longest formula ever

=(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)))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))
Simplifies to:
=42

7. ## Re: The Longest formula ever

Maybe, but I'd bet it could be reduced to 5% of that.

8. ## Re: The Longest formula ever

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.

9. ## Re: The Longest formula ever

Originally Posted by AciD
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.
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),"")))))))))))))))),""),""),""),""),""),"")

10. ## Re: The Longest formula ever

Originally Posted by Dangibb62
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),"")))))))))))))))),""),""),""),""),""),"")
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),"")))))))))))))))),""),""),""),""),""),"")

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•