LibreOffice Calc not computing functions (showing function, not output)

[erAck]

Check that the cell were you type the formula in is not formatted as text.

[BradK]

I have a similar issue with a ODS where the formulas don’t work but what I have noticed is despite explicitly Ctrl+1 set Category: All, set Format: General, OK., it doesn’t change the format of the column from Text and formulas still don’t work.

Version: 7.0.4.2 (x64)
Build ID: dcf040e67528d9187c66b2379df5ea4407429775
CPU threads: 8; OS: Windows 10.0 Build 19041; UI render: Skia/Raster; VCL: win
Locale: en-US (en_US); UI: en-US
Calc: CL

[erAck]

If you select a cell range that already has multiple different number formats assigned then invoking the number formatter displays General and selecting the General format will not change anything. You either need to temporarily set an explicit other format and after that invoke the number formatter dialog again on the same selection and set the General format, or remove all attribution on the selected cell range with Ctrl+M or context menu Clear Direct Formatting.

[erAck]

Note also that you have to re-enter a formula (editing the cell and appending and removing a blank is sufficient, or for a cell range with Find&Replace replacing = with = achieves the same) after the Text format was removed because the cell content is still text, not formula.

[smizles]

@erAck

Note also that you have to re-enter a formula (editing the cell and appending and removing a blank is sufficient, or for a cell range with Find&Replace replacing = with = achieves the same) after the Text format was removed because the cell content is still text, not formula.

This is still true as of 7.3.2.2. Personally, I consider this to be a minor bug because I think any reasonable person that selects a cell or cells containing data, right-clicks “Format Cells,” selects Number > General, should reasonably expect that data to be re-evaluated whether it is a number/formula or not. Sorry I don’t have time to submit this bug report right now, but I’m commenting here to find this solution again in the future as I no doubt expect myself to waste 15 minutes troubleshooting this again in the near future.

[mikekaganski]

@smizles

any reasonable person that selects a cell or cells containing data, right-clicks “Format Cells,” selects Number > General, should reasonably expect that data to be re-evaluated whether it is a number/formula or not.

No. Formatting a cell must never change the cell data.

You may apply formatting to large portions of sheet. E.g., select a whole column, and apply another format, to see if that fits your liking. And when you select that whole column, you may have selected some intermediate heading cells (imagine a heading cell with some ID consisting of 20 digits, which would happily convert to a number if it were allowed, but would only keep 15 digits of precision then). You do not want to discover that when you applied bulk-formatting, you also inadvertently destroyed some cell data (which you discover a week later).

Formatting and data are orthogonal. Cell data is only defined at input time, not at formatting time.

Please don’t waste time submitting that; there were a plenty of such requests, which indeed seem reasonable at a first glance, as long as one only considers the simplest cases, and doesn’t consider the fact that spreadsheets are designed to handle thousands of rows of data. Such bug reports are all closed NOTABUG.

[mikekaganski]

More examples of texts that could be ruined by automatic conversion to number:

• A phone number entered like +7-123-456-78-90 would become a formula =+7-123-456-78-90 with shown result of -740.
• A fraction-like text entered like 1 7/14 would become number 1.5, shown as 1 1/2 (you can’t reconstruct the original denominator). For 1 15/10, the result would be 2 1/2.
• A string like 1234 567 (ru-RU) or 1234,567 (en-US) - maybe some coordinates? - would become 1234567 (the placement of character separating two numbers, that happen to co-incide with group (thousand) separator, is lost).
• Similarly, 123456789.123456789 (en-US) would become number 123456789.123457 (tail lost).
• A string like 555e456789 would become 2E+308 (e being interpreted as exponent separator, resulting in maximum representable value, having nothing in common with original text).
• A string like 1/6 would become 01/06/2022 (en-US), and shown e.g. like 44,567.00 when incrementing decimals.
• A string with leading/trailing spaces around a number would indeed loose the spaces. If you have strings where original spelling is meaningful (say, you count frequency of different ways to represent something on writing), you loose the information.
• 0123 would loose its leading zero, breaking cases where it’s significant (e.g., when the leading zero designates octal numbers in C-like programming languages).
• dec 1 (a decrement operation?) would become 12/01/22 (en-US).
• true string would be recognized as a boolean value, convert to 1, and would show as, say, 1.00 when user increments decimal digits.

… etc.

Data conversion is a destructive process. Data formatting should not destroy the data.

[smizles]

@mikekaganski

Formatting a cell must never change the cell data.

You may apply formatting to large portions of sheet. E.g., select a whole column, and apply another format, to see if that fits your liking.

Well the way that this has been implemented is so counter-intuitive that I am back here for the second time this week looking for the solution. Can you explain to me again why LO Calc does not implicitly assume a cell with a properly formatted formula should be evaluated to obtain a result?

I’m disgusted at the thought I’m about to praise MS for anything, but Excel forces the user to prepend properly formatted formula text with an apostrophe (’) to signify “do not evaluate.” I am not trying to be that user that complains “Why can’t LO be more like MS Office,” but I’m struggling to keep an open mind right now.

TL;DR: Would you please explain the benefit from making the cell format control whether or not a formula gets evaluated? Are cell formulas and their evaluated results not the preeminent use of the Calc program?

[mikekaganski]

