Looks like a precision issue, a '1' appears in the 15th digit after decimal place when copying and pasting. I had expected a result with 8 decimal places. Can anyone confirm if this issue is resolved in latest release or 7.1 ? =9.25178559 - 8.9818 0.269985590000001 Sorry I'm not using newer than v6.4.6.2
(In reply to Jonny Grant from comment #0) > > Can anyone confirm if this issue is resolved in latest release or 7.1 ? > Probably you won't get a confirmation about that, since there seems to be no issue and hence no fix. It doesn't look like an bug but correct result of IEEE 754 floating arithmetic (which is not to be confused with pure calculus in a math book).
(In reply to Uwe Auer from comment #1) > (In reply to Jonny Grant from comment #0) > > > > Can anyone confirm if this issue is resolved in latest release or 7.1 ? > > > > Probably you won't get a confirmation about that, since there seems to be no > issue and hence no fix. It doesn't look like an bug but correct result of > IEEE 754 floating arithmetic (which is not to be confused with pure calculus > in a math book). I expected someone wouldn't want to fix this! We've seen this before with bugs in Calc. No user would ever expect to see 0.269985590000001 when they copy and paste the value into an email etc. Google Docs and Excel don't have this bug.
(In reply to Jonny Grant from comment #2) > No user would ever expect to see 0.269985590000001 when they copy and paste > the value into an email etc. Which makes a valid request, IMHO. Maybe the number of decimals can be limited for certain clipboard formats. However it's likely not simply dropping limiting it by 5 decimals but also rounding involved
(In reply to Jonny Grant from comment #2) > > Google Docs and Excel don't have this bug. Don't call it a bug. Probably Google Sheets and Excel perform some rounding based on some unknown idea. It's ok to request a change in arithmetic of the application, but it is not ok to call correct IEEE 754 calculation a bug.
There are numerous tickets on this topic. Not sure if bug 137679 helps here but there are likely other issues that fit the request. Not a topic for UX anyway since conversion into floating-point values lead to rounding errors. And Calc offers functions to deal with this.
(In reply to Uwe Auer from comment #4) > (In reply to Jonny Grant from comment #2) > > > > > Google Docs and Excel don't have this bug. > > Don't call it a bug. Probably Google Sheets and Excel perform some rounding > based on some unknown idea. It's ok to request a change in arithmetic of the > application, but it is not ok to call correct IEEE 754 calculation a bug. Firstly, thank you for your reply. As software developers, like yourself Uwe, I think we all understand IEEE floating point, double precision, rounding etc. Calc is a program for users, who do sums, for accounting etc. 9.25178559 has 8 digits of decimal precision, 8.9818 has 4. Of course, every piece of software knows the result would only have 8 digits of precision. It's appropriate to display only 8 digits, by whatever rounding Calc needs to do.
(In reply to Telesto from comment #3) > (In reply to Jonny Grant from comment #2) > > No user would ever expect to see 0.269985590000001 when they copy and paste > > the value into an email etc. > > Which makes a valid request, IMHO. > > Maybe the number of decimals can be limited for certain clipboard formats. > However it's likely not simply dropping limiting it by 5 decimals but also > rounding involved Firstly, thank you for your reply. What I would ask is - what would a user expect? For me, users would expect a number with 8 decimal places, the same as the input number. Feels like an easy fix...
HAHAHA! (SCNR)
We're trying to round and due to the rounding even have some wrong display strings resulting of the rounding to 15 decimals, but that 0.269985590000001 *is* already rounded; so yes, the entire display string conversion from binary IEEE 754 to decimal needs to be reworked, but any "user expects" and "easy fix" claim is just off. Decimal input 9.25178559 nearest binary IEEE 754 representable value 9.251785590000000780719346948899328708648681640625 (1.001010000000111010100000101001110101010011101110001 * 2^3) Decimal input 8.9818 nearest binary IEEE 754 representable value 8.98179999999999978399500832892954349517822265625 (1.00011111011010101110011111010101011001101100111101 * 2^3) Subtraction: 1.0001010001110111000110100011110111000011110111000000 * 2^-2 0.269985590000000996724338619969785213470458984375 rounded to 15 decimals 0.269985590000001 Yes, unfortunately rounding to 15 decimals is not enough *in this* case, but 14 would be even more wrong in other cases, and sometimes 17 are required.. Fwiw, the nearest representable binary for 0.26998559 is 0.26998558999999999752361645732889883220195770263671875 (1.000101000111011100011010001111011100001111011010111 * 2^-2)
(In reply to Eike Rathke from comment #9) > We're trying to round and due to the rounding even have some wrong display > strings resulting of the rounding to 15 decimals, but that 0.269985590000001 > *is* already rounded; so yes, the entire display string conversion from > binary IEEE 754 to decimal needs to be reworked, but any "user expects" and > "easy fix" claim is just off. Thank you for you reply. Feels like it's not IEEE rounding that's the issue, it's LibreOffice code, as you say wrong precision is being used, and is then even in the copy paste buffer! Why doesn't the LibreOffice code just use the correct decimal place it already knows? 8.9818 has 4 decimal places. 9.25178559 has 8 decimal places. Seems simple to me, use the 8 decimal places LibreOffice already knows? Here is another example of this issue: =9.25168559 - 8.9818 0.269885589999999
You seem to not understand. Once the value is in the IEEE 754 binary format there are no "8 decimal places" to use. It's about sufficient precision and rounding when converting from binary floating-point to the decimal display string. See also https://erack.de/bookmarks/D.html#010203 Or if you want to dive deep into then https://erack.de/bookmarks/D.html#Computer_Arithmetic_and_IEEE_754
(In reply to Eike Rathke from comment #11) > You seem to not understand. Once the value is in the IEEE 754 binary format > there are no "8 decimal places" to use. > > It's about sufficient precision and rounding when converting from binary > floating-point to the decimal display string. > > See also https://erack.de/bookmarks/D.html#010203 > Or if you want to dive deep into then > https://erack.de/bookmarks/D.html#Computer_Arithmetic_and_IEEE_754 Dear Eike My apologies if I was not clear. I only refer to the decimal display of the IEEE 754 number. More specifically, when I press <Ctrl>+C, and <Ctrl>+V in an email, i see 0.269985590000001 I'm only referring to the string conversion of this number which is stored in floating-point format by Calc. Calc needs to display as a string an appropriate number of decimal places to for the result, and the copy&paste buffer should contain the same, that should also be used so that copy&paste works as expected. Here is an even simpler example :- 1) Type =10.1 - 9.9 2) Observe 0.2 3) <Ctrl>+C 4) Move to your text editor and press <Ctrl>+V 5) Observe 0.199999999999999 6) Switch back to Calc, resize the column 7) Observe 0.199999999999999 I'll attach another screenshot Can the string conversion function take a hint on how many decimal places are needed? In this example, it will never be more than 1 decimal place needed right?
Created attachment 168339 [details] Another example
*** Bug 139095 has been marked as a duplicate of this bug. ***
> Can the string conversion function take a hint on how many decimal places are needed? That's exactly what's happening in a narrower column and the reason 0.2 is displayed when rounding occurs for less than 15 decimals. > In this example, it will never be more than 1 decimal place needed right? That's unknown. Copy-pasting via clipboard there's no information how much precision the target needs.
(In reply to Eike Rathke from comment #15) > > Can the string conversion function take a hint on how many decimal places are needed? > That's exactly what's happening in a narrower column and the reason 0.2 is > displayed when rounding occurs for less than 15 decimals. Sounds like that's the fix for the copy-paste buffer. Although surely the fix is to just display accurately with appropriate rounding no matter the column witth? My Casio calculator has 8 decimal display, it only shows 0.2 Casio never shows "0.1999999" > > In this example, it will never be more than 1 decimal place needed right? > That's unknown. Copy-pasting via clipboard there's no information how much > precision the target needs. I don't want to sound like a broken record, it's obvious the user only needed 0.2 right? What user would ever want 0.199999999999999 ?
There are numerous bug reports concerning floating-point precision, even with such simple examples as 0.2+0.2 producing a display of 0,399999999999999999. There is an excellent FOSS calculator called SpeedCrunch where they state the code may be incorporated into any product you desire. Their precision is not subject to the perennial floating point issue. Is it worth one of the LO developers taking a look at their code to ascertain whether it could inspire a solution for LO? http://speedcrunch.org/
(In reply to Colin from comment #17) > Is it worth one of the LO developers taking a look at their code to > ascertain whether it could inspire a solution for LO? It does not use IEEE 754 double floating-point values but calculates with decimal character strings.
IEEE 754 calculations aren't imprecise in total, just some special cases are 'weak' from which 'cancellation' strikes here, the subtraction of two values of similar magnitude crosses five 'binary ranges' from 'operand to result' in this example (+3 to -2) and thus is invalid in at least in the last five bits, this 'pulling' pulls the small deviation of the bin representation of 9.25178559 (which's bit-string ~00111011100001 | 1000111101~ is truncated at the position of the '|' and rounded up to ~00111011100010 as the first chopped digit is a '1') into relative 'higher position' and it becomes visible to the user, the small opposite deviation of 8.9818 having lost '111' three bits behind the end of it's string corrects a little portion of that, but stays invisible, the first thing to understand is that we don't have a display string problem here, but really a wrong value, (wrong only from a 'decimal view', IEEE 754 doesn't care for or know about the source and / or deviation of a value, and thus cannot deal with it, in binary the calculation is correct and doesn't inject additional deviation) the first thing @Jonny Grant needs to understand is that it's easy to find a better handling for this one case, but difficult to find something which works for all cases and doesn't break other calculations, reasonable correction can only be applied by the application program or a calculation library knowing operands and result and applying meaningful correction !directly after the calculation!, lateron the neccessary info is lost, the first decision to make is 'do we want decimal correct results?', @Mike Kaganski argued heavily against it in https://ask.libreoffice.org/en/question/290711/calc-are-decimal-correct-calculations-possible/?sort=latest, 'if' we want then rounding is a way to achieve, i provided samples in above thread and in tdf#130725, they are not 'finally perfect' but a valid proof of concept, i paused working on it as i got the impression @Mike Kaganski would block implementation, correcting fp-artefacts by rounding needs: 'correct rounding!', @erAck did much for it, should be tested if finally correct, and it needs info about the magnitude of the operands to have qualified info up to which digit they may contain valid value, and thus where to set the knife, this can be calculated in binary, but :-( the final step rounding to decimal positions is 'slow' as it requires a conversion into decimal, and somewhat error prone as it - and the rounding and the conversion back into binary - may inject additional errors?, imho better results are possible, desirable, justify the effort and the performance impact should be acceptable if implemented in code instead of user-macros, but that's just my subjective opinion, if 'the community' wants it differently it will probably stay as it is ... :-( @erAck: 'but that 0.269985590000001 *is* already rounded;' - ??? to which amount of decimal digits? rounding it to any value between 8 and 14 corrects the problem, '15' cannot help as the error is in 15'th decimal and significant digit, '8' would be appropriate here because the result can't contain valid value after this position based on its operands, a meaningful 'rounding range' cannot be a fixed value, but must be chosen based on the operands and the arithmetic operation
Dear Eike Rathke, This bug has been in ASSIGNED status for more than 3 months without any activity. Resetting it to NEW. Please assign it back to yourself if you're still working on this.
correction to c#19: > 'the small opposite deviation of 8.9818 having lost '111' three bits behind the end of it's string corrects a little portion of that, but stays invisible,' was wrong, sorry, as it is the subtrahend! being short this deviation of about 2.1600499E-16 adds! to the result of the subtraction further enlarging it. but the main problem is the 'cancellation' combined with 'FP representation imprecision'. calculating '9.25178559 - 9' ( '9' is exact ) pulls the positive devia of 9.25178559 ( ~ -7.8071935E-16 ) upfront and makes it visible. a more clear example? '=399999999999999.1 - 399999999999999' -> 0.125 is good in demonstrating the evil. devia > 2 in the second significant decimal digit or 25%! of a decimally correct result. That would be 'IEEE precision' which Calc claims but doesn't deliver. Calc tries to - similar to Excel - avoid some problems by restricting some processes - e.g. display of non integer values - to 15 digits while internally continuing with crooked values. such can cover some issues, but often throws few bigger problems elsewhere. As long as one does not have a good solution idea for such - which IMHO would require a radical rethinking for the understanding and handling of floating point numbers - it is to a large extent pointless to change individual such issues, it mostly only moves the problems instead of solving. e.g. Calc's and Excels restricting input and display to 15 decimal digits, while still using crooked values between that internally, leads to disabling 399999999999999.5 as input, calculating 'value( "399999999999999.5" )' to 400000000000000, and that minus 399999999999999 to '0', a 100% wrong result. as it's unlikely that Calc will come up shortly with a solution for a problem plenty other people haven't solved in the last decades, I'd propose to file some general 'prototype' FP precision bugs, e.g. - 'operand deviations adding up', - 'operand deviations adding up with in calculation rounding', - 'snap-to' prettyfying, - rounding 'ties to even', - 'cancellation' ... with qualified explanations, and mark the mass of existing and emerging FP-precision bug reports each as a duplicate of the most appropriate prototype. This could save a lot of work in managing of and responding to individual reports.