Store / Call an "Equation" ????

[epl5906]

HI, have a "processing" dilemma!!
I have at least a dozen 600 cell tables that perform calculations such as the above for each cell:

=IF(Weeknum<5;L4;SUM(OFFSET(K78;0;SUM(HM+RD;Weeknum>BYE;AND(Weeknum>18;FLAG=1))-3-FLAG;1;4+FLAG))/MIN(4;SUM(HM+RD)))

This is "really" chewing up processing time. Spread sheet rather slow.

That said, I don't really need all these calculated values, I just do a VLOOKUP based on the value and table I need. So, I'm wondering if there is a way to just "store" an equation, let's say in an array table, and as I need a specific instance of a value, I would search for the equation I need, and apply it to the raw data. This way, just processing 25 equations when I need, and not 7200.

Other option I considered was some type of "Array" formula. However, online manual not very conprehensive, and not sure how I would even start with the complexity of the equation above.

Thx.

[RusselB]

From what I read, I'm thinking that the array option might be your best bet. I admit that I've not used array formulas much, but the first reference that I was given for help in this area (aside from asking here), is the Wiki page at https://wiki.openoffice.org/wiki/Docume ... ing_Arrays

[epl5906]

Thx for the link. I'll check this out.
But I have to believe there must be some type of application of INDIRECT or otherwise that would swap in an equation?
Thx again.

[epl5906]

OK, ARRAY formula's won't work. There seem to be known "ISSUES" with COUNTIF, SUMIF and OFFSET. These are used extensively in my computations.
So, back to "storing and fetching" an equation!! Any takers??? LOL

[Lupp]

1. I cannot know for sure how your named expressions like Weeknum, HM, RD, BYE, and FLAG are defined an what expense there is to calculate them. If there isn't a lot of MATCH and LOOKUP or statistical functions, a few thousand formulae of that kind should calculate very fast. It shouldn't "chew up" too much processing power.

2. There is no standard function in Calc like EVALUATE(Formula) usable to parse a text as a formula and to evaluate it. Also LibreOffice BASIC does not offer something to that effect.

3. If you want to parse a text as a supposed formula and to subsequently evaluate that formula if applicable, you have to assign the text to the property 'Formula' of a cell dedicated to the purpose by user code.

See attached example (very crude; only for use with LibO)

aoo83027CrudeFormulaEvaluator001.ods

(12.39 KiB) Downloaded 118 times

Editing:

4. I missed to mention that my first example was made with and for LibO Calc. This makes a difference as AOO is much more restrictive with respect to side effects of user functions. I made a second example therefore:

aoo83027CrudeFormulaEvaluator002.ods

(15.62 KiB) Downloaded 94 times

[epl5906]

OK, we're definitely on to something here!
Thx for the spread sheet. Have not used FORMULA or INDEX before, but got that figured out.
I've created a NAMED ARRAY called FORM, and for the sake of argument, added 3 formulae. So, if array is A1:A3 containing:

SUM(B1:D1)
AVERAGE(B1:D1)
B1*C1/D1

And A5 contains the equation: "="&INDEX(FORM;2;1) which equals =AVERAGE(B1:D1) This is how far I got. Don't know how to "Execute this" LOL

The "blue" shade in the spread sheet = #VALUE and equation =PARSE_AND_EVALUATE_IN_A1(A2). This is I did not get. Oh, and BTW, one for the suggestion box. Would be GREAT if developers created a function like EVALUATE(A5) - that would be very useful. So, all in all, I have everything set up. Just don't know how to get the VALUE for A5.
Thx.

[epl5906]

Hi again. OK, having some trouble understanding your Point#3. "you have to assign the text to the property 'Formula' of a cell dedicated to the purpose by user code.". As I continue to "dig" into your replies, it seems I might have to create my own "user function" ?? I did notice you created a macro/user function called "PARSE_AND EVALUATE_IN A1 ??? This does not want to work in as I get a warning message when opening spread sheets, that macro's are disabled. Don't pay much attention to it, as I rarely use macro's. But that said, maybe that user defined function is how I evaluate the text formula??? Bummer, that type of programming is way above my pay grade!! LOL. Unless of course, I can copy and paste "your" version to my spreadsheet?? And before I can even do that, I need to "enable" my macros. Which I don't know how to do !!!!!
Thx, again!

[Lupp]

I would like to add a point to the list from my first post here:

0. This is a slightly(?) "academic" contribution. You should avoid to rely on such tricks. I don't use them myself on a regular basis. Regard point "1.". If your few thousand formulae slow down the working of your sheet significantly, they may be badly designed. Try to enhance the efficiency of your formulae and of their working in the context of your sheets before you ask for somehow "exotic" means.

Have not used FORMULA or INDEX before, ...

The FORMULA function is only used to make the active formula visible while another cell is selected. INDEX is needed for the functionality.

I've created a NAMED ARRAY called FORM, ...

Whether named or not is of little meaning. If named "FORM" is not the best idea because the word is in use for a completely different purpose.

The "blue" shade in the spread sheet = #VALUE and equation =PARSE_AND_EVALUATE_IN_A1(A2). This is I did not get.

Seems you didn't notice my remark concerning the first version of my demo document not working in Apache OO. The user defined function PARSE_AND_EVALUATE_IN_A1 is crucial for the functionality. It is relying on a side effect of its code that must be accepted in the context defined by Calc. As mentioned the acceptance for side effects is substantially more restricted in AOO than in LibO. Only the second version of the demo will also work in AOO Calc.
To understand the working of the function you need to understand its BASIC code.