This is completely unrelated to “Excel superiority” (a good program BTW). The specific thing that you started to complain (applying formatting does not change data) works the same way in Calc and in Excel - if you have a formula-like text, it will stay text when you apply a numeric format. The way how Excel considers cell format at the entry time is also the same: when a cell is formatted as text, then anything you enter there, even when it looks like a formula, is stored as text (and does not need to be prepended with apostrophe). You only need an apostrophe when you are entering a formula-like text into a cell formatted as number.

@smizles

explain the benefit from making the cell format control whether or not a formula gets evaluated?

It’s not “cell format controls if a formula is evaluated”. Rather, as shown and written above, cell format is taken into account when you are typing into a cell. People need an easy way to enter not only numeric data, but also texts, including those that might look like spreadsheet formulas. So it is a common spreadsheet convention that a cell formatted as text accepts everything typed into it as text. And then it’s not a formula, so nothing to evaluate, until you re-enter, allowing the program to re-interpret the data.

[smizles]

@mikekaganski

You only need an apostrophe when you are entering a formula-like text into a cell formatted as number.

Sorry for my tone earlier. I am just frustrated about this same problem confronting me again since apparently I did not learn my lesson the first time I had to come here. I read through your comments twice, so let me know if I digested it correctly. Are you saying that I need to apply a numeric format to the cells in question before I enter the formula? (Also, what tool did you use to record your screen? I would like to demonstrate how to reproduce this issue and the counter measures I have taken that don’t work.)

Here’s a screenshot from my original formula entry (cell contents appear the same after pressing enter/selecting another cell):

Here’s a list of what I have tried:

1. Formatting this cell as a number:
   

   

2. Entering the space after the = sign:
   

   

3. Using the Insert > Function tool (closest to success):
   

   
   
   
   

FYI, the correct output I am looking for in cell B6 is “(Speaker 1: Time Requirement)” without quotes.

[smizles]

@mikekaganski Now I’m doubly frustrated because I tried going through the motions on this page from top to bottom (how I resolved this issue earlier this week), but now I cannot seem to resolve it. Obviously I can manually just type the correct output in this case, but I want to learn “the” correct way so I can use LO for more complex tasks in the future. Here is the output when I use the functions in the following cells:

• B8: ="("&VALUE(B7)&": Time Requirement"

  • Output = Err:502

• B10: ="("&TEXT(B9)&": Time Requirement"

  • Output = Err:511

Also, apologies for the serial commenting, but at least I’m trying things, right?

[mikekaganski]

@smizles

Are you saying that I need to apply a numeric format to the cells in question before I enter the formula?

Yes.

@smizles

Also, what tool did you use to record your screen?

I used ScreenToGif.

@smizles

Entering the space after the = sign:

Note that it’s not required to do specifically that. Any edit that marks the cell data “changed” internally - even simple adding a space at the end and immediately Backspace it, then pressing Enter - would tell Calc “a new data is entered; process user input according to current situation” (which, when the cell’s format is already numeric, would be “try to interpret the data as number or formula”).

@smizles

Also, apologies for the serial commenting, but at least I’m trying things, right?

No need for apologies; everything is OK (but the correct thing would be when you created a separate question initially … but no need for that at the current stage, to not destroy the discussion context).

[mikekaganski]

@smizles

VALUE(B7)
TEXT(B9)

VALUE tries to convert a text (representing a number) to a number. If a text doesn’t represent a number (as Speaker 2 in your example), it can’t be converted to a number, and the error is emitted. Err:502 means “invalid argument”.

TEXT takes two arguments, the second should be a format string. The Err:511 means “missing variable”.

Overall, if you have a text in a cell, and need it in a formula, simply use it, like

="(" & B7 & ": Time Requirement"

See also Calc guide on Documentation page.

[mikekaganski]

@smizles

1. 

FYI, the correct output I am looking for in cell B6 is “(Speaker 1: Time Requirement)” without quotes.

Looking at the screenshot again, I suppose that your formula in B6 might work OK, and you only see the last line of a two-line result in the narrow row 6, because your B5 has a newline after the “Speaker 1”. So your B6 might contain somethin like

(Speaker 1
: Time Requirement)

and making row 6 taller could possibly allow you to see that.

[smizles]

@mikekaganski

making row 6 taller could possibly allow you to see that.

Hallelujah! ^ This guy is a debugging genius! I would never have thought of that. How did you know there was a newline at the end of the text in that cell? Is that a typical artifact to expect after an Excel conversion?

[SteveS]

This has been fixed as of LibreOffice 6.4.4.2 space after = is no longer needed to enable evaluation of the formula.

[erAck]

It never was.

[smizles]

@SteveS

This has been fixed as of LibreOffice 6.4.4.2 space after = is no longer needed to enable evaluation of the formula.

EDIT: I had to modify the cell contents to get the formula to evaluate. One of the successful methods was inserting a space after the = sign.
.
.
.
FALSE - I’m on 7.3.2.2, and this is the second time I have visited this page for the exact same problem this week.

[Piotr82]

In my case (LibreOffice 6.4.4.2 on Linux Mint), it helped to reload the spreadsheet. Just click on menu File and hit Reload. You should see your formulas calculated, at least I managed to see them again. Nevertheless it is a bug that should be fixed.

[Villeroy]

Nobody is able to fix any bug that is not reproducible on a developer’s computer system. Without the problem right in front of you, there is no way to fix it. The one and only way how I can reproduce unevaluated formulas is bad csv import ignoring csv import options.