[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

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

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |newbie-02 at gmx.de

--- Comment #4 from b. <newbie-02 at gmx.de> ---
Created attachment 156819
  --> https://bugs.documentfoundation.org/attachment.cgi?id=156819&action=edit
file with some 'step-0,1-series' produced with different methods

@Mike: you are aware of the source, 

just for others who check in for new: 

it's normal that you get rounding issues sometimes around the 15'th or 16'th
significant decimal digit as calc does store numbers in floating point values,
and that limits accuracy somehow. (reg. narrowing given or calculated decimal
values by binary representation with limited length and thus limited precision
some granularity steps in). 

short: 'According to en.wikipedia.org/wiki/IEEE_754 binary double 64bit have a
precision of 15.95 digits if leading digit is not null, you have hit
representation limit.' (somewhere in 'ask'?) 

irritating is that you can! display - display, not store! - such a value with
20 decimal digits in calc, and then see something like 6.00000000000001000000.
in terms of our well beloved 'leading zero' (Trump) the last six zeros in this
sample are 'fake zeros'. mnemonic bridge for that: the last sex is with faked
holes. 

the digits are used and filled with accuracy on smaller values ... it's a
tradeout to use those fakes to get 'nice looking columns' versus pretending
incorrect accuracy ... i'd suggest to display them in different colour or with
gray background to inform the user about their property as 'valueless
placeholders'. 

(may be that the content xml file stores even the faked zeros - it does!, calc
can't use them for calculations.)

the issue in your case, and the difference displayed on screen, might result
from different ways used for the calculation. just an assumption: 'drag down'
(i'll call it procedure 'A') has to do 44 steps of 'f(x(n))=f(x(n-1))+0,1'
starting with f(x(1))=1, with 44 times rounding the running total and the
rounding errors summed up?, 

while 'fill series' (i'll call it procedure 'B') is somehow 'smart' and
calculates 'f(x(45))=(45-1)*(0,1)+1' and rounds only once? 

you can play around with similar functions like 1 in row 1 and
'=1+(ROW()-1)*0,1' in the rows below (procedure C) - results similar to
procedure 'B', or 1 in D1 and '=D1+0,1' in D2, D2 copied to D3:D1000 (procedure
D), produces rounding errors as procedure 'A' but more likely rounded down
instead of roundig up (on my system). 

calc itself isn't aware of theese rounded displays, play with comparision
between e.g. A45 and B45 in attached sheet ... display is different but calc
will handle them as identical, 

as rounding errors are a 'must have', and the mathematical way to achieve a
goal is 'free', and the precision achieved is very near to the real world value
... notabug? 

but! ... if you look at your procedure 'A' and start with '0' in A1 (procedure
'E') you get 'clean results' ... thus ... 'assuming' (sorry Eike) someone fixed
that for values below 1 (imho there was something that calc has one more
significant digit for values below 1), but on fixing he'd work with 'small
values' only and fixed rounding in 17'th digit to 'human acceptable', without
considering the reduced accuracy for values above 1? 

procedures 'behind the scene' may be totally different ... 

you can achieve the accuracy of procedure 'B' for your task by 'fill series' or
using procedure 'E' and add 1 to every step ... 

i had a similar issue in a financial sheet sometimes ago and proposed to 'round
away' those errors ... it was not accepted reg. 'calc should never do unwanted
roundings' or similar ... neglecting that calc already does! roundings - not
towards decimals but towards binary values it can deal with. 

enhancement request? 

YES! 

it would be very nice and appreciated (by me!, by others?) and! save much time
and headaches for plenty people to get calc working in the same manner as
procedure 'B' or 'E' for tasks as procedure 'A' and 'D', not the same accuracy,
that's not possible, but the same way to handle the binary results for user to
work with ... 

is that a solution? roundsig()? 

https://ask.libreoffice.org/en/question/109372/incorrect-results-produced-by-simple-subtraction/ 

yes and no, it does push away the errors, but at the cost of lost comfort /
functionality as e.g. a dragdown of such formulas won't work. 

to long, sorry, i'll make a break here ... :-)

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/20191228/54600350/attachment-0001.htm>