Description: I found a rather annoying bug that I was surprised not to see in this tracker yet (unless my 40m of searching was incomplete). While f you select an entire column, you can see the SUM in the lower right. No matter what column of numbers I select, I get a deviation of +/-0.000000000001 or +/-0.000000000001 added to the real sum. So, if the real sum is 123.45, I will see 123.450000000002 reported by Calc...or I'll see 123.449999999999. Here's a good example data set: 1.2 1.44 1.2 1.2 2.4 1.44 1.2 1.2 1.2 2.4 1.2 1.68 0 1.44 3.36 1.2 1.44 0 1.2 1.68 0 1.2 1.44 2.4 1.2 20.4 1.44 1.2 1.68 1.44 1.2 1.44 1.2 1.2 1.2 1.68 1.2 1.2 1.2 1.2 1.2 1.2 1.44 1.44 1.2 1.44 1.2 1.2 1.2 2.4 1.2 1.44 1.2 0 1.2 1.2 1.2 1.2 1.2 1.2 1.2 0 1.2 1.2 1.2 1.2 1.2 1.2 1.44 1.2 0 1.2 1.44 0 1.2 1.44 1.2 1.2 1.44 4.8 3.36 1.2 1.2 1.2 1.44 1.2 1.2 1.44 1.2 1.2 1.44 1.2 1.2 4.32 1.2 1.2 1.2 1.2 3.36 1.1 3.6 1.2 0.85 0 0 0.35 2.4 1.2 0 1.44 1.2 1.2 1.2 1.2 2.4 1.2 1.2 1.2 1.2 1.2 1.2 1.2 1.2 1.2 2.4 1.2 2.88 1.2 0.7 1.44 1.2 1.44 1.2 1.2 1.2 0.78 1.2 1.2 3.36 1.44 0.6 1.2 1.2 1.2 1.2 1.2 1.2 1.2 1.2 1.2 1.2 1.2 1.2 1.2 1.68 2.4 1.2 1.2 1.2 SUM reported: 232.14 Now just add one more value: 1.2 SUM now reports: 233.339999999999 New set of numbers: 1.2 1.44 4.8 3.36 1.2 1.2 1.2 1.44 1.2 1.2 1.44 1.2 1.2 1.44 1.2 1.2 4.32 1.2 1.2 1.2 1.2 3.36 1.1 3.6 1.2 0.85 0 0 0.35 2.4 1.2 0 1.44 1.2 1.2 1.2 1.2 2.4 1.2 1.2 1.2 1.2 1.2 1.2 1.2 1.2 1.2 2.4 1.2 2.88 1.2 0.7 1.44 1.2 1.44 1.2 1.2 1.2 SUM reported as: 84.6 Now add just one more row: 0.78 SUM now reports: 85.380000000001 How is simple addition resulting in such a deviation from reality? Steps to Reproduce: 1. Select or add a column of decimal numbers 2. Observe SUM in lower right status bar 3. SUM sometimes differs by +/-0.000000000001 or +/-0.000000000002 Actual Results: Incorrect SUM in ~30% of cases Expected Results: Correct SUM Reproducible: Always User Profile Reset: No OpenGL enabled: Yes Additional Info: Version: 6.4.6.2 Build ID: 1:6.4.6-0ubuntu0.20.04.1 CPU threads: 24; OS: Linux 5.8; UI render: default; VCL: gtk3; Locale: en-US (en_US.UTF-8); UI-Language: en-US Calc: CL OpenGL vendor string: NVIDIA Corporation OpenGL renderer string: GeForce RTX 2080 SUPER/PCIe/SSE2 OpenGL core profile version string: 4.6.0 NVIDIA 460.67 OpenGL core profile shading language version string: 4.60 NVIDIA OpenGL core profile context flags: (none) OpenGL core profile profile mask: core profile OpenGL core profile extensions: OpenGL version string: 4.6.0 NVIDIA 460.67 OpenGL shading language version string: 4.60 NVIDIA OpenGL context flags: (none) OpenGL profile mask: (none) OpenGL extensions: OpenGL ES profile version string: OpenGL ES 3.2 NVIDIA 460.67 OpenGL ES profile shading language version string: OpenGL ES GLSL ES 3.20 OpenGL ES profile extensions:
Created attachment 171088 [details] Screenshot example of incorrect SUM
From my perspective not a bug but consequence of floating point arithmetic.
(In reply to Uwe Auer from comment #2) > From my perspective not a bug but consequence of floating point arithmetic. Which could be mitigated by Calc rounding the result so that it has the same number of significant figures as the inputs.
(In reply to Michael Warner from comment #3) > (In reply to Uwe Auer from comment #2) > > From my perspective not a bug but consequence of floating point arithmetic. > > Which could be mitigated by Calc rounding the result so that it has the same > number of significant figures as the inputs. ... then this might be a duplicate of tfd#137679 *Implement a Kahan summation algorithm for reduce the numerical error in the total*
Ah, there's what I didn't find in my search! Marking accordingly. Thanks! Glad it's being looked into already. *** This bug has been marked as a duplicate of bug 137679 ***
(In reply to Michael Warner from comment #3) > (In reply to Uwe Auer from comment #2) > > From my perspective not a bug but consequence of floating point arithmetic. > > Which could be mitigated by Calc rounding the result so that it has the same > number of significant figures as the inputs. No, unless you keep the input string (or some alternative form of the information) along with the data. And then, it will only be useful for the summation, and will break as soon as you do anything more complex. Which is ~100% (it's very rare to have spreadsheets not doing multiplication/division). Kahan is a good approach to improve *bulk* summation, but generally the error is unavoidable. E.g., it will help with 'SUM(A1:A20)', but not with 'A1+A2+A3+A4+...+A20'.
Ah, now I read the numbers here, and see that this is not a dupe of tdf#137679. That one helps when there are numbers of different magnitude, which is not the case here. This one will not change, and is because some (most) numbers in the set can't be represented in binary form. This is NOTABUG.
https://wiki.documentfoundation.org/Faq/Calc/Accuracy
No, no, no...I consider the inability to add very basic floating numbers to be a bug. If you can't add 1.5 to 1.5 and get 3, you're doing it WRONG. Period. Ex$el and other apps have been adding FPNs for aeons and seem to get it working right just fine. So, what's the special problem here again??
Created attachment 171164 [details] A screenshot from Excel with sum shown with 12 decimals (In reply to TheWebMachine from comment #9) > Ex$el and other apps have been adding FPNs for aeons and seem to get > it working right just fine. No they don't. They have exactly the same problems. All of them (Excel, Google Sheets, whatever). It is perfectly normal, that doing *discrete* math, you get the result normal for *discrete* math. And spreadsheets are just that - tools doing discrete math with binary 64-bit numbers fast. And they just don't have things like "0.1" - so they are always doing correct math with what *they* convert from your input. Whenever you enter '0.1', Calc, Excel, ... get not 0.1, but 0.1000000000000000055511151231257827021181583404541015625 (closest binary representation), and they do the correct following math with *that* number.
I've been a dev for 25+ years and still code for embedded controllers with FAR LESS CAPABILITY to this day. I know how floating point in binary works (have you ever built floating point math in pure assembly on an 8- or 16-bit microcontroller? because I have). Ex$cel only shows that level of "precision" if you tell it to. It doesn't complicate matters by making that perspective the default. How hard is it to say "we know we're not adding 0.0000000000000000736277477273 anywhere along the way so let's not show that many digits in the result because we know it's definitely wrong?" Y'all speak of performance penalties, but really what penalty are we talking about here? I'm not running a 386 with 1MB of RAM...and neither are you, I'd hope!
I don't care if we change the default visual representation to, say, 4 decimals. Of course it's funny when a dev with 25 years of FP assembly does not recognize this specific thing when they see it, but whatever. The calculations will not change, but - as said - personally I don't oppose playing with default representation. Maybe Eike has something to say?
(In reply to Mike Kaganski from comment #12) > I don't care if we change the default visual representation to, say, 4 > decimals. Of course it's funny when a dev with 25 years of FP assembly does > not recognize this specific thing when they see it, but whatever. > The calculations will not change, but - as said - personally I don't oppose > playing with default representation. Maybe Eike has something to say? Again, I know *why* the MATH error occurs, I just didn't understand why it hasn't been addressed in the commonly expected manner from the end user perspective (perhaps I failed to convey that precise point from the start, for which I apologize). This is the type of thinking that keeps LibreOffice down. The LAYPERSON doesn't want to deal with this type of effectively cosmetic nonsense, cares not to understand the why, and doesn't have to if they use another software...so that's precisely what they will do. Given that we've known the pitfalls of FPM in binary basically forever, why intentionally drive away potential new users with this type of known issue? You don't see it as a bug and I don't see it as one in the traditional sense either, but anyone trying to transition from M$ or others is going to immediately think something is broken and switch back because they have work to do. Sorry if I've opened an old can of worms or ruffled any feathers here. A lot of the focus of what I do involves making "fool-proof"/"unbreakable" interfaces for users who don't know their own backside from a hole in the ground...I've learned all too well how easily minor cosmetic changes can make or break a UX from the only perspective that matters at the end of the day (the end user/customer). I really am only trying to help. I've been using LO for years and must have been mentally glossing over the current presentation in Calc all this time. It only recently occurred to me that it was "unusual" from a UX perspective to allow that to be shown to the end user in a "user-friendly" software and thought it was perhaps a regression or new "bug" since I hadn't noticed it previously.
IMHO 68448 and 109189 fit better as ancestors, @TheWebMachine: your values are mostly 1.2 or multiples, the 'double' representation of 1.2 is short by about 4.4408920985006261578426667835888095E-17, thus expect some deviation, expect additional deviation as the smaller of two summands mostly looses some bits in the addition, the effect on the running total is changing with the accumulated value in the total - size ratio of the two summands, that decides about the 'bin-range-change' for the smaller summand, if the change results in a loss of a bit-string starting with zero it's simply truncated and the total runs short, if the chopped part starts with '1' the part taken into account is rounded up and the total grows more than it should, 'in sheet' the deviations are often rounded away or covered by 'snap to zero' or invisible in short cell format or similar, for 'previous sum plus 1.2' i get visible fails starting with row 46 (display 20 decimals), rawsubtract can detect deviations from row 9 on, in ex$el (2010) the deviations start with row 49, in the statusbar the totals are less rounded? (acc. @erAck) and calculated in another order which reg. 'non-associativity' may produce different results ... 'fun with floats and doubles' ... help: round your results, request a 'fail free working round', learn hacking and reprogram the statusbar calculation ... @Mike Kaganski: 'Kahan Summation' may! help as a: it's not limited to different magnitude, and b: the difference running total / summands will likely grow while all summands are positive ... @TheWebMachine: 'If you can't add 1.5 to 1.5 and get 3' - that's easy, all values have exact representation in doubles, 'Ex$el and other apps have been adding FPNs for aeons and seem to get it working right just fine' - seem to but don't, believe me, see comment above about ex$el. the handling is sometimes somewhat different, but the math and it's problems is the same, and difficult to deal with. i wouldn't say impossible, but difficult, and ex$el suffers from such too. it is! a bug, it is! a duplicate of already filed bugs, it is! a difficult task, and it likely won't! be solved in near future as it's a: low priority, and b: already old (8 years), if it would have been easy most likely someone would have done it in the meantime ... *** This bug has been marked as a duplicate of bug 68448 ***
(In reply to b. from comment #14) > @Mike Kaganski: 'Kahan Summation' may! help as a: it's not limited to > different magnitude, and b: the difference running total / summands will > likely grow while all summands are positive ... Right, I was mistaken.