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

[bugzilla-daemon]

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

--- Comment #30 from b.  ---
@Matt K: 'to see why this is happening' - roundup/roundoff error of factors /
divisors used for rounding strike?, while all (sorry, most) '1E+x' values are
exact as being integers most '1E-x' values carry deviations, but to different
extend, 

while: 
1e-14 is relatively friendly being truncated by '0001~' resulting in a
roundoff deviation of less than -2/256 ULP (~ -1,875/256 ULP), 

1e-10 is already difficult making a 0,5 ULP roundup from '10110111~' resulting
in a deviation of approx 1/2 ULP minus 55/256 ULP ~ 73/256 ULP, and 

1e-15 is critical with an approx roundup of 1/2 ULP minus 15/256 ULP -> ~
113/256 ULP, by rounding up regarding truncation of '10011011~', and 

1e-5 is somewhat catastrophic with an approx roundup of 1/2 ULP minus 4/256 ULP
-> ~ 124/256 ULP, by rounding up regarding truncation of '1100~', 

it's a matter for 'numerologists'? to find a way around those cliffs, 

@all: a simple '=ROUND(A1+0,05;2)' in A2:Ax would make it for this case, is
there really no ambition to relieve the user of such simple things (where the
users always have problems because, unlike the developers, they only know
afterwards! where the pitfalls lay)

@Mike Kaganski: '> return rtl::math::round(0.050044, 15);' - is
that the call to calculate the increment? what about: 
- rounding the step values after adding the increment? 
- rounding them 'harder', not to the max meaningful 15 dec digits (as rounding
to '15' is critical, see above), but acc. the amount of decimals digits given
by the operands? (two in this case), 
!!! i know !!! that such is more effort, but it's not at 'computation time' but
during 'user interaction' and there usually the user is the slow part, 
okok, it may! take some time if you fill a whole column ... but ... imho ... it
will be much less than the time we and users throw away with irritations and
failed calculations and discussions here, even if you compare to the worldwide
accumulated average delay, 

'Or even possibly a result of smart float-to-string algorithm finding shortest
string that round-trips to the same float.' - such is available?! it should be
used more often ... 

'I doubt that this is "info about the significant decimals keyed in by the
user".' - for my silly understanding and acc. occams razor it's somewhat
legitim finding the same value you A: keyed in into a cell B: in the saved file
defined as value for that cell, that B results of A, if in doubt do some tests,
the more often the value changes with what you key in the more likely a
relationship becomes, 

'In the area of floating-point calculations and related rounding errors,
there's no goal in either math correctness in the strong sense (only in the
IEEE 754 sense), nor in "Excel compatibility"' - this is sad and difficult, sad
because people should have goals, otherwise they often get lost in space and
time or work ineffectively, and difficult because it becomes hard to judge what
is good or bad in a project when there are no defined goals, 

'(it could only be possible if we know exact sequence of the calculations they
perform, like if they do it from left-to-right then top-to-bottom, or
top-to-bottom then left-to-right, or any other way for a given argument number
of SUM)' - ex$el calculates: 
- '=Lx+My+Nz': in the order Lx, My, Nz, as written in the formula, no matter
where the cells are positioned in the sheet, imho calc does the same, 
- '=sum(Ax:Cy)': top row from left to right then next row, imho different to
calc which acts rightmost column bottom up, then nextleft column, i 'assume'
oriental influence, and i'm astonished that nobody mentioned that earlier, as
it indeed introduces compatibility issues, 
ex$el does not: do any smart sorting of operands reg. magnitude or similar as
sometimes somewhere assumed, neither does calc (afaik), 
(above tests with ex$el 2010 ver 14.0.6023.1000 64-bit) 'home and business', 

'No. What is fine at the time of saving, is not OK at the time of performing
computations.' - we don't talk about critical mass computations here, we talk
about 'UI' and 'filling some cells', once calculated the values may remain ad
infinitum, it's much less 'performance impact' compared to users need to define
rounded increments and rounded steps by themself and implement them and then
have them recalculated with every recalc ... 

