It never was.
1 Like
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.
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.
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
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.
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.
- I create document with a concatenation formula and format the formula cell as text (common mistake).
- Save as xls
- Apply the conversion wizard
- Open the xls and the ods
Both files show the formula result.
Untitled 1.xls (8 KB)
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
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.
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.
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
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
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()
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:
- Save time on load: computations may take minutes for heavy spreadsheets.
- 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
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.
1 Like
Found this thread looking searching for functions not computing. Thanks for the info on the cached values. Will dig into my generator software and see if there is anything I can do to disable this.
import xlsxwriter
# Create a workbook and add a worksheet.
with xlsxwriter.Workbook('Expenses01.xlsx') as workbook:
worksheet = workbook.add_worksheet('Tab33')
# Some data we want to write to the worksheet.
items = ('Rent','Gas','Food','Gym','Total')
values = (1000, 100, 300, 50, '=sum(B1:B4)' )
for c, data in enumerate((items, values)):
worksheet.write_column(0, c, data=data)
1 Like
That in a comment says “It then sets a global flag in the XLSX file to say that all formulas and functions should be recalculated when the file is opened”. Which here is <workbook> child
<calcPr calcId="124519" fullCalcOnLoad="1"/>
Calc so far does not evaluate that and probably could.
Update 2022-08-08T16:27+02:00
This is tdf#144819.
1 Like
… which then could allow us to add “Follow document setting” to the “recalculate on load” option - and make it the default 