[Libreoffice-bugs] [Bug 141614] EDITING: Calc shows incorrect SUM (deviation of 0.000000000002 added to/sub'd from total) in bottom right status bar
https://bugs.documentfoundation.org/show_bug.cgi?id=141614 --- Comment #15 from Mike Kaganski --- (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. -- You are receiving this mail because: You are the assignee for the bug.___ Libreoffice-bugs mailing list Libreoffice-bugs@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
[Libreoffice-bugs] [Bug 141614] EDITING: Calc shows incorrect SUM (deviation of 0.000000000002 added to/sub'd from total) in bottom right status bar
https://bugs.documentfoundation.org/show_bug.cgi?id=141614 b. changed: What|Removed |Added Hardware|x86-64 (AMD64) |All OS|Linux (All) |All CC||newbie...@gmx.de Version|6.4.6.2 release |Inherited From OOo Resolution|--- |DUPLICATE Status|REOPENED|RESOLVED --- Comment #14 from b. --- 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 *** -- You are receiving this mail because: You are the assignee for the bug.___ Libreoffice-bugs mailing list Libreoffice-bugs@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
[Libreoffice-bugs] [Bug 141614] EDITING: Calc shows incorrect SUM (deviation of 0.000000000002 added to/sub'd from total) in bottom right status bar
https://bugs.documentfoundation.org/show_bug.cgi?id=141614 --- Comment #13 from TheWebMachine --- (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. -- You are receiving this mail because: You are the assignee for the bug.___ Libreoffice-bugs mailing list Libreoffice-bugs@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
[Libreoffice-bugs] [Bug 141614] EDITING: Calc shows incorrect SUM (deviation of 0.000000000002 added to/sub'd from total) in bottom right status bar
https://bugs.documentfoundation.org/show_bug.cgi?id=141614 --- Comment #12 from Mike Kaganski --- 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? -- You are receiving this mail because: You are the assignee for the bug.___ Libreoffice-bugs mailing list Libreoffice-bugs@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
[Libreoffice-bugs] [Bug 141614] EDITING: Calc shows incorrect SUM (deviation of 0.000000000002 added to/sub'd from total) in bottom right status bar
https://bugs.documentfoundation.org/show_bug.cgi?id=141614 --- Comment #11 from TheWebMachine --- 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.736277477273 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! -- You are receiving this mail because: You are the assignee for the bug.___ Libreoffice-bugs mailing list Libreoffice-bugs@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
[Libreoffice-bugs] [Bug 141614] EDITING: Calc shows incorrect SUM (deviation of 0.000000000002 added to/sub'd from total) in bottom right status bar
https://bugs.documentfoundation.org/show_bug.cgi?id=141614 --- Comment #10 from Mike Kaganski --- Created attachment 171164 --> https://bugs.documentfoundation.org/attachment.cgi?id=171164=edit 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.155511151231257827021181583404541015625 (closest binary representation), and they do the correct following math with *that* number. -- You are receiving this mail because: You are the assignee for the bug.___ Libreoffice-bugs mailing list Libreoffice-bugs@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
[Libreoffice-bugs] [Bug 141614] EDITING: Calc shows incorrect SUM (deviation of 0.000000000002 added to/sub'd from total) in bottom right status bar
https://bugs.documentfoundation.org/show_bug.cgi?id=141614 TheWebMachine changed: What|Removed |Added Resolution|DUPLICATE |--- Ever confirmed|0 |1 Status|RESOLVED|REOPENED --- Comment #9 from TheWebMachine --- 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?? -- You are receiving this mail because: You are the assignee for the bug.___ Libreoffice-bugs mailing list Libreoffice-bugs@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
[Libreoffice-bugs] [Bug 141614] EDITING: Calc shows incorrect SUM (deviation of 0.000000000002 added to/sub'd from total) in bottom right status bar
https://bugs.documentfoundation.org/show_bug.cgi?id=141614 --- Comment #7 from Mike Kaganski --- 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. -- You are receiving this mail because: You are the assignee for the bug.___ Libreoffice-bugs mailing list Libreoffice-bugs@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
[Libreoffice-bugs] [Bug 141614] EDITING: Calc shows incorrect SUM (deviation of 0.000000000002 added to/sub'd from total) in bottom right status bar
https://bugs.documentfoundation.org/show_bug.cgi?id=141614 --- Comment #8 from Mike Kaganski --- https://wiki.documentfoundation.org/Faq/Calc/Accuracy -- You are receiving this mail because: You are the assignee for the bug.___ Libreoffice-bugs mailing list Libreoffice-bugs@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
[Libreoffice-bugs] [Bug 141614] EDITING: Calc shows incorrect SUM (deviation of 0.000000000002 added to/sub'd from total) in bottom right status bar
https://bugs.documentfoundation.org/show_bug.cgi?id=141614 --- Comment #6 from Mike Kaganski --- (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'. -- You are receiving this mail because: You are the assignee for the bug.___ Libreoffice-bugs mailing list Libreoffice-bugs@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
[Libreoffice-bugs] [Bug 141614] EDITING: Calc shows incorrect SUM (deviation of 0.000000000002 added to/sub'd from total) in bottom right status bar
https://bugs.documentfoundation.org/show_bug.cgi?id=141614 TheWebMachine changed: What|Removed |Added Resolution|--- |DUPLICATE Status|UNCONFIRMED |RESOLVED --- Comment #5 from TheWebMachine --- 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 *** -- You are receiving this mail because: You are the assignee for the bug.___ Libreoffice-bugs mailing list Libreoffice-bugs@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
[Libreoffice-bugs] [Bug 141614] EDITING: Calc shows incorrect SUM (deviation of 0.000000000002 added to/sub'd from total) in bottom right status bar
https://bugs.documentfoundation.org/show_bug.cgi?id=141614 --- Comment #4 from Uwe Auer --- (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* -- You are receiving this mail because: You are the assignee for the bug.___ Libreoffice-bugs mailing list Libreoffice-bugs@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
[Libreoffice-bugs] [Bug 141614] EDITING: Calc shows incorrect SUM (deviation of 0.000000000002 added to/sub'd from total) in bottom right status bar
https://bugs.documentfoundation.org/show_bug.cgi?id=141614 --- Comment #3 from Michael Warner --- (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. -- You are receiving this mail because: You are the assignee for the bug.___ Libreoffice-bugs mailing list Libreoffice-bugs@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
[Libreoffice-bugs] [Bug 141614] EDITING: Calc shows incorrect SUM (deviation of 0.000000000002 added to/sub'd from total) in bottom right status bar
https://bugs.documentfoundation.org/show_bug.cgi?id=141614 --- Comment #2 from Uwe Auer --- >From my perspective not a bug but consequence of floating point arithmetic. -- You are receiving this mail because: You are the assignee for the bug.___ Libreoffice-bugs mailing list Libreoffice-bugs@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
[Libreoffice-bugs] [Bug 141614] EDITING: Calc shows incorrect SUM (deviation of 0.000000000002 added to/sub'd from total) in bottom right status bar
https://bugs.documentfoundation.org/show_bug.cgi?id=141614 --- Comment #1 from TheWebMachine --- Created attachment 171088 --> https://bugs.documentfoundation.org/attachment.cgi?id=171088=edit Screenshot example of incorrect SUM -- You are receiving this mail because: You are the assignee for the bug.___ Libreoffice-bugs mailing list Libreoffice-bugs@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
