Bug 94271

Summary:	Calculation in Calc is wrong
Product:	LibreOffice	Reporter:	ralf.gandenberger
Component:	Calc	Assignee:	Not Assigned <libreoffice-bugs>
Status:	RESOLVED DUPLICATE
Severity:	major	CC:	algot.runeman, jmadero.dev, lgodard.libre, raal, ralf.gandenberger
Priority:	medium
Version:	5.0.1.2 release
Hardware:	All
OS:	Windows (All)
Whiteboard:
Crash report or crash signature:		Regression By:
Attachments:	File showing the wrong calculation as described New file with issues

Description ralf.gandenberger 2015-09-16 11:38:55 UTC

Hi, 

I used *.xls files under Libre Calc, which worked fine so far. Since I updated to Libre Office 5.0.1.2 many results in the old files were wrong. I checked and found out that the reference to the cells is still correct, but not the calculated result of the cell referred to is used.

Example:
A1: 100, B1: 15, C1: =D1-B1; D1: 30
A2: =A1-C1

The result in this example should be 85, sometimes it is. In many cases it is 70, i.e. A1-D1. To me it seems that the calculation in C1 has not been made correctly.

The files I have are rather big and I had this mistake in several tables in one file.

The correct calculation could be reached by copying the cell A2 and inserting it again. But it did not help to copy A2 and insert it into all cells A2 to A...n.

I saved the file as ods-file, which didn't help.

Do you know this issue, any solutions ?

Thanks

Ralf

Comment 1 Algot Runeman 2015-09-16 12:26:00 UTC

Ralf,

Test System:
Kubuntu 15.04 (64 bit)
LibreOffice 5.0.1.2

Thanks for the report.

Entering the very limited data you supplied, I could not see the error you describe. Please attach a file of data (perhaps part of your large one) which demonstrates the problem. Without that, it will be difficult to check the problem.

Comment 2 ralf.gandenberger 2015-09-16 17:42:59 UTC

Created attachment 118777 [details]
File showing the wrong calculation as described

File showing the example

Comment 3 ralf.gandenberger 2015-09-16 17:46:26 UTC