reg. 



b.

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

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

[bugzilla-daemon]

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

Buovjaga  changed:

   What|Removed |Added

 Status|NEEDINFO|NEW
 CC|ilmari.lauhakangas@libreoff |
   |ice.org |

--- Comment #29 from Buovjaga  ---
Needinfo is only valid, if a bug report needs further info to be confirmed. It
leads to automatic closing in 7 months, so we don't use it in other cases.

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

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

[bugzilla-daemon]

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

Matt K  changed:

   What|Removed |Added

 Status|NEW |NEEDINFO

--- Comment #28 from Matt K  ---
Confirmed repro using recent master.  Only able to see behavior in first
attachment, not the second.  I had to make the cells wide enough and I forced
display to 16 decimals in Calc Options.

I'm also having trouble setting a breakpoint to see why this is happening.  I
tried to set a break at approxDiff, but can't get symbols loaded.  When I build
core/sc, it builds but the modules aren't loaded by calc -- wondering if the
code has changed locations.  Marking NEEDINFO to see if someone can give better
code pointers to investigate.

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

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

[bugzilla-daemon]

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

b.  changed:

   What|Removed |Added

 Status|NEEDINFO|NEW
   Severity|enhancement |normal

--- Comment #27 from b.  ---
still repro with procedure from OP: 

-0,0999
-0,0498
0,
0,0503

@Matt K: did you make the cells wide enough? did you force dsiplay with 16+
decimals? otherwise you'd see rounded values ... 

ver. 2021-02-18_10:36

Version: 7.2.0.0.alpha0+ (x64) / LibreOffice Community
Build ID: 62dff2844b0bf1d1bcb8eb4d6db529ef4a31bee4
CPU threads: 8; OS: Windows 6.1 Service Pack 1 Build 7601; UI render: default;
VCL: win
Locale: de-DE (de_DE); UI: en-US
Calc: 

P.S. QA-admins reg. green text: why change to 'unconfirmed'? it's clear bug and
had been confirmed at least twice ...

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

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

[bugzilla-daemon]

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

Matt K  changed:

   What|Removed |Added

 Status|NEW |NEEDINFO

--- Comment #26 from Matt K  ---
No repro on latest master using the repro steps from comment #1 or the
sequences from the 2 attached files.  Are there better repro steps, or does
this not repro anymore?

Win 10 x64, build id is
https://git.libreoffice.org/core/+log/bc817c2fb26ebbfcc7b6868fbcabb13772ddb90e

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

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

[bugzilla-daemon]

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

