Bug 126481

Summary: An option for Rounding mode in ScInterpreter::ScRound
Product: LibreOffice Reporter: himajin100000
Component: CalcAssignee: Not Assigned <libreoffice-bugs>
Status: RESOLVED WONTFIX    
Severity: normal CC: erack, miguelangelrv, oliver.brinzing, rb.henschel
Priority: medium    
Version: 6.4.0.0.alpha1+   
Hardware: All   
OS: All   
See Also: https://bugs.documentfoundation.org/show_bug.cgi?id=61512
Whiteboard:
Crash report or crash signature: Regression By:

Description himajin100000 2019-07-19 17:57:57 UTC 
Description:
see steps to reproduce

Steps to Reproduce:
1. Open Calc
2. Enter =ROUND((105 x 1.75 + 50) / 1.1) to A1
3. Do the same thing in Excel
4. Compare the result


Actual Results:
Excel: 213
Calc : 212

I doubt
Excel is doing calculation with RoundingMode_Corrected
https://opengrok.libreoffice.org/xref/core/sc/source/core/tool/interpr2.cxx?r=edcbe8c4#1010

Expected Results:
Calc should have an option in "Detailed Calculation Settings" for choosing rounding modes for the compatibility with Excel and for the compatibility with older version of Calc


Reproducible: Always


User Profile Reset: No



Additional Info:
Comment 1 himajin100000 2019-07-19 17:59:27 UTC 
oops,typo:
=ROUND((105 * 1.75 + 50) / 1.1)

I just copied the formula from another reporter
Comment 2 m_a_riosv 2019-07-19 20:57:16 UTC 
Looks like some kind of bug:

a1: 212,50000000000000000000	=(105 * 1,75 + 50) / 1,1
a2: 212,50000000000000000000	=ROUND(A1;16)
a3: 212,00000000000000000000	=ROUND(A1)
	
a5: 212,50000000000000000000	=VALUE("212,499999999999999999")
a6: 213,000	=ROUND(A5)
Comment 3 Regina Henschel 2019-07-20 11:27:52 UTC 
I think, that is nothing about rounding. That are the usual problems with converting between decimal and binary numbers and with double arithmetic.
For example compare (show 15 decimal places)
=233.75/1.1-212
=233.75*(1/1.1)-212
or
=100/3-33
=100*(1/3)-33
=1000/30-33
=10/0.3-33
[The purpose of the parts -212 and -33 is to make differences in the last bits visible.]
Comment 4 Eike Rathke 2019-08-01 13:30:38 UTC 
Problem is the actual underlying floating point value of =(105 * 1.75 + 50) / 1.1 is 212.49999999999997 and only for display rounded to 212.5 as it is within the accuracy margin, you can check with =RAWSUBTRACT(212.5;(105 * 1.75 + 50) / 1.1) that produces 2.8421709430404E-14

There's not much we can do here (except always displaying the "real" floating point value and get whining users). For 0 decimals (round to integer) ROUND() effectively calls std::round() to avoid bad "correction" resulting in wrong values, see bug 96821.