Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 21

Thread: The Longest formula ever

  1. #11
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,653
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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=
    ABCDEFGHIJ
    1Account CreditDebit EENUFCLIbf304
    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
    4bf304 60
    5EENU
    6ax465 70
    7zx506 68
    8bf304 45
    9FCLI
    10ax465 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)

    Aladin



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

  2. #12
    New Member
    Join Date
    May 2002
    Location
    France
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    thx aladin, it's working perfectly now

  3. #13
    New Member
    Join Date
    Jun 2012
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: The Longest formula ever

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

  4. #14
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    15,929
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default Re: The Longest formula ever

    That's a bona fide long formula.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  5. #15
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    20,652
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    5 Thread(s)

    Default Re: The Longest formula ever

    That's a symptom of a terminal disease.

  6. #16
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    15,929
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default 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

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  7. #17
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    20,652
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    5 Thread(s)

    Default Re: The Longest formula ever

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

  8. #18
    Board Regular repairman615's Avatar
    Join Date
    Dec 2009
    Location
    Tennessee, USA
    Posts
    1,885
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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.
    ___-Jeff______________

    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Help using VBA Code
    - Helpful links from Hiker95

    Code Tags:
    [CODE] (Place your code here) [/CODE]
    ___-2007-

  9. #19
    New Member
    Join Date
    Jul 2015
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: The Longest formula ever

    Quote Originally Posted by AciD View Post
    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. #20
    New Member
    Join Date
    Jul 2015
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: The Longest formula ever

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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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