Bug 126481 - An option for Rounding mode in ScInterpreter::ScRound
Summary: An option for Rounding mode in ScInterpreter::ScRound
Status: RESOLVED WONTFIX
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
6.4.0.0.alpha1+
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2019-07-19 17:57 UTC by himajin100000
Modified: 2019-08-01 13:30 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
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.