Bug 133840 - Calc Math error on INTEGER function
Summary: Calc Math error on INTEGER function
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium enhancement
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Calc-Function
  Show dependency treegraph
 
Reported: 2020-06-09 21:13 UTC by Werner
Modified: 2020-09-12 17:09 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
int demo (4.55 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2020-06-10 15:59 UTC, Oliver Brinzing
Details
test_mod_save_against_fp-rounding_errors (18.19 KB, application/vnd.oasis.opendocument.spreadsheet)
2020-09-12 17:09 UTC, b.
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Werner 2020-06-09 21:13:23 UTC
Description:
A3 = 11.09
=INT(MOD(A3,0.1)*100) 
result = 8
expected = 9

Steps to Reproduce:
A3 = 11.09
=INT(MOD(A3,0.1)*100) 



Actual Results:
result = 8

Expected Results:
expected = 9


Reproducible: Always


User Profile Reset: Yes



Additional Info:
no command glxinfo available
Comment 1 Oliver Brinzing 2020-06-10 15:59:36 UTC
Created attachment 161844 [details]
int demo

Please have a look at the attachment:

If you add more decimal places you will see:
=MOD(A3,0.1)*100 results in 8,99999999999993

https://erack.de/bookmarks/D.html#Computer_Arithmetic_and_IEEE_754

btw: result in ms excel 2016 is 8 too.
Comment 2 BogdanB 2020-06-10 17:37:10 UTC
INT(8,99) is ok to be 8. INT "rounds a number down to the nearest integer". The nearest integer DOWN is 8.
Comment 3 b. 2020-06-11 09:33:00 UTC
    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,
Comment 4 b. 2020-09-12 17:09:30 UTC
Created attachment 165422 [details]
test_mod_save_against_fp-rounding_errors

besides that i'd read somewhere that gcc has! a 'decimal' library, and thus implementing correct decimal math shouldn't be too big an issue ... 

(ok, 'performance', but plenty sheets are 'small', and most performance problems can be thrown on hardware today, the hours and days that users and supporters waste with irritations about fp-rounding-issues are in my opinion much worse than a few microseconds of computing time) 

... i was curious if it is possible to solve some of the FP problems with 'intelligent rounding', here is my first try, not a solution, just an attempt of a proof of concept, 

the user defined macro function 'mod_s' rounds the result acc. to the decimal places of the values, and returns a better result for '=INT(MOD_S(11.09,0.1)*100)' than the standard 'mod' of calc with '=INT(MOD(11.09,0.1)*100)' does, 

i'm not! sure if this works for all cases / values, i didn't even code or test it with fractional modulators in mind, it was tricky enough to get it to work because in calc not even 'mod' in formulas is compatible with 'mod' in basic macros :-( , it is 'unfinished work', i just want to 'throw it to the people' for examination and discussion ...