(In reply to Algot Runeman from comment #1)
> Ralf,
> 
> Test System:
> Kubuntu 15.04 (64 bit)
> LibreOffice 5.0.1.2
> 
> Thanks for the report.
> 
> Entering the very limited data you supplied, I could not see the error you
> describe. Please attach a file of data (perhaps part of your large one)
> which demonstrates the problem. Without that, it will be difficult to check
> the problem.


Hi Algot,

thanks for coming back. Attached is a file which shows the issue after line 151. I had the same miscalculation at the top of the table, but copied F5 and pasted it again - and the calculation was correct.

But it's probably not a suitable solution to look for mistakes, insert the formula again and hope the problem is gone.

Thanks for looking at this

Ralf

Comment 4 raal 2015-09-16 18:06:31 UTC

Which cell is bad?
row 152:
183,77 	183,77		Mai	19 899,89 	55,72 	184,28 	240,00

Comment 5 MM 2015-09-16 21:19:51 UTC

Bit strange, when i'm on windows 7 x64 with version 4.4.5 with 'Formula > Recalculation on file load' Off I get this with row 152:

183.91 	237.45		Mai	18,883.69 	52.87 	184.58 	237.45 

When I turn it on (always) and reload, I get this:

192.03 	192.03		Mai	2,141.76 	0.00 	192.03 	192.03 

Tried a fresh profile, no difference.

Evenmore stranger is when I tested it under mint 17.2 x64 with 4.4.5 and 5.0.2.1
Then I got these values.

Formula > Recalculation on file load' Off:

183.91 	237.45		Mai	18,883.69 	52.87 	184.58 	237.45 

Always on:

183.77 	183.77		Mai	19,899.89 	55.72 	184.28 	240.00

So which one is correct here ???

Comment 6 ralf.gandenberger 2015-09-17 10:31:52 UTC

Hi, the whole thing gets even stranger. In addition to the issue I described I found out that in the file I added the last time the rounding in cell G7 was incorrect (it should be 157,52 instead of 157,51). I copied the cell and pasted the content again, now the rounding is correct.

But: as of row 10 the calculation in the cells of column F are wrong. And as of row 122 there is no calculation in column G at all.

Comment 7 ralf.gandenberger 2015-09-17 10:32:57 UTC

Created attachment 118792 [details]
New file with issues

Comment 8 ralf.gandenberger 2015-09-17 13:19:26 UTC

Hi,

The results are different every time I open a file. Sometimes operations in cells are calculated, sometimes they aren't. I set up a new file with the data of the loan without any copying from other files whatsoever. The first rows were fine, then the results were wrong without any logic behind it. When I closed the file and opened it again, most of the cells were not calculated.

I unistalled LO 5.0.1.2 and reinstalled it again. No changes.

I unistalled LO 5.0.1.2 again and installed LO 4.4.5.2. The calculations are still incorrect.

Comment 9 ralf.gandenberger 2015-09-17 14:57:08 UTC

Another try: I transferred the files with the problems to my iPad and opened them with MS Excel. All the results are there and correct - what a relief, the files are still intact. If I open that very file with Libre Office Calc, the calculation is wrong again.

So it must be a problem with either LibreOffice Calc or my PC, on which I run Windows 8.1. (all updates installed).

Comment 10 ralf.gandenberger 2015-09-17 15:07:31 UTC

(In reply to MM from comment #5)
> Bit strange, when i'm on windows 7 x64 with version 4.4.5 with 'Formula >
> Recalculation on file load' Off I get this with row 152:
> 
> 183.91 	237.45		Mai	18,883.69 	52.87 	184.58 	237.45 
> 
> When I turn it on (always) and reload, I get this:
> 
> 192.03 	192.03		Mai	2,141.76 	0.00 	192.03 	192.03 
> 
> Tried a fresh profile, no difference.
> 
> Evenmore stranger is when I tested it under mint 17.2 x64 with 4.4.5 and
> 5.0.2.1
> Then I got these values.
> 
> Formula > Recalculation on file load' Off:
> 
> 183.91 	237.45		Mai	18,883.69 	52.87 	184.58 	237.45 
> 
> Always on:
> 
> 183.77 	183.77		Mai	19,899.89 	55.72 	184.28 	240.00
> 
> So which one is correct here ???

Hi MM,

now that I have the correct calculation (on Excel) I can answer your question:

Only the last one is correct.

Comment 11 ralf.gandenberger 2015-09-17 15:08:54 UTC

(In reply to raal from comment #4)
> Which cell is bad?
> row 152:
> 183,77 	183,77		Mai	19 899,89 	55,72 	184,28 	240,00

Hi raal,

now that I have the correct calculation again: your results are correct. How did you get them

Ralf

Comment 12 raal 2015-09-20 10:53:15 UTC

(In reply to ralf.gandenberger from comment #11)
> (In reply to raal from comment #4)
> > Which cell is bad?
> > row 152:
> > 183,77 	183,77		Mai	19 899,89 	55,72 	184,28 	240,00
> 
> Hi raal,
> 
> now that I have the correct calculation again: your results are correct. How
> did you get them
> 
> Ralf

For recalculation of the sheet you can use CTRL+SHIFT+F9 shortcut key. You can set automatic recalculation in options->LO Calc-> Formula -> Recalculation on file load.
Please provide step by step instructions how to reproduce the bug.

Comment 13 ralf.gandenberger 2015-09-20 16:41:21 UTC

(In reply to raal from comment #12)
> (In reply to ralf.gandenberger from comment #11)
> > (In reply to raal from comment #4)
> > > Which cell is bad?
> > > row 152:
> > > 183,77 	183,77		Mai	19 899,89 	55,72 	184,28 	240,00
> > 
> > Hi raal,
> > 
> > now that I have the correct calculation again: your results are correct. How
> > did you get them
> > 
> > Ralf
> 
> For recalculation of the sheet you can use CTRL+SHIFT+F9 shortcut key. You
> can set automatic recalculation in options->LO Calc-> Formula ->
> Recalculation on file load.
> Please provide step by step instructions how to reproduce the bug.

raal,

I have the settings you describe. Unfortunatelly there is no reproduction of the bug. I have different results every time I open the file. Sometimes the bug disappears in one row after I copy the formula and paste it again into the same cell. But the a wrong result is in a different cell. If I do the copy and paste exercise there, the issue may be solved there, but is in another place. It is never correct in the whole file.

From the comments of the other users it seems to me it has something to do with Libre Office run on Windows. Do you run a Windows OS ?

Ralf

Comment 14 MM 2015-10-15 21:15:46 UTC

You might wanna check out bug 94924
If I turn off 'allow use of opencl', I get the right values.

Comment 15 ralf.gandenberger 2015-10-21 16:02:02 UTC

Oh MM, I'm soooo grateful, many thanks for the hint. It really solves the bug in my file.

Comment 16 Joel Madero 2015-10-21 22:55:08 UTC

Closing as a dupe of that bug since it fixes the issue.

*** This bug has been marked as a duplicate of bug 94924 ***