The Longest formula ever

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

[AciD]

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

[Bruno]

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

[Nimrod]

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.

[zacemmel]

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

[Aladin Akyurek]

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

[Jay Petrulis]

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 ]

[Jack in the UK]

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.

[AciD]

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

[stevebausch]

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.