[Libreoffice-bugs] [Bug 133840] Calc Math error on INTEGER function

[bugzilla-daemon at bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=133840

b. <newbie-02 at gmx.de> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
           Severity|normal                      |enhancement
         Resolution|NOTABUG                     |---
                 CC|                            |newbie-02 at gmx.de
     Ever confirmed|0                           |1
             Status|RESOLVED                    |NEW
            Version|6.1.5.2 release             |Inherited From OOo

--- Comment #3 from b. <newbie-02 at gmx.de> ---

    cruel, over and over again ... these fp-rounding-errors ... 

    to many of them it was said <menu - options - LibreOffice Calc - calculate
- precision as shown> would help ... not here ... 

    > INT(8,99) is ok to be 8

    is ok, but 

    > =MOD(A3,0.1)*100 results in 8,99999999999993

    is not, @Oliver: btw. how did you get the '3' in the end, i got 14 '9's, 

    > btw: result in ms excel 2016 is 8 too.

    that's not a good excuse for everything ... 
    (ok, correctness vs. compatibility exceeds the space here) 

    =INT(MOD(A3*100;0,1*100)) will hold but it's a crutch, 

    maybe we should implement iEEE754-2008, if necessary in software and as an
additional datatype, for smaller projects the performance should be sufficient, 

    and by then there should be a wiki: 

    maybe there already is, then please link here, the normal user does not
want to work through 37+ scientific papers from @erAck's impressive collection
before calculating 0.1 plus 0.2, 

    with such a wiki you could link all corresponding requests (bugs) to it and
the topic would be finished, 

    if there isn't one already, here's a start: 

    "how do I (best) live with the errors injected by floating point values", 

    "spreadsheet calculations and errors for dummies ..."

    - most computers do not manage a continuous number space, but single
numbers between a smallest number and a largest number, 

    - there are gaps between these individual figures, 

    - there is no other way because computers are finite machines with finite
capabilities, but there are infinite numbers, 

    - for most computers today, several standards have been agreed upon, e.g.
'integers' and 'floating point' values, 

    - in most of today's calculation programs floating points (FP's) are mostly
used for numbers, 

    - these are 'good' because fast, the processors have special commands and
functions for them, 

    - but they also have disadvantages, they are 'granular', and are based on
the dual number system (only zeros and ones) - 'binary', thus often small
errors occur when converting decimal numbers as used by most humans to binary
representation used by most computers and vice-versa, (other representations
with different weaknesses and advantages exist, e.g. BCD, but didn't make it to
the 'standard pc'), 

    - the resulting errors are usually very small and negligible, but sometimes
they come to the surface and become 'relevant' through failed comparisons or
multiplications, 

    tips to deal with it: 

    0. if you're interested in the topic in general, search the Internet, 

    1. don't scold the developers, many clever minds have given a lot of
thought, the result is unsatisfactory but also has advantages, no single
developer is 'to blame', 

    2. be aware of the problem, then you can handle it better, 

    3. use 'integer arithmetic', financial tables for example make mistakes
with decimal numbers (34,56 €), but work 'clean' if you calculate with cents
(3.456 cent) and only divide the final result by 100. Meanwhile some banks
calculate like this, 

    4. explore the settings of your spreadsheet for automatic rounding, in calc
e.g. <menu - options - libreoffice calc - calculation - precision as shown>,
this does not always hold, but often, 

    5. round in your formulas, a bit finer than you need for the result, but
you eliminate the errors in 14th, 15th and 16 decimal places (or 'significant
digits') which otherwise like to come up as unexpected mysteries, 

    5a. =INT(ROUND(MOD(11,09;0,1);3)*100) e.g. 'works' - results in 9 - while
=INT(MOD(11,09;0,1)*100) fails - results in 8 - because 8.99999999999999 is
truncated to 8, 

    6. round after every step which can make mistakes, actually - with FP's -
after nearly every step in every formula ... that's annoying! 

    7. suggest to implement IEEE 754-2008 here and there, it's an extension of
the standard that calculates slower, but has better handling for decimal
numbers, 

    8. the whole saving by faster calculating tables is of no use to the users
if they have to fight with wrong results for two months afterwards, not all
developers are aware of that yet, 

    9. the whole saving by faster calculating tables is of no use to the
developers and supporters if they have to deal with questions from annoyed
users for years! afterwards, not all developers and supporters are really aware
of that yet,

-- 
You are receiving this mail because:
You are the assignee for the bug.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.freedesktop.org/archives/libreoffice-bugs/attachments/20200611/28aa95da/attachment-0001.htm>