[Libreoffice-bugs] [Bug 55960] Filling cells with incremental numbers – wrong numbers

[bugzilla-daemon at bugs.documentfoundation.org]

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

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

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |newbie-02 at gmx.de
             Status|RESOLVED                    |REOPENED
           Severity|major                       |enhancement
         Resolution|WORKSFORME                  |---

--- Comment #9 from b. <newbie-02 at gmx.de> ---
reopening, 

this bug is still - or again - present in ver. 

Version: 6.4.0.3 (x64)
Build ID: b0a288ab3d2d4774cb44b62f04d5d28733ac6df8
CPU threads: 8; OS: Windows 6.1 Service Pack 1 Build 7601; UI render: default;
VCL: win; 
Locale: de-DE (de_DE); UI-Language: en-US
Calc: 

sequence 1:  -2, -1,95 ... produces the values as in sample
https://bugs.documentfoundation.org/attachment.cgi?id=68541 when re-producing
it in a new column, 

while the second sequence: -1, -,9 ... which showed errors around 0 an 0,1 for
me, and the third sample from
https://bugs.documentfoundation.org/attachment.cgi?id=122375 look 'healed',
when re-producing they produce 'exact decimals' (at least for some 300+ rows i
tested and if i didn't overlook something), 

@Mike has done some work on this issue in
https://bugs.documentfoundation.org/show_bug.cgi?id=129606, 

the still reproducible error may have re-occured for any reason, or might have
been overlooked by @Xavier and @raal in c#7 and c#8 because it doesn't show up
if the column is too narrow, then the numbers are shortened / folded / rounded
to two decimal places and the error looks gone, it's easier spotted in the
input-line / formula-bar, or when you broaden the column, and format the cells
to show 20 decimal places. 

the bug - better 'the problem' - is widespread - see #90419, #128312, #129606,
#130221, #130356 and plenty others - it isn't really a bug as @Mike explained
in some of the mentioned threads, it's just 'unavoidable' as the representation
of decimal fractions in binary 'floating point format' isn't exact for most
values but includes - very small - rounding errors. 

once you know this you can avoid such problems by using clever rounding or
formatting, or you can learn to live with it as you know the source of the
problem. 

but it will take plenty people much time for research when they hit this
problem for the first time and had expected a spreadsheet to do exact
calculations. they won't start a research for 'limitations when representing
decimal values in binary IEE 754 floating point format', but will put questions
like 'my spreadsheet produces wrong results' in 'ask', 'bugs', or in other
forums. 

thus i suggest to keep the 'bug' as an enhancement request and check whether
it's possible to avoid it in general (or for more use cases) by applying some
rounding (make it optional with a switch 'use decimal friendly rounding' to
avoid conflicts with scientists who like to keep their 'exact' results, and /
or implement IEEE 754-2008 'decimal' format for correct decimal calculations in
general. 

-- 'exact' scientific calculations made with calc (or other spreadsheets?) are
not really exact in most cases, they have very good - but limited - precision,
as the representation in 'binary floats' affects most input values, and even if
the input is exact the result of calculations will be affected by rounding
issues in plenty cases?? it's only small errors, but if you try 'chaos
calculations' or wheather forecasting where 'the flap of a butterfly's wings at
the Amazon can trigger a typhoon in the Philippines' ... you need to know about
the limitations of the tools you use ... the error is just easily spotted by
humans in cases as the attached cases, but it's affecting other values too, and
there it's 'invisible' -- 

reg. 

b.

-- 
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/20200210/13d5f116/attachment.htm>