--- Comment #25 from Mike Kaganski  ---
(In reply to b. from comment #24)
> whatever it is - you'll know better than me - it's a valid starting point
> for the proposed algorithm, 

No. What is fine at the time of saving, is not OK at the time of performing
computations.

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

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

[bugzilla-daemon]

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

--- Comment #24 from b.  ---
(In reply to Mike Kaganski from comment #22)

thanks for your fast reply, 

> I doubt that this is "info about the significant decimals keyed in by the
> user". Rather, it's the number rounded to some number of decimals on save,
> which happens to coincide. Or even possibly a result of smart
> float-to-string algorithm finding shortest string that round-trips to the
> same float.

whatever it is - you'll know better than me - it's a valid starting point for
the proposed algorithm, 

> if you
> can suggest an optimization that gives better precision by re-arranging
> calculations, without performance loss - it's fine to discuss in a dedicated
> issue like "Function FOO can give better precision", with analysis of
> correctness of suggested optimization, and comparison of performance and
> precision of old and new ways.

this is too high a demand on me, since i am neither c-coder nor performance
specialist nor LO developer, 

(besides i tried to give 'my best two cents' in the previous post,) 

an important basic question is whether you are allowed to comment on problems
if you cannot do it better yourself, Adolf Hitler said 'NO!', i say 'yes', 

["Only he who can solve a task better is entitled to criticism". - Adolf Hitler
Speech on May 1, 1934 in Berlin, in: Max Domarus (Ed.): Reden und
Proklamationen, 1932-1945, Vol. 1, p. 379

Source:
https://beruhmte-zitate.de/zitate/128459-adolf-hitler-nur-der-ist-zur-kritik-berechtigt-der-eine-aufgab/]

so, if you can accept that even people who are 'idiots' in certain areas are
allowed to bring up strange results from these areas and may make - even
idiosyncratic - suggestions for solutions, among other things because mankind
has mainly developed in this way ... and because you can sometimes free
'specialist idiots' from their narrow-mindedness ... then we can continue
talking, 

in the sense of goals and correctness - imho LO / calc should! have goals how
to solve calculations, and theese two: 'math-correctness' and
'ex$el-compatibility' i encountered in discussions here again and again,  

'performance' is a dilemma, for 'small tasks' like filling 100 lines with
discrete values the 5 nanoseconds? extra computing time needed for step-by-step
rounding is certainly the smaller the problem, in contrast to the hours and
days we spend explaining the 'necessary errors' to users, in large sheets with
thousands of values and calculations the computing speed becomes of course more
important, but there the loss due to the immensely complex troubleshooting also
increases and the consequences of undiscovered errors can be terribly harmful, 

[in which city do you live? LO is in use at italian military? consider they
decide to bomb greece out of europe to become 'the only one largest problem in
europe', they use calc for the target calculation and nuke out your city ...]

it is not my choice or decision how LO and calc and 'the developers' deal with
these problems, but my urgent recommendation to try to produce satisfied users
with correct results and to try to get rid of fp-rounding-errors and to get rid
of the miserable compatibility to ex$els errors ... while keeping doors open
for users to migrate ... towards LO ... and for that 'more accurate results
than ex$el' would be a great invitation ...

P.S. 

tdf#129606: correct, well done, keep going ... 

P.S. II 

i'd suggest to think about general solutions rather than individual bugs ...

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

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

[bugzilla-daemon]

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

--- Comment #23 from Mike Kaganski  ---
(In reply to b. from comment #21)
> to calm down: ex$el is only slightly better in these things, in this test it
> has only one error in the first 62 lines instead of three (calc), and the
> first error occurs in line 42 instead of 39 (calc), tested with ver. 2010,
> winx64, 

It's not "slightly better". It's different. E.g., for case in tdf#129606, Excel
produces 32 "wrong" values in first 90 cells, first one being in cell 56; while
Calc does not produce a single one (after the fix in the mentioned bug).

And that doesn't mean "Calc is better". As said, they are simply different in
how they handle the inherent imprecision in different scenarios.

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

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

[bugzilla-daemon]

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

--- Comment #22 from Mike Kaganski  ---
(In reply to b. from comment #21)
> thanks for your comment, sorry for objecting, please correct me if i'm
> wrong, 

Please don't. There's no need to be sorry for any objecting.

> from a file saved with ver. 7.1, three cells with values: 
> 
> B3: -2
> B4: -1,95
> B5: 0,05
> 
> content.xml: 
> 
> ... 
> ... 
> 
> trailing zeroes are truncated on save, but the info about the significant
> decimals keyed in by the user is visible there, besides the values being
> denominated as 'float' i 'see' pure decimal 'fixed point' fractions there, 

I doubt that this is "info about the significant decimals keyed in by the
user". Rather, it's the number rounded to some number of decimals on save,
which happens to coincide. Or even possibly a result of smart float-to-string
algorithm finding shortest string that round-trips to the same float.

> as well as 'value' as as 'text', 

Of course. When it's output, the output is text of a cell.

> starting with theese values it is technically possible to calculate -0,05 as
> correct result after 39 additions of 0,05 to -2, instead of
> -0,0498, as calc just did, 

This would require to perform these operations related to obtaining shortest
string representation at calculation time, with related unacceptable penalty.

> but ... calc misses both targets and can claim neither correctness nor ex$el
> compatibility ...

In the area of floating-point calculations and related rounding errors, there's
no goal in either math correctness in the strong sense (only in the IEEE 754
sense), nor in "Excel compatibility" (it could only be possible if we know
exact sequence of the calculations they perform, like if they do it from
left-to-right then top-to-bottom, or top-to-bottom then left-to-right, or any
other way for a given argument number of SUM). An error bound to rounding
errors caused by given correct order of computation is OK; if you can suggest
an optimization that gives better precision by re-arranging calculations,
without performance loss - it's fine to discuss in a dedicated issue like
"Function FOO can give better precision", with analysis of correctness of
suggested optimization, and comparison of performance and precision of old and
new ways.

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

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

[bugzilla-daemon]

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

--- Comment #21 from b.  ---
(In reply to Mike Kaganski from comment #20)
> (In reply to b. from comment #19)
> > or are values stored as floats there too?
> 
> This. Calc is not a key logger.

hello @Mike Kaganski, 

thanks for your comment, sorry for objecting, please correct me if i'm wrong, 

from a file saved with ver. 7.1, three cells with values: 

B3: -2
B4: -1,95
B5: 0,05

content.xml: 

... 

   
   
  -2
   


   
   
  -1,95
   


   
   
  0,05
   

... 

trailing zeroes are truncated on save, but the info about the significant
decimals keyed in by the user is visible there, besides the values being
denominated as 'float' i 'see' pure decimal 'fixed point' fractions there, 

as well as 'value' as as 'text', 

starting with theese values it is technically possible to calculate -0,05 as
correct result after 39 additions of 0,05 to -2, instead of
-0,0498, as calc just did, 

even with a 'fuzzy' floating point calculator: 

algorithm 1: 
take -2 
repeat 39 times: 
   [add 0,05 
   round to two decimals] 

algorithm 2: 
take -2
take -1,95
calculate difference
round to two decimals
calculate result times 39
round to two decimals
add to -2
round to two decimals

i accept that you are much deeper in the project and code and have profound
knowledge, i see that you did! work to enhance this point, 

but above sample is one of plenty where i - and some other 'simple minded
users' - don't understand why calc 'insists in producing obviously wrong
results', 

looking at the complaints here and in 'ask' and their long history and how time
consuming it is for devs and supporters to deal with theese complaints, and
estimating how many other users are irritated without expressing it and how
many may turn away from calc or spreadsheets in general ... 

i can't understand why there is such poor success in efforts to produce correct
decimal results ... as it would be so easy ... (imho, please correct me if i'm
wrong), 

sorry for the long post, i'd like to bring long lasting problems to points
where they are either solved or proven as unsolveable, 

to calm down: ex$el is only slightly better in these things, in this test it
has only one error in the first 62 lines instead of three (calc), and the first
error occurs in line 42 instead of 39 (calc), tested with ver. 2010, winx64, 

but ... calc misses both targets and can claim neither correctness nor ex$el
compatibility ...

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

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

[bugzilla-daemon]

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

--- Comment #20 from Mike Kaganski  ---
(In reply to b. from comment #19)
> or are values stored as floats there too?

This. Calc is not a key logger.

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

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

[bugzilla-daemon]

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

--- Comment #19 from b.  ---
one more suggestion ... inspired from: 

> 0.05*39=1.9502
> -2+0.05*39=-0.049822

the first calculation is 'two decimals' times 'integer' ... 

users would expect, and mathematical correct would be! - a result with max two
decimal digits, thus just round to that, 

same applies to second sample, 

and e.g. 3,45 * 4,567 'two decimals' times 'three decimals' could / should
frankly be rounded to max six decimals, 

addition / subtraction: round to max decimals of summands, 

multiplication: round to product of count of decimals, 

division: no chance, 

exponentiation: simple exponents: round to exponent times decimals in mantissa,
fractional exponents: no chance, 

radication: no chance, 

other calculations ... are patched together from above? 

handling like that won't be 'easy' - as the float's dont carry the info about
the source value they came from, but somewhere in the sheet you have the data
the user keyed in, at least in the file (or are values stored as floats there
too?),  

and if you have that data calculation can be done by a function which keeps the
info about the 'original digits', and does appr. rounding on the result, 

ok?

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

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

[bugzilla-daemon]

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

--- Comment #18 from b.  ---
just an idea ... 

intelligent rounding may lead to intelligent faults? 

the user expects a value / number fitting his input and his idea what to
calculate, 

A) would it work to round to the number of digits the user formatted the cells
to ... ??? 

B) or just bring 'precision as shown' to work, 

A: the format of the input cells is applied to the targets on dragfill? either
from them being formatted as 'two decimal places' or from having input of that
length it would be legitim to round the dragfill results to that frame? (i know
that @Mike doesn't like the format affecting the result ...) 

B: i tried dragfill with 'tools - options - libreoffice calc - calculate -
precision as shown' activated, and expected the artefacts to go away ... they
didn't ... :-(
even calculating a series of "=x+,05" starting from -2 with cells formatted to
two decimals and precision as shown activated produces 'artefacted values', you
can see that by replacing the formulas with their results (strg-c -
shift-strg-v with 'formulas' deactivated) while still formatted to two
decimals, and then observing the input line or reformat the cells for more
decimal places ... 

maybe i misunderstood the idea, concept or power of 'precision as shown'??? but
it was once recommended to me as a solution against fp rounding errors

tested with 6.2.8.2 winx64 unpar. 

reg. 

b.

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

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

[bugzilla-daemon]

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

Eike Rathke  changed:

   What|Removed |Added

   Keywords||difficultyInteresting,
   ||easyHack, skillCpp

--- Comment #17 from Eike Rathke  ---
Ah well, we can try, but if no one shows up to pick it we should do.

Code pointers:
sc/source/core/data/table4.cxx approxDiff() and its use in
ScTable::FillAnalyse() and all places in that file in functions
ScTable::*Fill*() that use SubTotal::SafeMult() and SubTotal::SafePlus().

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

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

[bugzilla-daemon]

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

--- Comment #16 from Mike Kaganski  ---
In double precision,

> 0.05*39=1.9502
> -2+0.05*39=-0.049822

First idea is that if we returned not only the increment from the approxDiff,
but also the number of decimals for rounding (which is 15 in this case), and
then used the rounding in the following calculation of the resulting increment
for each cell, then this specific sequence would be OK.

Yet, this is not a full solution, since this would fail with longer sequences,
like "-22;-21.95;...". That is because when multiplying the atomic increment,
we also need to decrease resulting absolute precision proportionally (so if
initially it's 5E-16 for 0.05, then for 0.05*39, it's 5*E-16*39=1.95E-14, so we
need to round the result of 0.05*39 to 14 decimals).

Eike: if agreed, I suggest to make this an easy hack.

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

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

[bugzilla-daemon]

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

--- Comment #15 from Mike Kaganski  ---
(In reply to Eike Rathke from comment #14)
> In the Input Line.

Ah yes, sorry. Blind me; must had missed that because of being tired in the
evening :-)

I don't think we need to limit the precision of calculation of the increment
here. The call in approxDiff is

> return rtl::math::round(0.050044, 15);

and it returns 0.050003, which is the closest binary representation
to 0.05 (see
https://www.binaryconvert.com/result_double.html?decimal=048046048053 ) - i.e.,
no additional limiting would improve this.

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

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

[bugzilla-daemon]

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

--- Comment #14 from Eike Rathke  ---
In the Input Line.

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

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

[bugzilla-daemon]

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

--- Comment #13 from Mike Kaganski  ---
(In reply to Eike Rathke from comment #12)
> and in fact in current master this bug here is not exactly reproducible
> anymore, but the imprecision instead starts at -0.1 with -0.0999

Sorry, I couldn't see a problem with sequences of -2,-1.95,... and -1,-0.9,...
in the first 100 cells - do I misunderstand what should I check?

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

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

[bugzilla-daemon]

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

Eike Rathke  changed:

   What|Removed |Added

 CC||mikekagan...@hotmail.com

--- Comment #12 from Eike Rathke  ---
The code introduced to fix bug 90419

commit e89c0e4fb783bd36d5f5fea154ee8608e542dae4
CommitDate: Mon Jun 13 13:53:19 2016 +0200

Resolves: tdf#90419 diminish precision error in Series Fill

was augmented to fix bug 129606 with

commit 8f46501233c164ff91d77a7f5adf74ea16cd0165
CommitDate: Wed Jan 8 15:57:19 2020 +0100

tdf#129606: limit precision in ScTable::FillAnalyse

and in fact in current master this bug here is not exactly reproducible
anymore, but the imprecision instead starts at -0.1 with -0.0999


@Mike: would it make sense to limit precision even one digit further, or would
we enter hell from another entrance then?

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

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

[bugzilla-daemon]

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

--- Comment #11 from b.  ---
there have also been improvements made by @Eike Rathke in bug #90419, could
something from that help for this bug?

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

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

[bugzilla-daemon]

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

Buovjaga  changed:

   What|Removed |Added

 Status|REOPENED|NEW

--- Comment #10 from Buovjaga  ---
Correcting status to NEW

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

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

[bugzilla-daemon]

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

b.  changed:

   What|Removed |Added

 CC||newbie...@gmx.de
 Status|RESOLVED|REOPENED
   Severity|major   |enhancement
 Resolution|WORKSFORME  |---

--- Comment #9 from b.  ---
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.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

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

[bugzilla-daemon]

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

raal  changed:

   What|Removed |Added

 Status|NEW |RESOLVED
 Resolution|--- |WORKSFORME

--- Comment #8 from raal  ---
Tested with LO 6 on Linux and Windows and the bug is fixed. Closing.

dev version:
http://dev-builds.libreoffice.org/daily/master/

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

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

[bugzilla-daemon]

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

--- Comment #7 from Xavier Van Wijmeersch  ---
Can not reproduce the error with, tested with both examples.

Version: 6.0.0.0.alpha0+
Build ID: 41b7713334351d7cc455eef5241bd3988b9d1e94
CPU threads: 8; OS: Linux 4.9; UI render: default; VCL: kde4; 
TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:master, Time:
2017-09-13_22:56:21
Locale: nl-BE (en_US.UTF-8); Calc: group

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

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

[bugzilla-daemon]

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

Thomas Lendo  changed:

   What|Removed |Added

 Blocks||107575


Referenced Bugs:

https://bugs.documentfoundation.org/show_bug.cgi?id=107575
[Bug 107575] [META] Number format bugs and enhancements
-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

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

[bugzilla-daemon]

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

Eike Rathke  changed:

   What|Removed |Added

 CC||er...@redhat.com
Version|3.6.2.2 release |Inherited From OOo
   See Also||https://bugs.documentfounda
   ||tion.org/show_bug.cgi?id=90
   ||419
 OS|Windows (All)   |All

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

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

[bugzilla-daemon]

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

--- Comment #6 from sharkylaser <2xd9yw+i0g75dzsv...@sharklasers.com> ---
Confirmed in 5.0.4.2

Attachment added.

Possible floating point error.

-- 
You are receiving this mail because:
You are the assignee for the bug.
___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
http://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

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

[bugzilla-daemon]

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

--- Comment #5 from sharkylaser <2xd9yw+i0g75dzsv...@sharklasers.com> ---
Created attachment 122375
  --> https://bugs.documentfoundation.org/attachment.cgi?id=122375=edit
Floating point error when dragging a list to auto fill

Demonstrates deterioration of auto filled incremental lists. Possible floating
point error.

-- 
You are receiving this mail because:
You are the assignee for the bug.
___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
http://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

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

[bugzilla-daemon]

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

Beluga todven...@suomi24.fi changed:

   What|Removed |Added

 CC||todven...@suomi24.fi

--- Comment #4 from Beluga todven...@suomi24.fi ---
Still repro.

Win 7 Pro 64-bit Version: 5.1.0.0.alpha1+
Build ID: 437210d58f32177ef1829d704f7f4d2f1bbfbfdd
TinderBox: Win-x86@39, Branch:master, Time: 2015-06-18_07:21:56
Locale: fi-FI (fi_FI)

-- 
You are receiving this mail because:
You are the assignee for the bug.
___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
http://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

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

[bugzilla-daemon]

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

--- Comment #3 from QA Administrators qa-ad...@libreoffice.org ---
** Please read this message in its entirety before responding **

To make sure we're focusing on the bugs that affect our users today,
LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed
bugs which have not been touched for over a year.

There have been thousands of bug fixes and commits since anyone checked on this
bug report. During that time, it's possible that the bug has been fixed, or the
details of the problem have changed. We'd really appreciate your help in
getting confirmation that the bug is still present.

If you have time, please do the following:

   *Test to see if the bug is still present on a currently supported version of
LibreOffice (4.4.1 or later)
   https://www.libreoffice.org/download/

   *If the bug is present, please leave a comment that includes the version of
LibreOffice and your operating system, and any changes you see in the bug
behavior

   *If the bug is NOT present, please set the bug's Status field to
RESOLVED-WORKSFORME and leave a short comment that includes your version of
LibreOffice and Operating System

Please DO NOT

   *Update the version field
   *Reply via email (please reply directly on the bug tracker)
   *Set the bug's Status field to RESOLVED - FIXED (this status has a
particular meaning that is not appropriate in this case)


If you want to do more to help you can test to see if your issue is a
REGRESSION. To do so: 

1. Download and install oldest version of LibreOffice (usually 3.3 unless your
bug pertains to a feature added after 3.3)

http://downloadarchive.documentfoundation.org/libreoffice/old/

2. Test your bug 
3. Leave a comment with your results. 
4a. If the bug was present with 3.3 - set version to inherited from OOo; 
4b. If the bug was not present in 3.3 - add regression to keyword


Feel free to come ask questions or to say hello in our QA chat:
http://webchat.freenode.net/?channels=libreoffice-qa

Thank you for your help!

-- The LibreOffice QA Team This NEW Message was generated on: 2015-04-18

-- 
You are receiving this mail because:
You are the assignee for the bug.
___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
http://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

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

[bugzilla-daemon]

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

--- Comment #2 from Owen Genat owen.ge...@gmail.com ---
Still reproducible (both in example and for new sheets) under Ubuntu 10.04
x86_64 running v4.1.3.2 Build ID: 70feb7d99726f064edab4605a8ab840c50ec57a.

-- 
You are receiving this mail because:
You are the assignee for the bug.
___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
http://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

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

[bugzilla-daemon]

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

Michael Stahl mst...@redhat.com changed:

   What|Removed |Added

  Attachment #68541|text/plain  |application/vnd.oasis.opend
  mime type||ocument.spreadsheet

-- 
You are receiving this mail because:
You are the assignee for the bug.
___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
http://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

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

[bugzilla-daemon]

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

billh...@y7mail.com changed:

   What|Removed |Added

 Status|UNCONFIRMED |NEW
 Ever confirmed|0   |1

--- Comment #1 from billh...@y7mail.com ---
Confirmed on LO 3.6.3rc1 Windows Vista 32 bit.

It looks like it is using Floating Point arithmetic instead of decimal to
calculate the values.

-- 
You are receiving this mail because:
You are the assignee for the bug.
___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
http://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs