I followed a few rounding error bugs. And the issue of rounding errors seem pretty difficult to resolve. It might be useful to know that some accounting/bookkeeping applications internally store and process factions as divisions (0,5 > 1/2). Divisions can be managed with simple arithmetic calculations. Only when the resulting value needs to be displayed, the fraction is shown. Consider this a friendly FYI and feel free to ignore.
Thanks for the suggestion, Ceaus. Which accounting/bookkeeping applications would that be? Eike, would you like to comment?
Comment what? All major spreadsheet implementations use IEEE 754 floating point double precision to store values, accuracy deficiency and rounding errors are inherent and well known, see https://help.libreoffice.org/latest/en-GB/text/scalc/01/calculation_accuracy.html?DbPAR=CALC and https://wiki.documentfoundation.org/Faq/Calc/Accuracy Asking to store fractions ("divisions") internally instead of binary floating point values doesn't help much as that would already be lost when storing the value in any of the commonly used file formats, unless also the storage file format is changed. Besides that, processing fractions in calculations would come with a performance penalty and would require an entire rewrite of the interpreter calculation engine. Something very unlikely to happen. Yes, real accountancy software like GnuCash does not use floating point values but fractions, both internally and stored to file, and real accountants also don't use spreadsheets to do serious accountancy.
Thanks Eike, that's exactly the kind of comment I was after :) I can point to the calculation accuracy help page but can't confidently comment on lower level tidbits like you can. Ceaus, hope that justifies closing as "won't fix" for you too.
Wow, that's quite a snappy response. It was just a friendly suggestion... (In reply to Eike Rathke from comment #2) > Comment what? All major spreadsheet implementations use IEEE 754 floating > point double precision to store values, accuracy deficiency and rounding > errors are inherent and well known, see > https://help.libreoffice.org/latest/en-GB/text/scalc/01/calculation_accuracy. > html?DbPAR=CALC and https://wiki.documentfoundation.org/Faq/Calc/Accuracy > > Asking to store fractions ("divisions") internally instead of binary > floating point values doesn't help much as that would already be lost when > storing the value in any of the commonly used file formats, unless also the > storage file format is changed. Besides that, processing fractions in > calculations would come with a performance penalty and would require an > entire rewrite of the interpreter calculation engine. Something very > unlikely to happen. > > Yes, real accountancy software like GnuCash does not use floating point > values but fractions, both internally and stored to file, and real > accountants also don't use spreadsheets to do serious accountancy.
Ceaus, have you thought about the use of explicit fractions as a number format? Format | Cells | Numbers , then select "Fractions" in the category, then make a choice in the list of specific formats. Also, please have a look at my bug 158219.
(In reply to Eyal Rozenberg from comment #5) > Ceaus, have you thought about the use of explicit fractions as a number > format? No, I've not. Spreadsheet use for me personally doesn't cover more than the usual suspects for work chores. So I don't have a stake here. However I've always wondered why there are so many issues related to the rounding of numbers (each one every time a little bit different), where we only try to fix that local border case. Apparently there's no incentive to take up a more fundamental view in what numbers mean to people, how they are displayed, and how they are stored (as per your examples). That makes the argument "All major spreadsheet implementations use IEEE 754 floating point double precision to store values, accuracy deficiency and rounding errors are inherent and well known" a non-argument and tone deaf. It limits the problem to be discussed in terms of floating point storage capabilities. As if floating point storage is the only option. Closing the discussion with "real accountants also don't use spreadsheets to do serious accountancy" is just condescending and so much misses the point. But hey, I'm just a random dude with a idea...