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

English
, ,
#26

@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

20220526-LO_Calc-Formula_Not_Evaluating-Value+Text
20220526-LO_Calc-Formula_Not_Evaluating-Value+Text371×575 21.8 KB

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

#27

Yes.

I used ScreenToGif.

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”).

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).

1 Like
#28

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.

1 Like
#29

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.

1 Like
#32

Hallelujah! :raised_hands: ^ This guy is a debugging genius! :hugs: 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?

20220526-LO_Calc-Formula_Not_Evaluating-Actually_Displays_Multiline
20220526-LO_Calc-Formula_Not_Evaluating-Actually_Displays_Multiline202×629 15.2 KB

#14

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

#15

It never was.

1 Like
#22

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.

#16

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.

#19

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.

1 Like
#24

My issue happens from a legacy MS Excel (.xls) file I received that I converted to LO Calc (.ods) using the File > Wizards > Document Converter tool.

#30

and why do you post screen shots of a file instead of the file itself?

What was the number format before you changed it? Text? Well, in this case it is just another overly formatted Excel document.

  1. I create document with a concatenation formula and format the formula cell as text (common mistake).
  2. Save as xls
  3. Apply the conversion wizard
  4. Open the xls and the ods

Both files show the formula result.
Untitled 1.xls (8 KB)

#31

This is a roster for a Toastmasters group, so would you like me to publish your full name, home address, email, and cell phone on the internet if you were in our group? It will take a lot of effort to sanitize this document before it is “shareable,” which is why I wanted to check for obvious errors on my part before needlessly risking accidentally sharing confidential information.

1 Like
#33

Did I do that with my example document where I tried to reproduce the problem based on the given information?
Make a copy of yours and remove everything except some of the bad formula cells and cells they are referencing. Write dummy values into the referenced cells. Save and reload the document before uploading to make sure that the problem still occurs in the reduced demo.
This is the one and only way how your problem can be “transfered” to a developer’s computer so he/she would be able to fix something.

#44

I have a fresh install of LibreOffice 7.4 and have the same issue with the function not showing the result. I have checked that all the formatting was the same in the cells being used. The function I’m trying to use is DATEDIF. I tried typing the function directly and using the wizard. I even tried the space after the = sign. Nothing worked, even though the function wizard showed the correct result in the example result. I then wondered if the issue was something to do with the column as it was inserted between columns in use.

What worked…
Adding the function in a different column at the end of the current columns in use worked. I then grabbed the column and moved it across to the place I needed it in the spreadsheet.

It would seem that Calc does not like to apply functions to an inserted column, but will display correctly in a default column. Why an inserted column is treated differently I can’t say, nor can I find any difference in any settings, however I had the exact same issue in version 6.4, so this is a bug and it has been carried over to the new version. I hope the dev team can use this info to better the programme.

1 Like
#45

For an unknown reason, the Default format was not anymore → Category:Number Format:'General.
Solution:
F11->Right Click on ‘Default’ and click on ‘Modify…’
Category:click on ‘Number’
Format:click on ‘General’
Click on ‘OK’

After this entering a number into a cell, it is seen as a number and aligned to the left.
Entering a function like =concat(“A”,“B”) → OK. Entering =1+1 → OK

If some functions/calculations not working, → CTRL+M on the cell and change the contents if needed.

My 2 cents

#35

:+1: ⁠⁠⁠⁠⁠

#46

For me the problem was I have turned on the “Show Formula”:
View > Show Formula (Ctrl + `)

image

With that check on we see not the result of the formula calculation but the formula

#36

I am able to repo with the spread sheet from the xlsxwriter tutorial with python 3.8.10 and XlsxWriter==1.3.3

the output file TestWB.xlsx (5.2 KB) shows the sum as 0. Ctrl+Shift+F9 forces a recalculation and replaces it with the result of the formula.

import xlsxwriter

# Create a workbook and add a worksheet.
workbook = xlsxwriter.Workbook('Expenses01.xlsx')
worksheet = workbook.add_worksheet()

# Some data we want to write to the worksheet.
expenses = (
    ['Rent', 1000],
    ['Gas',   100],
    ['Food',  300],
    ['Gym',    50],
)

# Start from the first cell. Rows and columns are zero indexed.
row = 0
col = 0

# Iterate over the data and write it out row by row.
for item, cost in (expenses):
    worksheet.write(row, col,     item)
    worksheet.write(row, col + 1, cost)
    row += 1

# Write a total using a formula.
worksheet.write(row, 0, 'Total')
worksheet.write(row, 1, '=SUM(B1:B4)')

workbook.close()
#37

Heh, that’s not a bug. Interesting, that if you open the file in MS Excel in read-only mode (say, download it from this thread on Windows, so that it is marked “blocked” as received from Internet), it also shows 0.

In Calc, there is Options|Calc|Formula, section Recalculation on File Load, which tells by default that when opening Excel 2007+ files, cached values must be used, and not recalculated. And indeed, the file has a “cached” wrong value:

        ...
        <row r="5"
             spans="1:2">
            <c r="A5"
               t="s">
                <v>4</v>
            </c>
            <c r="B5">
                <f>SUM(B1:B4)</f>
                <v>0</v>
            </c>
        </row>
        ...

The v element is that “pre-calculated” value. If it weren’t there, or if the mentioned option would be “Always recalculate”, you would get the wanted result.

The option is meant to serve two important things:

  1. Save time on load: computations may take minutes for heavy spreadsheets.
  2. Possibly even more important - allow opening spreadsheets “as author created them” in cases where Calc may give different results (e.g., where locale data may give different representation, or there are differences in document models between the two applications, or even simply bugs).

So this example is rather a bug in the generator software, that should not provide wrong “pre-calculated” data, where it should just skip it.

2 Likes

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