Created attachment 129828 [details] Example The "H" column of SUM calculates wrong. Example: December, 2016: = SUM (F343: F370) 17.00 = bad value. The correct value = 23.00 Why?
Doing a hard recalc works fine, it is a xlsx file, you can set up to always recalculate the file at opening. Menu/Tools/Options/LIbreOffice calc/Formula - Recalculate on file load, change it to 'Always' or 'Prompt user'. Option was incorporated to avoid an initial recalculation on large files.
Actually, the problem is with the automatic recalculation of cells, When I updated cell C, it resulted into instantaneous update of cell F, but not to cell H. This is the problem that I need to look for. Hope it get fixed
Hi Miklos, Can you describe how this bug emerged, I mean reproducing it with the given file seems to be of little use, since I cannot reproduce the error well. The only way to make sure that the error doesn't emerges is to do hard recalc at beginning.
The actual cause is that in the document some (not all) formula results in column F are stored as type string and strings are ignored while calculating SUM, hence.. Affected cells in the December range are F343, F349, F350 and F364. Might be some earlier version had a bug that for a formula such as =IF((C349&D349)="","",(D349-C349-7)) if it resulted in an empty string the string type somehow was persistent even if later a numeric value was calculated, which doesn't explain for F343 though where the formula is =D343-C343. Then, even in subsequent versions, as long as the affected formula cells were not recalculated the type was kept and saved again. There's not much we can do unless during import we wanted to check if any string type result could be a numeric result instead.. Once the document is recalculated (e.g. using Shift+Ctrl+F9 or after load using the Tools-Options setting already mentioned) and saved again the values are correct.
One idea how that initially could have happened: if the document was created in Google docs, Google sheets if saved as .xlsx have a bug that they wrongly use the string type for formula cells. LibreOffice 5.1 doesn't, but later a check was introduced if the document was saved by Microsoft Excel or LibreOffice Calc, and if not then we don't rely on the type information.