What notation can be used to create comments in a formula

English
#1

I have some complicated formulas that I’m having trouble unravelling while I work out the details of my spreadsheet. I’ve found a couple of tricks that help:

  • I use mulitiline formulas and indent to show the nesting of the formula elements.

  • When I’m pulling data from another column, I give the whole column a name and just use the name of the column in the formula and Calc automatically get the data from the cell on the same row.

=IF( InputText = "" , "" ,
   SEARCH(
      Target ,
      InputText ,
      Start , End - Start
   )
)

is so much easier to read and the formula looks the same on each row because there are no row-number changes.

Those two features/techiques together are a game changer for me.

But I want more. I want to be able to annotate the formula to explain the logic and the intent of the various parts, just like I would in a computer language.

I’ve tried making notes at the top or bottom of the column or in an adjacent column. I’ve tried having a separate sheet where I explain the way the formulas work. But for me, if it’s not right in line with the formula, I forget to change the notes when I change the formula and I can come back a week later and wonder, “What did I do *that * for?”

I’ve experimented with embedded null constructions like:

... & IF(0,"This is a comment that can be attached to a text value","")
... + IF(0,"This is a comment that can be attached to a number value",0)

but those IFs keep fooling me into thinking they are part of the formula logic. They literally are, but semantically they are not.

So my question is: Does anyone have any more intuitive notation for adding comments to Calc formulas?

I would love something like # Comment to end of line...
but a null function like #("Comment inside parens and quotes") would be an improvement over the IF` constructions above.

Any ideas?

#2
1 Like
#5

Thanks for that link, Mike. They came up with similar solutions to what I had, and also suggested functions to use instead of my unaesthetic “`+IF(0,”…",0) construction.

I like using “+N("...")” to attach to a numerical value and “&T(N("..."))” to attach to a text value. No extraneous parameters and N() is mnemonic for “Note” and T(N()) can be mnemonic for “Text Note”, since it’s important to keep track of what kind of value I am attaching the comment to.

.

They also have a good example of LET() that I will sort through. I haven’t gotten LET() to work for me in LO Calc yet, and their suggestion that it allows naming of intermediate variables is exactly why I was trying it out.

.

Thanks!

#3

I have difficulties to undertstand the idea to insert comments into a formula using complicated means, and by that to copy the complete construct in many cases into lots of other cells.
I would suggest to create an annotation (also named “comment” for the UI) wherever it’s needed for clarity, to paste the formula into it, and to comment it there. Sending a copy of the formula as a string into the annotation can be simplified using a macro.
See example:
disask117628_commentingFormulas.ods (22.5 KB)

1 Like
#4

That is the essence of my question: how can I make it simpler, clearer, and more intuitive?

I would much rather have a single formula in one place. I can do that with the Regular Expressions that are part of many of my formulas. The RE argument to REGEX() is text so in the REGEX() formula I can just point to a named cell that has the RE text in it.

.

But I haven’t figured out how to have a normal formula located in a single cell and then execute that full formula from multiple cells. If I could do that, I could save a lot of copying each new version of the formula down multiple rows. I have fount that it’s possible to convert a Formula to Text, using the FORMULA() function, but there is no EXEC() function to read a text string in a cell and execute it as a forumula that I have found yet. Do you know of a way to do this?

.

As I said in the OP, having the commentary about the formula in a separate place is a maintenance issue for me. I would like to be able to comment on Calc formulas just like I do when defining a Ruby or JavaScript operation. I usually lay out my code and comments so that the comments are visually separated from the code by indentation and the flow of the code can be followed, with the comments right there to explain each step or nesting level. That’s what I want to do in complicated Calc formulas.

.

I used to worry about having all that extra text in each cell bloating the size of the spreadsheet. But I’ve realized that such concerns date from when I had to get a whole program to fit onto a single floppy disk. I have a full terabyte of free space on my SSD drive right now so having a text file be four or ten times as big is not an issue. Calc can handle over a million rows and over 16,000 columns so my 10 or 20 columns and 500 or 1000 rows are not straining it. It calculates invisibly fast.

.

What matters most to me is having the formulas be readable when I return to work on them after some time and having the descriptions of why and how be present inline with the what. Readability, maintainability, simplicity, and aesthetics are what I care about, so I am adapting the tool beyond it’s “normal” use patterns so that it can support my style of working because I want to make my working time be as simple and straightforward as possible.

.

The patterns I described in the OP are helping a lot. I’m asking for help in making the comments aesthetically cleaner, prettier if you will.

.

I know it’s not possible to have a pattern like “# Comment to end of line...”
because Calc formulas are not line-based and the multi-line format depends on treating Return characters as white space within a text string.

.

Is there a way of defining my own function, e.g. #() or maybe _() that would simply do nothing with its arguments, not even test how many there are? For my purposes, that would be a beautiful thing.

.

Right now, the best I have is something like:

=IF( InputText = "" , "" ,   IF(0,"A TITLE for the Formula Goes Here","")&
   SEARCH(                   IF(0,"
                                What I am SEARCHing for, and WHY,
                                goes here, including what my source is
                                and what I am going to do with the result.
                             ","")&
      Target ,               IF(0,"
                                Note that syntactically, this comment is
                                attached to the InputText string, because
                                it comes after the comma after Target, but
                                visually it comments on the Target parameter.
                             ","")&
      InputText ,
      Start                  +IF(0,"
                                This comment is attached to the Start parameter,
                                which is a number, so it must use + instead of &
                                and it must end in 0 instead of and empty string.
                             ",0)
   )
)

As you can see, I have a pattern that works fairly well, but I find the IF(...) structure and those final parameters, the "" and the 0, to be overly complicated for simple typing and reading. That’s what I would like to simplify, if I can.

.

Thanks for listening.

1 Like
#6

Not sure whether it fits your use case, but the Multiple operations tool allows re-use of formulas.

#7

My understanding is still incomplete, but I don’t think I would get better insight reading even more text. I like to work with realistic example files (.ods here) where I can try some ideas without using a lot of time to create a framework for it myself.
Any way I feel sure that you won’t find a satisfying way to do “Literate Programming” (as D.E. Knuth suggested it for imperative/procedural and object-oriented programming). The low-level functional programming by formulas in spreadsheets isn’t the proper surrounding for the idea.
My previous suggestion to use annotations may be unhandy, but columns are very cheap in spreadsheets, and they are hidden or shown again within a fraction of a second.
Generally the concepts an author followed often gets easily understandable (and recallable for the author himself) if action isn’t done by complicated formulas, but by simpler ones using their own columns and contributing to a “merging” formula in a next column to get the next explicitly needed result. In addition you can have columns with your commented, but not actually working formulas and use them like “outcommented” lines in a traditional program.
If you provide a realistic example I may be able to go a bit deeper, and possibly even to suggest (partial) solutions with the help of “named variables” which can be defined and used by Calc formulas (and do much more than LET()). They are a breach of the spreadsheet idea on the one hand, but sometimes useful in the hand of a user coming from procedural programming - and even beyond that.
(I will not continue to contribute to this thread if I don’t get the “realistic example” mentioned above.)

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.