[Libreoffice-bugs] [Bug 129606] Drag-fill series produces values like 6.00000000000001

[bugzilla-daemon at bugs.documentfoundation.org]

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

--- Comment #12 from b. <newbie-02 at gmx.de> ---
Created attachment 157434
  --> https://bugs.documentfoundation.org/attachment.cgi?id=157434&action=edit
129606_testsheet1_ori.ods, a sample with leftover floating point irritations

hello @Mike, 

i couldn't find a version 6.5x where to be sure your patch is included,
downloaded 'master' - now named 7.0x - shortly, and it looks as if your patch
is in and solves the dragfill problem. 

but the problem of unprecise and 'user irritating' decimal - floating.point -
binary - floating-point - decimal conversion behaviour seems more general, see
red marked cells in attached sheet. 

(e.g. '=(1234+0,12)-1234' which one would expect so result in 0,12 instead
produces 0,11999999999989100000 with as well showing the rounding problem as
the irritating zeros behind it pretending the result to be more accurate than
it really is.)

i couldn't find the old discussion about that problem in short time, instead
some other which talks about the problem and! a possible solution?. 

you can find it there: 

https://www.libreoffice-forum.de/viewtopic.php?f=6&t=16543&hilit=floating+point&start=10#p43772

the point: 

'A 'simple' solution to the dilemma would be to use the newer floating point
format IEEE 754-2008, because here an exact conversion of decimal values would
be possible even with decimal places.'

sounds somehow promising for me, but i can't say how far it would reach. 

neither can i say how much work it would be to implement it and how deep the
compatibility cut would be. 

(at all, as the 'new' standard is quite old already, i can't say if it or parts
of it have already been implemented in calc.)

wikipedia talks about an even newer version there:
https://en.wikipedia.org/wiki/IEEE_754#2019

maybe you'll like to dig into it, or you can give it to persons or groups which
are interested or 'in charge'. 

as the discussion is in german i tried a translation with deepl: 

************
Re: Calculation error / imprecision LO Calc

Contribution by Mr. Hobbybyte " Thu Aug 4, 2016, 10:45
after several attempts to narrow the whole thing down a bit,
I have come to the following conclusion :

- Calc calculates internally with 32 bit floating point values
- Intermediate values are stored in the (old) floating point format according
to IEEE754

and thus the problem is causal, as mikele above already correctly suspected,
was caused by an inaccurate conversion to the (old) floating point format.
This then leads to the inaccuracies described above when performing additions.
However, I would have suspected that calculations are either caused by an own
arithmetic or at least with the help of more precise intermediate values
take place. However, this is not (yet) the case.

@mikele
Your test shows exactly the problem with the intermediate values.
A 'simple' solution to the dilemma would be to use the newer floating point
format IEEE 754-2008, because here an exact conversion of decimal values would
be possible even with decimal places.
Since I haven't found the time to look at the source more closely, I don't know
if this is also would be easy to implement (bfp754.h), since all basic
functions are affected.

@Hiker
The "Accuracy as displayed" option may obscure the error under certain
circumstances, but in the calculation with fractions even produce new errors.
During the tests this option was shut down everywhere.
I have tested with different Excel versions from 97 - 2013 and found different
(in)accuracies has been determined. I even found some effects of the operating
system used in Excel (32 or 64 bit)
A 64 Bit Excel was not available for testing
According to my current knowledge, the problem of Excel is also only solved by
clever rounding with the help of the last Place(s) bypassed, but not really
solved.

Thanks and best regards
************

reg. 

b. 

P.S. @Xisco if - for whatever reason - you'd like a new 'bug' for this
enhancement proposal instead warming up 'old stuff' ... feel free to move it
accordingly. as well you may decide if this bug should be reopened.

-- 
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/20200126/8983853b/attachment.htm>