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
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
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
@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'...... ;-)
(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
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 ***
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.
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 ***
just to clear out a few points ... rounding already happens on the source data, plenty 'decimal-exact' values do not! have an exact floatung point representation, thus instead of 9425,03 something like 9425,0300000000006548 is stored, and thats probably not yet the actual value but something calc can calculate and display, such imprecisions can add up in additions, throw away precision or even the smaller value with summands of different magnitude, dramatically loose precision on subtractions of similar values etc. quite often accumulated imprecisions throw a value across a border where calc with it's 'approximate-equal' functionality (which is neccessary to circumvent the imprecisions) stops to say 'different but similar enough to be equal' and judges 'different', thus you get your errors, multiplikation and division are said to perform better, but are not totally 'safe' afaik, at least not in calc, 'unavoidable' is not correct, we do have the input values, we do have math rules about precision, we can round out - correctly - at least most of theese artefacts, calc doesn't regarding 'performance' and too few developers who'd like to enter this battle ... we are not! talking about decimal numbers with few decimal digits, we are talking to represent them with 'double precision binary float numbers', where already dec 0,1 is an infinite fraction ... as the simple 1/3 is in decimal, calcs quality improved over the time, but this bug is still in, tdf#67026 is still live, i hope the problem will be solved sometime, because only then one could use calc legally for financial calculations, '("Precision as shown" should be used with caution given its behaviour of discarding all precision beneath that which is displayed)' yes!!!, be careful with things like that, it's a silly brutal dump tool,
(In reply to b. from comment #8) > i hope the problem will be solved sometime, because > only then one could use calc legally for financial calculations, > Do you have a source where it states that any application can be used legally for financial calculations? If not, then .... If yes, then please share the link (at least for another spreadsheet piece of software)
(In reply to Luuk from comment #9) > Do you have a source where it states that any application can be used > legally for financial calculations? > > If not, then .... > If yes, then please share the link (at least for another spreadsheet piece > of software) imho german tax authorities, 'DATEV', most banks and stock exchanges and plenty 'approved' bookkeeping programs claim that, it overtaxes my capa and time to check whether they really meet this requirement, i have heard of two cases where stock exchanges did not, one was a small roundoff error which went unnoticed for month ... i dunno if other spreadsheets are better, i know that most use doubles and have fails, and that that's not announced by the vendors as it normally should be: microwave: 'don't use to dry your cat', Porsche: 'careful, faster than other cars', McDonald: 'careful, hot coffee may be hot', spreadsheets / calc: 'careful, the results are not necessarily accurate, expect marginal deviations in most and glaring errors in some cases' (may be that's somewhere in the rtfm's, i didn't as most users won't) i recall a few comments that considered Excel and calc to be 'not suitable for finance', and think from experience that both are mainly used for financial calculations, thus 'improvements appreciated' ...