Bug 134168

Summary:	LibreOffice Calc wrong calculation
Product:	LibreOffice	Reporter:	anonimou_eu
Component:	Calc	Assignee:	Not Assigned <libreoffice-bugs>
Status:	CLOSED NOTABUG
Severity:	normal	CC:	erack, himajin100000, xiscofauli
Priority:	medium
Version:	4.1 all versions
Hardware:	All
OS:	All
Whiteboard:
Crash report or crash signature:		Regression By:
Attachments:	Wrong math on formulas

Description anonimou_eu 2020-06-20 18:57:13 UTC

Description:
A simple multiplication is giving me wrong results.

On the attached sheet, you can see below the "Total Investido" marked in red the formula and checking the results, it is giving me wrong results, in many repetitions.

Steps to Reproduce:
1. "Recursive" Formula: =IF(E36<0;G35;(G35*SUM(E$5:E35)+F36*E36)/SUM(E$5:E36))
2. Copy the value of last line of the "recursion"
3. Multiply it by another result from other formula (a simple SUM)


Actual Results:
Multiplication like 147*147.91 gives me 21742,53 while it should be 21742.77
123*189.88 = 23354,64 (expected 23355.24)
116.32*229 = 26638,12
 




Expected Results:
Wrong math resulted.


Reproducible: Always


User Profile Reset: No



Additional Info:
I'm not really sure how to report it under the "steps to reproduce" and "results" because it seems to be a internal error on how the calculation is done. Rounding?

I'm not really sure how to reproduce it, not using the formulas described on the file attached.

Comment 1 anonimou_eu 2020-06-20 18:58:33 UTC

Created attachment 162232 [details]
Wrong math on formulas

Hopefully, no personal identifiable information on the file :o)

Comment 2 Xisco Faulí 2020-07-13 14:50:29 UTC

Reproduced in

Version: 7.1.0.0.alpha0+
Build ID: 00be56d9db396d284f66ab723d6fbb898b888dfb
CPU threads: 4; OS: Linux 4.19; UI render: default; VCL: gtk3
Locale: en-US (en_US.UTF-8); UI: en-US
Calc: threaded

Version: 5.2.0.0.alpha1+
Build ID: 5b168b3fa568e48e795234dc5fa454bf24c9805e
CPU Threads: 4; OS Version: Linux 4.8; UI Render: default; 
Locale: ca-ES (ca_ES.UTF-8)

Version 4.1.0.0.alpha0+ (Build ID: efca6f15609322f62a35619619a6d5fe5c9bd5a)

@Eike, I thought you might be interested in this issue

Comment 3 Eike Rathke 2020-07-14 09:43:09 UTC

Not a bug.

There is accumulated inaccuracy in the results you can see when applying the General format instead of 0.00, it already starts at G6 132.025 (which itself is correct but maybe unexpected by you) and G7 148.1882 and G8 148.834150943396 ... the visual value 147.91 is in fact 147.908396164021 so multiplying that with 147 of course gives 21742.5342361111

You need to round the calculations as desired, here 2 decimals, so in G6
=IF(E6<0;G5;ROUND((G5*SUM(E$5:E5)+F6*E6)/SUM(E$5:E6);2))
and copy down.

Either that, or activate "Precision as shown" under Tools -> Options -> Calc -> Calculate, which is more expensive though and maybe undesired for other formulas.