Known(?) Bug: Formulas Unexpectedly Convert to Text

English
#1

I was working on a fairly complex (for me) formula, the formula was working a bit, but not quite right, I was trying various things that seemed like they might help, when suddenly the formula got turned into text, wrapped in its cell, and didn’t calculate. Undo did not make a difference. There was no leading single-quote character ("’") to remove, copying and pasting didn’t help. I was stuck. Tore my hair and cursed LibreOffice for hours.

Google searching came up with things from six and twelve years ago that didn’t quite help, but which eventually pointed me in the right direction.

It seems one of the things I had tried earlier to get the formula to work was formatting the column where the formulas were as TEXT. Turns out that is a GREAT BIG OOPS. After applying the format, the formula still calculates just fine. It’s only later, when I later made a change to the formula, in trying something else, that the glitch happened. When changing the formula, the new version gets written into the cell and that’s when it becomes Text, with no way back. So I’m looking, for hours, at what the specific change was, not at the formatting change that I tried hours before.

It kind of makes sense, and I still consider it a bug. If you have a bunch of blank cells that are formatted as Text and you start writing in them, everything will be formatted as text. And helpfully, so to speak, if you type numbers in those text cells, they will be entered as text. If you type a formula, it will be text, not calculated, AS IF you had started each cell with a single quote, but without that character actually being there. Is that actually helpful? I don’t know.

The real glitch is that if you apply that Text format to non-blank cells, and some of them have formulas, the formulas will still keep on calculating and displaying properly, so you don’t know yet that there’s anything amiss. It’s only when you make a change to a formula that suddently, because the cell is now being written (over the previous contents) that it becomes Text instead of remaining a formula. So the bug can become evident some long time after you actually made the change (formatting the cells as Text) that creates the problem.

At this point, nothing you do, type, or paste in that single cell will change the text back into a formula. UNDO does not undo it. Undo will revert your typing, but it does not undo the conversion of the formula to text because that’s not in your immediate change list, it’s way back in history somewhere. That’s what drove me crazy for hours. Changing the format of the cell to General won’t even do it – until you paste the formula into the cell again.

I have found similar problems stated, but no explanations. Does anyone have any alternate explanations? If so, I’d like to hear before I submit this to Bugzilla. My question is not:

“What’s going on, how do I fix it?”

But rather:

“Is my understanding correct or am I missing something fundamental about what’s going on here?”

As I said above, this may be intended behavior, but it creates serious problems that are hard to debug.

#2

I guess thats the price we pay, when using an old tool wich was designed last century and has the additional problem of mixing code and data…
.
IMHO the/one. problem is the use of format as type. Formatting does NOT change the contents of existing cells, but it is considered when data is entered. This explains why you often feel the impact only later, when the cell is changed next time. There is a faq to change from text to numbers. The idea is always to trigger the automatic recognition again.

https://wiki.documentfoundation.org/Faq/Calc/How_to_convert_number_text_to_numeric_data/en

#3

This just requires a spreadsheet user to learn the spreadsheet concept that was fundamental in spreadsheet industry for the last 40 years. Applying formatting (any formatting!) does not change the type of existing data; existing text formatting affects the newly entered data (which is treated as text, regardless of possibility to interpret as numbers or formulas).

So - for you, it was a matter of knowing the software you use; and now, you know to look at the cell formatting - so where will those “serious problems that are hard to debug” be? In fact, there are only two possibilities here when you enter new formula, and it shows formula text instead of result: either you have formulas shown in View menu; or the cell is pre-formatted as text.

The alternatives could be - to merge formatting and data types (“applying formatting also changes data type immediately”) - a destructive option (changing data type may irreversibly change data, which, when you apply formatting to whole columns, will go unnoticed in ~100% cases); or introduce another property in the UI, in addition to formatting - the type - i.e., adding complexity to the already complex software - and introduce incompatibilities, adding to learning curve, breaking interoperability…

(And no, please do not submit this to Bugzilla; we will have to close it NOTABUG anyway.)

See also: UI: Poll: how should LO calc react to format changes - #7 by mikekaganski
LibreOffice Calc not computing functions (showing function, not output) - #18 by mikekaganski

#4

