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

[brian15co]

Hi Everyone,

I cannot get libre office calc to recognize functions. I am being shown the formula as it is typed, not the desired output.

I have tried Tools > Options > LibreOffice Calc > View > Formulas. Either checked or unchecked gives the same result.

Also, if I press Ctrl-F8, text remains black, numbers turn blue, but nothing turns green (another question told me that formulas would turn green).

below are identical questions whose solutions do nothing.

Calc cells shows only formula and not product/output

Hello, Since updating to (I think) LibreOffice 3.5.0 (and now 3.5.1) on Mac OS X 10.6.8, all my Calc spreadsheet formulas continue to work, but their cells no longer show the results! Instead, the cells show the formula. I tried copy a paste special with numbers only from the formula into another cell, and the formula’s output gets pasted. This is very strange and frustrating, can anyone help with fixing this? Thanks!

Calc Cells not calculating at all?

Hi Very straight forward question really that has I am sure a simple answer - none of my Calc spread sheets will do any calculations i.e. the highlighted cells result in =Sum (cells) and no result?? Tried it several times in different spreadsheets and it works fine in Gnumeric, but not Calc at all?? Any answers gratefully accepted David

http://en.libreofficeforum.org/node/2353

Functions do not execute in Calc cells. They just appear as text. This is in LibreOffice 4.1 running in Windows 7, 32 bit

I have tried re-installing Libre Office, tried re-booting Windows, and all the usual procedures. I have previously run Calc satisfactorily, and opening an old spreadsheet allows me to enter formulas in an ordinary way. I’d appreciate any suggestions on dealing with this!

[LibreGuy]

First of all, make sure your formula starts with the = character. Try this formula to test:

=IF(TRUE();“HELLO”;"")

Normally you should see “HELLO” in the cell you pasted the formula into.

If not:

1. Right-click the cell in which you pasted the formula and choose ‘format cells’.
2. Select ‘number’ and then ‘standard’ or ‘default’ (depending on your language). Click ‘OK’.
3. In the formula-bar (the bar on top where you can see your formula) click on the = button. The button will now turn into an Arrow. Click on the arrow.

Now you should see “HELLO”.

[Shivaschandra]

This is not working for me , it still shows the formula and not the solution.

[LibreGuy]

Can you upload your spreadsheet?

[dvdsev]

You need to type a “space” between equal sign and the rest of formula. At least it worked for me

[SteveS]

Adding the space after the equals worked for me as well. I’d call this a minor but irritating bug

[erAck]

I fail to see how that could be possible. Also, IMHO such behaviour was never submitted as bug. One should assume if that was a common behaviour it should had shown up as a bug report. If it happened only for specific function names then it may have been a translation error where the “word” of a function included a leading space. But then again the parser should had eaten that before… What UI language with translated function names were you using, if any?

[newchapmj1]

I just got the same problem and the fix above worked for me as well
("Adding the space after the equals worked for me as well.)

Version: 7.0.2.2 (x64)
Build ID: 8349ace3c3162073abd90d81fd06dcfb6b36b994
CPU threads: 12; OS: Windows 10.0 Build 19043; UI render: Skia/Raster; VCL: win
Locale: en-GB (en_GB); UI: en-GB
Calc: CL

[erAck]

@newchapmj1: If it is reproducible and also happens in 7.0.4 (which currently is the latest stable release you should upgrade to anyway) and also after resolving possible user profile corruption then please submit a detailed bug report also attaching a sample file with which the bug is reproducible for you.

[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?