Enhancing readability of complex Calc formulas

EvinT · November 15, 2022, 1:13am

Is there some way to enter and later edit cell formulas while seeing them in a structured way? Instead of seeing this in formula bar (in one usually enormous and arbitrary wrapped line):

=IF( J$5 < G4; IF( B3 = B4; $KontAddr.$A$1 = 1; $KontAddr.$A$1 = 2); $KontAddr.$A$1 = 3)

to see something like this (in several lines with manually or automatically entered line breaks and tabs):

=IF( J$5 < G4;
	 	IF( B3 = B4;
 		           $KontAddr.$A$1 = 1;
 		           $KontAddr.$A$1 = 2
	 	);
        $KontAddr.$A$1 = 3
  )

I have LibreOffice calc documents with many variables and large number of rows. With many dependencies spread across many sheets formulas have recently become so complex as to be almost unreadable. If I store intermediate results in a new sheets or cells, I will soon run into memory and speed problems.

Best solution for less cluttered and more readable formulas would be some kind of macro language. VBA or Python or another real macro language is too complex and too different than calc internal functions to be used often, fast and for great variety of dissimilar problems. Naming sub segments of complex formulas is useless because formula expression names don’t have arguments.

I have tried to manually structure appearance of my cell formulas by entering line breaks in formula bar editor. It is possible to force “new line” character with Ctrl-Enter. However, new line character 0x0A (\n) is later automatically replaced with space, and line breaks are not saved upon formula entering.

Is there some way to configure Calc to stop replacing “New line”, Tab and possibly some other control or escape characters in cell formulas so we could manually break formulas in several lines to enhance readability, and than save that formatting? Comment escape character would also be useful. These control characters should be treated like “spaces”, that is ignored, but retained in .ods document. It would enormously enhance usability for non trivial Calc documents.

Last option that I can think of is some add-on program which completely replace formula editing bar with its own formula editor. Than it can automatically and dynamically reformat and syntax highlight formulas according to Calc syntax.

Is there way to enhance readability of complex formulas by any of the above possibilities?

Version: 6.4.5.2
Build ID: 40(Build:2)
CPU threads: 4; OS: Linux 4.12; UI render: default; VCL: kf5;

karolus · November 15, 2022, 5:12am

the best way is to reduce the complexity of:
Data-layout and sheet-layout.
last but not least use use the right function instead of MULTI-NESTED-IF-CHAINS.

btw. you show a non valid formula… why??

EvinT · November 15, 2022, 7:02am

It is part of much larger code (which had “KontAddr” sheet). Used just as an example of code formatting. Code itself is not important.

mikekaganski · November 15, 2022, 5:20am

One could always use Ctrl+Enter to create multiline text content; however, it was not possible to have newlines in formulas.
In LO 3.5, multiline inputbar was implemented. One could use newlines when entering formulas, but they were converted into spaces upon finishing the entry.
Storing newlines in formulas was implemented in version 7.3 as a resolution of tdf#76310. (Tabs are still converted to simple spaces, though.)
Yet, even before, it always was (and is) possible to see formulas in structured way using Function Wizard and its Structure tab.

Anyway, use of overlong formulas is itself very questionable. Spreadsheets have plenty of cells, and one can always add more sheets, to accommodate for additional intermediate simpler formulas, instead of squeezing everything into one big unmaintainable formula. Just use what spreadsheets provide you by design.

EvinT · November 15, 2022, 7:09am

So not preserving original formatting characters was a non compliance with the OASIS OpenFormula specification and it was fixed in later Calc version than mine, for exactly the reason I mentioned (to allow for clearer representation of complex formula). Thank you for the information.

"OASIS OpenFormula specification allows using SPACE, TAB, LINE FEED and CARRIAGE RETURN as space characters. It also specifies that embedded newline be represented by single LINE FEED, and requires implementations to “retain white space entered by the original formula creator and use it when saving or presenting the formula, and should not add additional white space unless directed to do so during the process of editing a formula” "

https://bugs.documentfoundation.org/show_bug.cgi?id=76310

"It appears this has been discussed in this OASIS issue:

[OFFICE-701] Public Comment: OpenFormula: 5.14 White Space Clarification - OASIS Technical Committees Issue Tracker

The clarifying comments in the change document (linked at end) clearly indicate the reason for including these characters (to allow for clearer representation of complex formula). Given the attachment provided in comment 4 I can confirm the behaviour under Debian 7 x86_64 v4.3.0.2 Build ID: 14ed55896fdfcb93ff437b85c4f3e1923d2b1409. Status set to NEW. Platform to All/All"

Now we could just hope for maybe some further help in function editor, for example color syntax highlighting or detailed data inspection of parts of formula that is being written, debugged or edited. Is somebody aware of alternative function editor add-on?