Bug 158487

Summary:	Calculation bug in LibreOffice Calc when working with €
Product:	LibreOffice	Reporter:	Mike <mike>
Component:	LibreOffice	Assignee:	Not Assigned <libreoffice-bugs>
Status:	RESOLVED NOTABUG
Severity:	normal	CC:	miguelangelrv, mike
Priority:	medium	Keywords:	difficultyInteresting
Version:	7.3.7.2 release
Hardware:	All
OS:	All
Whiteboard:
Crash report or crash signature:		Regression By:
Attachments:	Example what I though was a bug

Description Mike 2023-12-02 07:25:46 UTC

I found a bug in LibreOffice Calc 7.3.7.2 in calculating money in €.
It looks though that it could also happen with any currency.
If you set the cells to € with 2 decimals like € 1234,00, it does not calculate with those 2 decimals but with the number it has calculated.
I made a sheet where it had to calculate 25% of a number in € with 2 decimals.
In that situation I had to add 2 values in €, one being the calculated number in € and the calculation was € 0,01 off.
That is because the number is not rounded up like people expect but Calc seems to just drop the third decimal it calculated.
I investigated it and found out that the calculation is done with the calculated number where the third decimal was a 5.
In one column I had 6 of those and so the calculation was wrong by € 0,03.
In my opinion it is a critical bug because it means that you cannot trust the calculations that Cacl does with money!
And if you would be a in charge of finance for a company that uses LibreOffice Calc to calculate all your finances, that would be a reason to stop using it.
Now I also know that you can up the decimals of a cell in € from 2 to 3 of even more but in IT-land that is rated as a workarround and not a fix.
The way I see it there are 2 bugs in 1:
1) If you set the decimals to 2 it should also calculate with 2 decimals.
2) If you calculate 0,01 + 0,015 and round the number to 2 decimals, the outcome should be 0,03 and not 0,02.
In mathmathics rules, if you round a number than 1 to 4 rounds down and 5 to 9 rounds up.
That means if you have to round 0,015 to 2 decimals, it should become 0,02 and not 0,01.

Comment 1 Mike 2023-12-02 07:51:41 UTC

I just realized that in my situation it showed up in calculating money but of cause the rounding of numbers with more decimals as you want to show also means that faulty calculations in Calc can happen to al numbers it calculates.

Comment 2 m_a_riosv 2023-12-02 11:39:23 UTC

There is an option for this.
Menu/Tools/Options/LibreOffice Calc/Calculation - Precision as show.
null/C:/Program%20Files/LibreOffice/help/en-US/text/shared/optionen/01060500.html?System=WIN&DbPAR=CALC&HID=modules/scalc/ui/optcalculatepage/OptCalculatePage#:~:text=with%20Microsoft%20Excel.-,precision%20as%20shown,-Specifies%20whether%20to

Comment 3 Werner Tietz 2023-12-02 11:51:58 UTC

Cannot reproduce
input of:
0.015
0.01

shows with 2 decimal places:
0.02
0.01

and the sum of both with 2 decimal places _shows_ :
0.03

If you need also further calculations with the _formatted_ Values, you should probably use →→Tools→Options→LO-Calc→Calculate→→ [x]Precision as shown

Comment 4 Mike 2023-12-02 12:25:21 UTC

Created attachment 191190 [details]
Example what I though was a bug

Comment 5 Mike 2023-12-02 12:34:49 UTC

I added the document "test bug.ods"
This shows what I mean.
I found the setting "Precision as shown" and that is indeed exactly what solves this.
You can toggle it to see the difference in the calculation.
If you did not set "Precision as shown", the outcome of the 2 and 3 decimal calculations are exactly € 0,01 off.
Now I am running Linux Mint which comes with LibreOffice.
The default setting of "Precision as shown" is "Not activated".
I presume that on whatever OS you install it, that is also the dafault setting.
Why is this setting off by default?
Seems to me it should be on by default...