Bug 80017 - EDITING: Rounding error in simple calculation - no division or multipliction used
Summary: EDITING: Rounding error in simple calculation - no division or multipliction ...
Status: RESOLVED DUPLICATE of bug 67026
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.2.0.4 release
Hardware: Other Windows (All)
: medium major
Assignee: Not Assigned
URL:
Whiteboard: BSA
Keywords:
Depends on:
Blocks:
 
Reported: 2014-06-14 09:10 UTC by Andrew
Modified: 2015-04-23 03:24 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
See cells J21 and J29 for error (18.63 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-06-14 09:10 UTC, Andrew
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Andrew 2014-06-14 09:10:08 UTC
Created attachment 101032 [details]
See cells J21 and J29 for error

Problem description: 
On a spreadsheet where all numbers entered have only 1 or 2 significant decimal digits and ALL operations are either subtraction or addition: the calculations sometimes introduce a rounding error.

The only other instruction used is a IF statement which should have no impact on the calculation per se.

 
Steps to reproduce:
1. See attached file in cells J21 and J29

              
Operating System: Windows 7
Version: 4.2.0.4 release
Comment 1 Luuk 2014-06-14 10:44:27 UTC
I copied your example, and opened it with Excel 2013
I got the same results.

Apparently, when the result of  this is 'wrong':
 -9425.03 - -9268.98 = -156.050000000001

But also:
 -9000.03 - -9270.98 = 270.949999999999

If you ROUND the result on 2 decimals, it will compare OK again... ;)

I think this is 'the way it works'-behaviour ?

Even Microsoft created a page about this:
http://support.microsoft.com/kb/214118
Comment 2 Andrew 2014-06-14 17:32:31 UTC
Luuk,

if there were divisions and multiplications I would agree with you ... actually I would never of brought up the issue.

HOWEVER - the spreadshhet contains ONLY subtraction and addition operations which should NEVER INTRODUCE rounding errors. If you look at the Microsoft example there is a multiplication.

I repeat (because this is the second time I bring up the subject): a spread sheet with only "+" and "-" operations SHOULD NEVER CONTAIN ROUNDING ERRORS.

Andrew
Comment 3 Luuk 2014-06-14 17:51:45 UTC
@Andrew: I agree with you that this error should not happen with only "+" and "-" oprations.

I also see that Excel 2013 is doing the same thing as LO 4.2.4.2 (which i'm looking at, see my second comment)
[ B.T.W.: This is NOT the same as claiming that Excel is GOOD, and that LO should behave the same way was Ecel does!!! ]

i will leave this at 'UNCONFIRMED', and wait for someone else to give his/her opinion on this, and mark this as 'CONFIRMED' or as 'IMPROVEMENT'...... ;-)
Comment 4 Peter Underdown 2015-04-14 14:52:37 UTC
(Props to all contributors-- thank you for a great free product)

Just want to add my voice to calling this behavior a bug, requesting that it be seen in the following light: a user (such as myself today) can get bizarre results that are hard to find the cause of. And I'm a pretty good debugger of things. I was comparing calculated quantities (a running balance) to entered quantities, a very useful way of verifying data entry. My sheet was, as usual, formatting the results with 2 decimal places. So the failure of equality tests was perplexing. 
So in short, regardless of its presence in other software, this behavior can cause serious confusion and waste of time as each user encounters it for the first time and has to track down for themselves what is going on. It doesn't even matter whether they do so as I did by investigative debugging or by querying the (excellent, by the way) help and support system. Either way, it shouldn't be a part of normal workflow. Nobody expects rounding errors from addition. Strictly speaking this is not even rounding error-- no rounding is needed for the calculation, so the proper characterization is calculation error. 

Thanks
Comment 5 Matthew Francis 2015-04-15 16:51:00 UTC
This is an unavoidable consequence of the use of floating point numbers to store numbers. The numbers which are being subtracted can't be precisely represented, and so the result of the subtraction is marginally different from what is compared to.

While there are other ways to represent numbers for computation, floating point is used because arbitrary precision costs much more in processing time, which would severely limit the size of spreadsheet calculations which could be performed in real time.

The general solution to this issue when working with floating point numbers is to test whether a number (such as a calculation result) is within a very small amount of what it is compared to, rather than for precise equality.


For a related discussion of the same topic in Microsoft Excel, see:

http://en.wikipedia.org/wiki/Numeric_precision_in_Microsoft_Excel
specifically the section
"Subtraction of Subtraction Results"

*** This bug has been marked as a duplicate of bug 67026 ***
Comment 6 Andrew 2015-04-22 20:26:51 UTC
Sorry but I don't agree with the resolved status due to duplication with bug 67026. I understand that both explain the problem in terms of floating point representation but bug 67026 goes on to talk about multiplication and division whereas 80017 talks ONLY about adding and subtracting.

Maybe I'm being overly picky and obnoxious but subtraction and addition CANNOT - I REPEAT CANNOT - be concerned with rounding error. How Excel and Calc choose to represent numbers should take this very basic mathematical axiom into account. NO MATTER what I do something like the example in the Wikipedia article (http://en.wikipedia.org/wiki/Numeric_precision_in_Microsoft_Excel#Subtraction_of_Subtraction_Results)

should never have to be checked, especially as we're talking about simple numbers with 3 decimal digits.

Maybe - I am being facetious - Calc and Excel need to introduce an option to represent numbers within a certain range in a real number notation which guarantees reliable representation and manipulation of simple numbers.

Why am I so uptight about this subject? As an ex-programmer and systems analyst (Assembler, C & C++) I cannot accept that basic, axiomatic mathematics be totally ignored.
Comment 7 Matthew Francis 2015-04-23 03:24:36 UTC
Other tools exist to perform calculation with infinite precision, with all the trade-offs that entails. However, that's a different niche to that filled by a spreadsheet.

If you enable
  Tools - Options - LibreOffice Calc - Precision as shown
then the displayed results in cells will be able to be compared precisely regardless of minute differences in the floating point result. That still won't allow formulae which internally generate imprecise floating point results such as =IF(C21-F21<>I21,"ERROR "&(C21-F21-I21),"OK") to succeed, but if you store the result of C21-F21 in a cell and then compare with I21, that will then compare exactly.
("Precision as shown" should be used with caution given its behaviour of discarding all precision beneath that which is displayed)


Changing the whole mathematical basis for how Calc works essentially isn't going to happen, so closing this bug again.

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