Hello Can anything be done, when specifying 13 decimal places to get it to not show a 1? Without me using clunky =ROUND etc throughout my spreadsheet. =8495.95 - 8208 287.9500000000010 I saw there are lots of tickets about how numbers are represented in floating point so when converted back to decimal due to rounding errors it's not perfect. I understand all of this, I don't need an explanation of floating point... I always recall "double" precision floating point has 15 decimal places. of course "float" 32 bit only has 7 digits of precision. Is Calc not using double precision?
(In reply to Jonny Grant from comment #0) > I understand all of this, I don't need an explanation of floating point... I > always recall "double" precision floating point has 15 decimal places. of > course "float" 32 bit only has 7 digits of precision. Is Calc not using > double precision? Are you sure you "understand all of this" and "don't need an explanation", given that you ask this? Your numbers don't use "12" (as in title) or "13" decimals. Number 287.950000000001 has 15 decimals shown: 2,8,7,9,5,0,0,0,0,0,0,0,0,0,1 And the last digit of that number (1*10^-12) comes from a negation involving two numbers, each of which have *four* decimals to the right of the dot; so for those numbers, the 1*10^-12 constitutes the 16th decimal. The negation makes that *normal* error visible because of the loss of significance. All that is explained in FAQ [1] and the linked Wikipedia article [2]. No, nothing can be done. [1] https://wiki.documentfoundation.org/Faq/Calc/Accuracy [2] https://en.wikipedia.org/wiki/Floating-point_arithmetic#Addition_and_subtraction
Maybe you didn't understand this issue? 287.950000000001 ____-----------^ <- 12th digit. You can easily see this by setting Calc to format a number with "12 decimal places" I don't think I can explain it any better. However, if you right click on a cell with that number, and select "Format Cells..." you can experiment yourself. Users expect decimal arithmetic to work. Whatever a developer using single or double precision floating point thinks - the user doing accounts will still expect it to work normally in decimal. Like it does on any calculator I have on my desk. It's very easy to see deducting a whole number from a larger number with only two decimal places... should only give a number with two decimal places. The rest should be zero. That's an easy fix isn't it Mike?
(In reply to Jonny Grant from comment #2) > Maybe you didn't understand this issue? I did. When you write: > I always recall "double" precision floating point has 15 decimal places. you yourself don't understand that these 15 decimal places are *not* "places to the right of the decimal point, no matter how many more digits I have to the left", but *all* the decimals in the number. Again: the error comes not even from 287.9500000000010, but from 8495.95. This number is stored in 64-bit double exactly as 8495.95000000000072759576141834259033203125 and you can count where the error starts: it's the "7" at 13th digit after the decimal dot, which is the 17th digit of the number. Now you perform a negation: 8495.95000000000072759576141834259033203125 -8208 = 287.95000000000072759576141834259033203125 That *is* the *exact* result, that is stored after the negation. It has the "7" in 13th digit after the decimal digit, and when rounded to 15 *significant* digits (i.e., showing total of 15 digits of the number, which is the maximum shown to user in Calc), the "7" is correctly rounded up to "10", giving you the "1" in 12th position after the decimal.
Maybe it's just an English thing, we're taught as follows in formal education https://thirdspacelearning.com/gcse-maths/number/decimal-places/#:~:text=re%20still%20stuck.-,What%20are%20decimal%20places%3F,the%20right%20of%20that%20number. I know other countries may have different understandings, some even use different separators (dot instead of comma etc) May I check if you read the "Format cells" dialog? "decimal places" refers to the number of decimal places after the dot. If I set 3 decimal places on that dialog it does show the format code: #.000 Then my number I enter as: 1.0012 Only shows 1.001 So it seems accurate? Anyway, without going over floating point. Why not truncate the output two 2 decimals after the dot? The rest can be displayed as 0 because it's obvious in decimal arithmetic they will never be anything else even if I set 15 decimal places after the dot.
(In reply to Jonny Grant from comment #4) How does all that relate to *your* raised issue that "1" appears at 12th position after the decimal dot after you subtract 8208 from 8495.95; and *your* confusion of the dialog's decimals (telling about decimals *after* the dot) vs. decimals stored in double-precision floating-point value? That dialog talks about different thing, so you need to comprehend that your words > when specifying 13 decimal places and then > I always recall "double" precision floating point has 15 decimal places. of > course "float" 32 bit only has 7 digits of precision. Is Calc not using double > precision? you are talking about *two different* decimal places - one from dialog (about places to the right of dot), another about total decimals in the number. And so you compare apples to oranges.
Mike, Are you like this with everyone? Do you often get into arguments in life? You don't sound like a qualified software engineer. This issue could have been fixed by now. I'll put a $100 bug bounty on it - I'm not personally familiar with the code base. I'm only talking about decimals after the dot, both on the display and on the dialog. Stop wandering off and talking about anything else. If you've a software engineer, you've still not commented on my proposal to always format and display zeros after an obvious subtraction of with a number containing only two decimal places after the dot.
Try Options->Calc->Calculate->Precision as shown. https://help.libreoffice.org/7.4/en-US/text/shared/optionen/01060500.html?DbPAR=CALC#hd_id3145150
(In reply to Mike Kaganski from comment #7) > Try Options->Calc->Calculate->Precision as shown. > https://help.libreoffice.org/7.4/en-US/text/shared/optionen/01060500. > html?DbPAR=CALC#hd_id3145150 Unfortunately that is the same. I propose fixing it in the code base, not an option for one user to tinker with on every computer spreadsheets are used on.