Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 21

Thread: The Longest formula ever

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

    Default

    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.

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

    Default

    after reading my question, i think i wasn't be very clear..i'll explain the function of my formula :

    i have a temp.xls filled with numbers and some references, for exemple at the reference EENU, i have the account bf304 with 2 values in column 5 or 6 (debit or credit).

    As the reference bf304 isn't unique i need to search between 2 "great" reference, EENU and FCLI in this case.

    after finding the number of the line where is located my bf304, i check which cell (debit or credit) is filled and if credit is filled i negate the result, in the other case i just use the result.

    and at least when i can't find bf304(or whatever) between 2 references, i get a #N/A.

    and i don't want to cose i do a sum on all the result above..so.. when i have 1542+8794+#N/A+546 i get = #N/A

    ..quite boring.

    hope u'll understand my "english" :p

  3. #3
    Board Regular Bruno's Avatar
    Join Date
    Feb 2002
    Location
    Flanders
    Posts
    484
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Acid, your formula is so complicated ...

    In the case I have to test for several conditions, I prefer to use some extra columns to register the results.

    For example : use column K for the result of your first IF-statement, column L for the result of your second IF-statement, etc.

    Doing this, you can split your "longest formula ever" in a way it is easier to follow/understand ...

    Hope this helps you back on the track...


  4. #4
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    High Acid:
    (pun intentional)
    I think your problem is interesting an believe it can be solved with an array formula. Could you give me a another example with a little more data ? I really don't want to reverse eng. your code.

  5. #5
    Board Regular zacemmel's Avatar
    Join Date
    Apr 2002
    Location
    Redmond, WA
    Posts
    636
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I would have to bet that this formula can be rewritten in a much smaller fashion.

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

    Default

    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?

    Aladin

  7. #7
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    Consider using named formulae, especially the intermediate MATCH terms.

    A better approach would be to use the database functions in Excel, like DSUM, or going with SUMPRODUCT. For these to work, it would help to name your data table ranges. You might have to use multiple SUMPRODUCT functions together, but it would be much, much shorter and more flexible.

    Bye,
    Jay

    [ This Message was edited by: Jay Petrulis on 2002-05-07 12:24 ]

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    well ive read that formula a few times and seems to be fine, but my names Jack in the UK and Aladins been in here and commented, so take his advice,

    i say this is far to long, im my books long as needs be, i would split in to 2 3 4 5 6 as many as is simple columns and convert each stage.

    or ill get Get Chris D to eat trip!

    and as a bonus ill eat my dog, i love kebabs!

    alsation kebab, yum! with chillie sauce and salad of cause.
    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

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

    Default

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

  10. #10
    Board Regular
    Join Date
    May 2002
    Posts
    810
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Wow.........maybe that sort of code is usual and customary for the spreadsheet guys, but before I ever attempt to create and debug it, I would set up a macro to handle it, and a "ReCalc" button to invoke the macro.

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
  •