[Calc] How to work with long formulas?

users
#1

Hi

Some time ago I had to write a function that will display filename of current
Calc workbook. After some Googling and trial and error I came up with this
monster:

=RIGHT(MID(CELL("FILENAME";A1);2;
FIND("'#";CELL("FILENAME";A1))-2);LEN(MID(CELL("FILENAME";A1);2;
FIND("'#";CELL("FILENAME";A1))-2))-FIND("$$$";SUBSTITUTE(
MID(CELL("FILENAME";A1);2;FIND("'#";CELL("FILENAME";A1))-2);"/";"$$$";
LEN(MID(CELL("FILENAME";A1);2;FIND("'#";CELL("FILENAME";A1))-2)) -
LEN(SUBSTITUTE(MID(CELL("FILENAME";A1);2;FIND("'#";CELL("FILENAME";A1))-2);"/";"")))))

(Of course it could be shorter if only temporary cells were allowed; you can
clearly see that much of this function is multiplication of some formula set.)

Since I am beginner of Calc, I had really hard time working on this. That made
me wonder:
how do I effectively work with long formulas in Calc? What
tools/extensions/tips are there to ease my work?
How can I keep track of relevant parts of each function, their input and
output values etc?

How do you guys handle such things?

Thanks in advance for any tips.

#2

I'm not sure what you mean by "if only temporary cells were allowed": they surely are! You can put intermediate calculations into some convenient area outside your print ranges - possibly even in hidden cells or on another sheet. In (say) X1, enter:
=CELL("FILENAME")
Note that the second parameter for the CELL() function appears - at least in this case - to be optional; the function presumably defaults to the current cell.
In X2, enter:
=FIND("'#";X1)-2
and in X3 enter:
=MID(X1;2;X2)
Then your final formula - in the required cell - reduces to:
=RIGHT(X3;X2-FIND("$$$";SUBSTITUTE(X3;"/";"$$$";X2- LEN(SUBSTITUTE(X3;"/";"")))))
which would be somewhat easier to understand and debug. (It's probably capable of further simplification.)

But how about a simpler solution - using this time only two cells for intermediate values?
In X1, enter:
=CELL("FILENAME")
as before.
In X2, enter:
=SEARCH("/[^/]+'#";X1)+1
and in the required cell:
=MID(X1;X2;FIND("'#";X1)-X2)

The regular expression in the SEARCH() function matches a slash followed by any number of characters (at least one) not including a slash followed by your single-quote hash-mark combination, effectively finding the beginning of the file name, the FIND() function reference then finds its end, and the MID() function picks out the required file name itself.

Note that for this to work, you need to have the option ticked at Tools | Options... | LibreOffice Calc | Enable regular expressions in formulas.

I trust this helps.

Brian Barker

#3

Brian,

At first, thanks for your reply.

Unfortunately, it misses the point. My question isn't "how can I get filename
of current workbook in Calc?" but "how can I effectively work with long
formulas?". This problem is much more generic.

To put it other way around, imagine that I have received file with mentioned
formula in cell. I know what it outputs, but I don't know how. I would like to
debug it and understand what exactly it is doing.

What are my options? Is separating repeating parts of formula onto other cells
the only way?

#4

Well, yes: that's what I did. As you take the formula apart, you will begin to see what it does and how. No, I don't see any clever way to analyse a formula. Fundamentally, I think you need the formula's author to explain how it works - which is what I tried to do with mine.

Brian Barker

#5

Well, that's a pity. I looked at some so-called expert Excel sites and it
seems that they have exactly the same problem. They encourage people to write
formulas step-by-step and then merge them. Of course dissembling these
monsters is hard.

I will wait and check for other answers (if any). Also I will ask on other
places. If there are no good tools for such tasks, I will push this to LO
developers. I think it would greatly improve our software.

#6

My preferred way for analysing a complicated/long formula is to copy
the formula to a text editor and break it apart there. For instance, I
find nested IFs much easier to understand/analyse if arranged in a
programmatic layout.

And, of course, if the cells a formula references can all be seen
simultaneously, there is always the F2 key to help get some idea of
what the formula is doing.

#7

One question to ask is what is the final output supposed to be? Often this will give clues to the type of functions needed and their order. Also, I tend to scroll the Calc function list to see if there is a function I am not familiar with that does what I want. Often I have found a complex formula can be made more manageable by searching for functions and finding some that make the final formula more compact and easier to understand.

I do not try to memorize the functions.

#8

Hi Miroslaw,

Mirosław Zalewski schrieb:

Hi

Some time ago I had to write a function that will display filename of current
Calc workbook. After some Googling and trial and error I came up with this
monster:

=RIGHT(MID(CELL("FILENAME";A1);2;
FIND("'#";CELL("FILENAME";A1))-2);LEN(MID(CELL("FILENAME";A1);2;
FIND("'#";CELL("FILENAME";A1))-2))-FIND("$$$";SUBSTITUTE(
MID(CELL("FILENAME";A1);2;FIND("'#";CELL("FILENAME";A1))-2);"/";"$$$";
LEN(MID(CELL("FILENAME";A1);2;FIND("'#";CELL("FILENAME";A1))-2)) -
LEN(SUBSTITUTE(MID(CELL("FILENAME";A1);2;FIND("'#";CELL("FILENAME";A1))-2);"/";"")))))

(Of course it could be shorter if only temporary cells were allowed; you can
clearly see that much of this function is multiplication of some formula set.)

Since I am beginner of Calc, I had really hard time working on this. That made
me wonder:
how do I effectively work with long formulas in Calc? What
tools/extensions/tips are there to ease my work?
How can I keep track of relevant parts of each function, their input and
output values etc?

How do you guys handle such things?

I use help columns. That has the advantage, that it is easy to see, where an error occurs.

Other ways are:
- shorten the formula by using named expressions
- define a function macro
- define a MULTIPLE.OPERATION template area

In addition it depends on the purpose of the formula. Some filter or sorting problems can be solved easier with pivot table and named filter and sorting areas than with search functions.

Kind regards
Regina

#9

To put it other way around, imagine that I have received file with mentioned
formula in cell. I know what it outputs, but I don't know how. I would like to
debug it and understand what exactly it is doing.

What are my options? Is separating repeating parts of formula onto other cells
the only way?

My preferred way for analysing a complicated/long formula is to copy
the formula to a text editor and break it apart there. For instance, I
find nested IFs much easier to understand/analyse if arranged in a
programmatic layout.

And, of course, if the cells a formula references can all be seen
simultaneously, there is always the F2 key to help get some idea of
what the formula is doing.

You mean Ctrl+F2, right?

Johnny Rosenberg

#10

No, I meant F2, which highlights dependent cells. That was why I wrote
'if the cells a formula references can all be seen simultaneously'.

Personally, I don't find the Ctrl+F2 function very helpful, but it
might be useful for some.

#11

You mean Ctrl+F2, right?

No, I meant F2, which highlights dependent cells. That was why I wrote
'if the cells a formula references can all be seen simultaneously'.

Ooops.

#12

Everyone who participated in this thread,

Thanks for your replies and tips. I will try to find my way in Calc's world.
Also I have some ideas how debugging and creating long formulas could be
improved; I will most certainly push them to developers, so maybe in future it
will be simpler.

Thanks again and have a nice day, everyone