The Longest formula ever

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.
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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 <u>or</u> 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
 
Upvote 0
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...
 
Upvote 0
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.
 
Upvote 0
I would have to bet that this formula can be rewritten in a much smaller fashion.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
"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...
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

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