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

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

[smizles]

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.

[Villeroy]

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

@smizles

Formatting this cell as a number:

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)

[smizles]

@Villeroy

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

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.

[Villeroy]

@smizles

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?

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.

[initium]

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.

[H2354]

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

[mikekaganski]

@agwn

Will see if i can pull together a minimal test script to generate a sheet that will not calculate

⁠⁠⁠⁠⁠

[savelevgeo]

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

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

[agwn]

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

[mikekaganski]

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.

[Villeroy]

Your problem is completely unrelated to LibreOffice. You generate a 3rd party document format with a 4th party tool. Quite obviously, the tool writes 2 strings for every expense.
On top of it all, your problem is off topic. Your sum formula shows 0 as a result value. The topic was about cells showing a formula instead of a result value.