Are you referring to the length of time that spreadsheets have been around, or to the age of LibreOffice? Do you have any suggestions for a more “modern” tool, particularly at a similar price point to LibreOffice Calc?

IMHO, this is a problem caused by emulating Microsoft, which is notorious for overzealous handholding (to the point of breaking bones).

Unfortunately for the snideness of your remarks, that is not the situation. As explained more than once in the OP, the formula was calculating as expected until I attempted to modify the formula (by the insertion of a single character into a REGEX), at which point it was transformed into Text, and ⌘Z did not revert the change, it only removed the added character. I could repeat the process numerous times.

If the conversion to Text datatype instead caused the insertion of a single quote at the beginning of the cell text (the traditional way of getting things that don’t look like text to be interpreted as text, going all the way back, I believe to the first version of Excel and maybe to its unreleased predecessor, “Electronic Paper”) there would be no problem and the fix would be obvious. But the change was invisible and un-undoable. I still maintain that that is difficult to debug.

Nowhere in the doc, that I could find, and I’m good at searching doc – I was a developer documentation writer/editor for 40 years – is it explained that the leading single quote is not needed if the cell format is already set to Text, and only for that one format, all other formats will require the leading single quote. And I was only able to do that looking after I figured out what the problem had to be. This is not “user friendly” although it may indeed be an emulation of Microsoft.

Your admonition to “know the software I use” is spurious, you’re implying that no one should attempt to use software for which they have not memorized the manual, or else implying that it’s my responsibility to know the undocumented idiosyncrasies of that software and not bother you by asking about them. Either is not living in the real world.

Thank you for letting me know that submitting this issue to Bugzilla would be pointless. That’s another emulation of Microsoft that disappoints me.

I would like to suggest that if the behavior of the software cannot be changed (e.g. adding that standard single quote instead of making the change invisibly) then the behavior should be documented in some place that is findable. A one sentence “NOTE:” under “Cell Formatting > Text” could do it.

Thanks.

#5

Jupyter Notebooks.


Edit: I use databases a lot. They keep types consistent (if I wish so). But SQL is neither modern, nor without problems, as it also varies between databases-engines/software.

#6

I do not see how that conflicts with

You do some editing of a cell: this is, you change the content of the cell. At this moment, the cell is already formatted as text (=pre-formatted). At the end of your editing, Calc takes the new text, and decides how to treat it. At this moment, my comment applies 100%. It doesn’t matter, that there was something else in the cell earlier; or that there was a history of formatting of that cell.

No, you are putting your words into my mouth. There was no “you should not attempt” - but there was “some software needs that, so in case of trouble, the learning is the first thing to try”. Also, I never said “do not ask” - I myself repeatedly answer and explain; here you didn’t ask that - so we were discussing something completely different. I said, that there is no “hard to debug problems” - there is only a learning curve, after which, this “problem” is obvious. And learning does not imply “only do it yourself”, we are here to help, in addition to many other resources.

#7

This is what I described above:

It is really difficult to change behaviour wich is around for so long time. A lot of people know the current set and will complain, when their habits may need change.
.
We see even problems with “simple” things like dark mode. IMHO the first question after implementing dark mode was “where can I switch this of again” from people using dark mode (everywhere?) but not in their office-suite.
.
Additional: Some like LibreOffice being as close as possible to MS-Office, some not…

#8

WOW! Jupyter Notebooks looks pretty amazing. I’m seriously intrigued by its possibilities.

The idea of “Document Centric Code” looks like it connects back to Don Knuth’s concept of “Literate Programming” where code and doc are merged into one source (which he called “Web” format), then processed into compilable code (via “Tangle”) or publishable doc (via “Weave”) as needed.

#9

My apologies. I was taking my frustration with “the problem” out on you.

Thanks, I’m getting that, now that I’ve been able to move past my frustration.

I’ve been on both (or is it all three?) sides of that in software off and on for over 40 years. Keeping something wrong because people are used to it is not a very good excuse, in politics, engineering, science, or personal relationships.

Forty years ago I spent some time lobbying for the power, simplicity, speed, and health benefits of the Dvorak Keyboard over Qwerty. I still have the electric typewriter that I special-ordered from Smith-Corona with the Dvorak keyboard. And I’m using Qwerty on my Macbook. And I still wish I could change things.

Thanks for your help and patience.

.
August

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