Created attachment 155224 [details]
Screenshot of Calc showing formula incorrect and calculation result

This is normal: computers represent numbers as binary numbers, and not all decimal numbers are representable as finite binary floating-point numbers. E.g., a simple 0.1 is not finite in binary form.

See http://en.wikipedia.org/wiki/Floating_point#Accuracy_problems.

Users working with fractional numbers should account for that.

Closing NOTABUG.

(In reply to Mike Kaganski from comment #2)
> This is normal: computers represent numbers as binary numbers, and not all
> decimal numbers are representable as finite binary floating-point numbers.
> E.g., a simple 0.1 is not finite in binary form.
> 
> See http://en.wikipedia.org/wiki/Floating_point#Accuracy_problems.
> 
> Users working with fractional numbers should account for that.
> 
> Closing NOTABUG.

We can't seriously expect home & business users to know about IEEE 754, 'floating point' and 'mantissa'. A significant number of people have very limited knowledge of math. What our users will notice is that 31000.99 - 32000.12 does not equal -999.129999999997.

Microsoft Excel and Google Sheets do not exhibit this behaviour. Even VisiCalc from 1981 (https://www.pcjs.org/apps/pcx86/1981/visicalc/) does not exhibit this behaviour.

https://i.imgur.com/kUzGIaj.png

This is *not* a bug, and any spreadsheet software works that way - see the link above for MS Excel 2016 with the steps you described in comment 0 (B4 is set to show 12 decimals).

And this is GSheet showing the same: https://docs.google.com/spreadsheets/d/1moqvtgPINDUFFHE1ryusmR1nB47kJvnLs0A7OYcO7SE/edit?usp=sharing

*** Bug 130221 has been marked as a duplicate of this bug. ***

Now i'm getting a little frustrated ... 

these wrong calculations are! mistakes! period, duplicate ans 'NOTABUG' is misleading, 

they are errors because calc - and other spreadsheets? - give results that are not mathematically correct. 

one might discuss whether they are unsolvable errors and leave them as they are, but one shouldn't try to convince normal people with normal mathematical education that they are not errors. This will always and again cause endless discussions. 

ex$el and g$$gle have - according to comments in other threads - at least partially concealed the problem with rounding, @Mike has fixed a variant / consequence of these rounding errors in #129606, so improvements are! possible. 

i had suggested to check an eventually fundamental improvement in #130221 - allegedly the IEEE has taken the problem into account and with the IEEE standards 754-2008 and 754-2019 allows to calculate decimal numbers without rounding errors - 

imho it is worth at least a check or test if: 
- something like this could be implemented, maybe in future releases, and 
- if calc and the developers can get out of many stupid requests that way, and
- whether calc could positively distinguish itself from other spreadsheets by solving this issue. 

or if Mike's improvement from #129606 can be used for other cases as well ... 

the practice of dismissing any suggestion for improvement as a duplicate of old bugs (that no developer will look at anymore because they are grown too long and perhaps awkwardly presented at first), i feel to be ... suboptimal. 

it continues to provide the 'administrators' with a lot of administrative work, yes, but it doesn't move the project forward. 

see there: 

http://bugs.documentfoundation.org/attachment.cgi?id=157453

a sheet with errors and the improvements by @Mike from #129606, and there: 

https://bugs.documentfoundation.org/show_bug.cgi?id=130221#c0

something about old IEEE 754 not! being the last in possibilities

i'd like if someone reopens this bug, or points to another place where the issue can be discussed in depth ... 

reg. 

b.

two more comments ... sorry if I argue too persistently, i like improvements, and contradiction or hastily supressing bugs as duplicate (especially of those imho too hastily qualified as 'notabug') sometimes annoys me and sometimes pushes me to contra-contradiction ... : 

1. IEEE 754-2008 really promises to have a solution? 

wikipedia about f.-p. accuracy problems and formats and a solution: 

https://en.wikipedia.org/wiki/Floating-point_arithmetic#Accuracy_problems

citation: 

'As decimal fractions can often not be exactly represented in binary floating-point, such arithmetic is at its best (...) , and at its worst when it is expected to model the interactions of quantities expressed as decimal strings that are expected to be exact. An example of the latter case is financial calculations. For this reason, financial software tends not to use a binary floating-point number representation. The "decimal" data type of the C# and Python programming languages, and the decimal formats of the IEEE 754-2008 standard, are designed to avoid the problems of binary floating-point representations when applied to human-entered exact decimal values, and make the arithmetic always behave as expected when numbers are printed in decimal.'

imho that qualifies for 'NOTABUG' being wrong, and a request for improvement (an enhancement request?), being a valid and justified proposal as it would be possible, 

just my two cents ...

2. for those poor people who have to do financial / accountancy calculations in spreadsheets ... :-( ... i have compassion with you ... a trick used by some banks as well: 

do the calculations with integer values representing the 'cents' (or whatever your currency defines as minimum fractional part of it's units), and divide the results for display if neccessary ... 

e.g. =0,1 EUR + 0,1 EUR ... or similar may fail in some situations, while =10 cent + 10 cent ... (divided by 100) has a better chance for an exact result as most integers have an exact representation in binary, (and in floating point / IEEE 754?) 

(not tested - short in time - just a stolen idea, but looks promising) 

(pls. no arguing that 0,1 + 0,1 and most calculations will 'show' correct results when displayed with 2 decimal digits, yes, i'm through with all that stuff, problems will step in once you have 'if([0,12]=[0,12])' being false reg. comparing 0,119999999... with 0,12000000000001... or similar, or when rounding too often and too early to avoid carrying fractions, and thus sums of rounded values deviate against the rounded of a sum, and different parts of your software / your sheet use different approaches to the result ...) 

(we / you had a proposal for IEEE 754-2008 5 years ago, see #76245, still unanswered, and other bugs e.g. problems with large values as well, see #37923 and plenty bugs cited there) 

reg. 

b.

(In reply to b. from comment #7)

Bug 130221 was rightfully closed as duplicate of this; and this was rightfully closed NOTABUG.

> these wrong calculations are! mistakes! period, duplicate ans 'NOTABUG' is
> misleading, 
> 
> they are errors because calc - and other spreadsheets? - give results that
> are not mathematically correct. 
> 
> one might discuss whether they are unsolvable errors and leave them as they
> are, but one shouldn't try to convince normal people with normal
> mathematical education that they are not errors. This will always and again
> cause endless discussions. 

Now you mix *three* different things: "bug", "mistake", and "error"; and also use ambiguous word "misleading". Bug tracker is only and exclusively a tool for tracking *bugs* of this software, not anything that is not a bug, even if it has "error" from mathematical point of view. So *first* thing to learn and accept is to avoid any reference to "errors", and only speak about bugs. Bug tracker does not have to "convince normal people" in anything; it simply states if something is *issue in the software*. If you don't accept that, you are spammer. Period.

Spreadsheet applications are created to perform machine calculations. They have well-known limitations, like those that result from requirement of performance: these applications are necessarily only perform mathematics on system hardware (e.g., CPU), without using complex software calculations, which could in theory provide some different (and sometimes mathematically more correct) results. That is not something to be changed in Calc in any foreseeable future; and unless there is a way to perform more precise calculations on hardware, any duplicate of this bug is NOTABUG (but ignorance of submitter, or in case when someone has already been informed, stubbornness or trolling).

Currently, the predominant computer architecture uses binary representation of numbers, specifically in case of floats (which are universally used for any numbers in spreadsheet software) - IEEE 754-1985. These numbers represent arbitrary decimal numbers with limited and specified precision; hardware operations on these representations give specific *errors* in results (which are *not* bugs!); and that is expected and normal state of things. This is the state of the art and standard of the industry; and again - this is not to be "discussed in depth", or argued with developers, wasting their time the thousandth time in need of explaining the same thing to just another person who happens to dislike reading what is already told many times.

> ex$el and g$$gle have - according to comments in other threads - at least
> partially concealed the problem with rounding,

That was shown false: see comment 4 and comment 5. Repeating a false statement ad nauseam does not make it true.

> @Mike has fixed a variant /
> consequence of these rounding errors in #129606, so improvements are!
> possible.

A fix in an interactive operation that is known to (1) usually require small precision; (2) not to have mission-critical consequences of small (in)accuracies resulting from applied extra rounding; and (3) be not performance-critical (because it's a UI operation) can *not* be extrapolated to any other calculation in spreadsheet, for which any of these points might not hold. E.g., even ignoring accuracy problems that might arise from these roundings (guess what: if there were universal rounding algorithms that magically make *binary number* calculations *more* correct, they would be already be in standard, and implemented), the operation would add huge performance penalty, which would put system working with any descent spreadsheet to its knees.

> i had suggested to check an eventually fundamental improvement in #130221 -
> allegedly the IEEE has taken the problem into account and with the IEEE
> standards 754-2008 and 754-2019 allows to calculate decimal numbers without
> rounding errors - 
> 
> imho it is worth at least a check or test if: 
> - something like this could be implemented, maybe in future releases, and 
> - if calc and the developers can get out of many stupid requests that way,
> and
> - whether calc could positively distinguish itself from other spreadsheets
> by solving this issue. 

A new standard does not change anything by itself. Are you such a day-dreamer that you imagine that any standard would magically get implemented in your hardware just because it has magic "IEEE" letters? Read the Wikipedia article [1], which explicitly tells "Discussed but not included". No hardware implements that standard; neither there is a hardware implementing IEEE 754-2019, so nothing to discuss here, too.

> the practice of dismissing any suggestion for improvement as a duplicate of
> old bugs (that no developer will look at anymore because they are grown too
> long and perhaps awkwardly presented at first), i feel to be ... suboptimal. 

Your feelings are irrelevant here. Any issue in bug tracker has, as already mentioned, only one purpose: allow *developers* have a tidy list of something to work on. This issue is nothing to work on. You need to tune your expectations.

> it continues to provide the 'administrators' with a lot of administrative
> work, yes, but it doesn't move the project forward. 

... and exactly closing something that is not to be worked on *does* help move project forward, just by making a clear list of actually improvable stuff. Bug tracker is not a place for metaphysical muses about "what place this works should have been".

[1] https://en.wikipedia.org/wiki/IEEE_754-2008_revision

(In reply to Mike Kaganski from comment #9)
> "what place this works should have been".

A typo: should read "what place this world should have been".

(In reply to b. from comment #7)
> ... one shouldn't try to convince normal people with normal
> mathematical education that they are not errors. This will always and again
> cause endless discussions. 

This, by the way, only tells that "normal" mathematical education is not adequate. See e.g. this lab [1] from Department of Mathematics of Boise State University (just a random result found on Internet search for "finite precision mathematics").

[1] https://math.boisestate.edu/~calhoun/teaching/matlab-tutorials/lab_19/html/lab_19.html

*** Bug 130356 has been marked as a duplicate of this bug. ***

as #130356 shows: as long as this problem is not solved there will be questions ever and ever again. 

@Mike expects users to adapt to the limitations of spreadsheets. 

@'common user' expects spreadsheet to do correct calculations. 

(@common user is the majority ...)

i couldn't yet find out if IEEE 754-2008 decimals are already available 'in hardware' or need a software emulation, but they should calculate correctly. 

so i would appreciate if someone would take care of making them available in the program, or find another way to make the results more 'user friendly' - by clever rounding?. 

Otherwise it could be that other efforts - to make a good figure compared to ex$el and g$$gle and get users to switch - are neutralized because some - many? - users prefer to work with programs that don't always force them to think about the fact that a result looks wrong at first, and to do studies if this is a problem of the program or the calculations created by the user in this or that case. 

if 'decimals' can only be done with moderate performance, it could be that in many financial-mathematical sheets this is only a minor problem, since today's hardware is quite fast, and in financial sheets the calculations are classically summing up a lot, but normally have none to few complex 'power intensive' calculations. 

thus I suggest to turn this bug into an enhancement request with high priority. 

ok?

*** Bug 132990 has been marked as a duplicate of this bug. ***

Created attachment 165424 [details]
test_sum_save_against_fp-rounding

besides that i'd read somewhere that gcc has! a 'decimal' library, and thus implementing correct decimal math shouldn't be too big an issue ... 

(ok, 'performance', but plenty sheets are 'small', and most performance problems can be thrown on hardware today, the hours and days that users and supporters waste with irritations about fp-rounding-issues are in my opinion much worse than a few microseconds of computing time) 

... i was curious if it is possible to solve some of the FP problems with 'smart rounding', here is my second try, not a solution, just an attempt of a proof of concept, 

the user defined macro function 'sum_s' rounds the result acc. to the decimal places of the values, and returns a better result for '=SUM_S(31000.99, 32000.12)' than the standard 'sum' of calc does with '=SUM(31000.99, 32000.12)', 

i'm not! sure if this works for all cases / values, it's limited to two summands, it was tricky enough to get it to work because calc basic has no function max, it is 'unfinished work', i just want to 'throw it to the people' for examination and discussion ...  

the idea is to show that 'correct' results for calculations with decimal fractions are possible, with the means available at runtime of the program, even with the 'subset' of it available to the user, programmers and developers can do much more, 

the idea is not! that now all users should start to calculate with basic macros (although this might be a quick help for some of them), but to give an incentive that 'man', we, the programmers / developers try to find a way to become 'better than ex$el' in this respect, because that would be a 'really good thing' ... imho

*** Bug 156858 has been marked as a duplicate of this bug. ***