OK, having some trouble understanding your Point#3. "you have to assign the text to the property 'Formula' of a cell dedicated to the purpose by user code.".

The point "3." is concerning the working of the user defined function included with the demo. "... You have to assign the text ..." is exactly what that function is doing for you.

... that macro's are disabled ...

With "macros" disabled the demo must fail. You can set the 'Macro Security' to 'Medium' to be able to permit macros when loading a file. See: "macros;security" of the 'Help'. If you are sure the user code not is "malware" you can do so.

... BTW, one for the suggestion box ...

Developers won't notice a feature request posted in this forum. There is a dedicated site for bug reports and feature requests. Please consider first to learn much more about Calc before starting to post feature requiests. (Excel VBA is containing such a function. There is little encouragement to use it. Also Excel does not supply a standard function for the purpose usable in cells.)

[Villeroy]

I do not have the time to follow all the detailed requirements.
Most users ignore scenarios: download/file.php?id=3004
The validation feature can be used with formula expressions.
Relative references can be defined as names which can be useful with predefined formula expressions.

[epl5906]

Hello LUPP,
Thx for all the goodies:
1. Would like to think that "design" is not bad. Volume seems to be an issue. The "workbook" contains something like 15 sheets. Only 1 of those sheets is dedicated to those dozen 30 X 20 tables with complex equations. That said each table has 10 complex equations, and other 10 columns use RANK. VLOOKUP is used extensively in all 15 sheets. I also like CONDITIONAL FORMATTING and they can be somewhat complex with VLOOKUPS and (LOL), some instances of VLOOKUP containing VLOOKUP ie: VLOOKUP(VLOOKUP(;;;;);;;;;). SUMIF & COUNTIF other popular functions. FIND searches though Table of 5000 rows to search and HIGHLIGHT text strings. So, not sure which of these are taxing on CPU.
2. No issues on FORM array, that can be any name.
3. I did "enable" macros. Interestingly, it spit out the formula "=PI()" in cell A1. But that I suppose is all a moot point, as I will not be able to have time to learn visual basic or other syntax to create the user defined function. Sounds sexy, and a good solution, but I would be like a fish out of water on this one. I thought I was a "not bad" "spread-sheeter", but this site can certainly "humble" one! But thx anyway. Oh, and BTW, I did read and look into your 2nd sheet, just not advanced enough to thorougly understand it. Conceptually I do, "executionally" it's challenging.
4. I still do like the concept of a function called "EVALUATE", that takes a text string such as "=SUM(::)" and executes it. That would be a "God-send"!
5. I did receive another post re: assigning equations to a range name. This sounds promising and closer to my skill set. If this doesn't work, then I will have some compromises to make.
Thx again for all your valuable input.

[epl5906]

Hello Villeroy,
Thx for the spread sheet and ideas. Interesting about the "relative" address property in range names. Did not know that. I will play with this on the weekend and get back to you. Being called unexpectedly out of town. But if I can assign those complex equations to a name, the problem solved!
Thx.

[Villeroy]

1. Would like to think that "design" is not bad. Volume seems to be an issue. The "workbook" contains something like 15 sheets. Only 1 of those sheets is dedicated to those dozen 30 X 20 tables with complex equations. That said each table has 10 complex equations, and other 10 columns use RANK. VLOOKUP is used extensively in all 15 sheets. I also like CONDITIONAL FORMATTING and they can be somewhat complex with VLOOKUPS and (LOL), some instances of VLOOKUP containing VLOOKUP ie: VLOOKUP(VLOOKUP(;;;;);;;;;). SUMIF & COUNTIF other popular functions. FIND searches though Table of 5000 rows to search and HIGHLIGHT text strings. So, not sure which of these are taxing on CPU.

Same spreadsheet madness since decades. Wrong tool. Wrong approach. Wrong layout.

[epl5906]

LOL. To be honest, the spread sheet "started out" being rather simple, but then kept "morphing" into this behemoth! So, trying to get better design going, and there actually have been great improvements introducing pivot tables. But that all said, still wondering what I can trim down that is CPU intensive, and quite frankly just have no idea. Conditional Formatting? RSQ? RANK? Complex VLOOKUPS? OFFSET? AT this point, a little guidance, if anyone has some, would be great!
Thx.

[keme]

It seems that we are fumbling in the dark. Is it possible to attach your file here?

If attaching your file is not an option (e.g. file too large, confidential info, intellectual property), but you would still trust it with someone you don't know, you might send me a PM to arrange some kind of file sharing.

[epl5906]

Progress. The concept of storing an equation with relative addressing in a RANGE NAME looks very promising. One problem is that "some" of those equations require OFFSET, and therefore, need a start point. Therefore, would need @ least 1 equation for "each" "X" OFFSET start point. ie: OFFSET ("X" ; ; ; ;
So, now been playing with USER DEFINED FUNCTION. The VOL (a,b,c) is unfortunately too simplistic. Would be nice to "pass" my "X" value into a user defined equation, but realize that now it's written in BASIC, which I haven't been around for 35 years.
So, how would I create a function the performs the following:

F(n) = SUM(OFFSET("X";0;1;1;Weeknum-1))/SUM(HM+RD)
Which would be reduced to :
Function SEAVG (X,a,b,c,)
where: parameters substituted in would be: SUM(OFFSET(X;0;1;1;a)/sum(b+c)
I'm thinking this cannot be written in BASIC, because OFFSET in indigenous to AOO?? So, are there any ways to do this?
Thx.