[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

--- Comment #30 from b. <newbie-02 at gmx.de> ---
@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 '00000001~' 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 '10000100~', 

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.050000000000000044, 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.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.freedesktop.org/archives/libreoffice-bugs/attachments/20210227/fc7629f6/attachment.htm>