[Libreoffice-bugs] [Bug 109189] Statusbar sum and average calculation with decimal values equivalent to zero sometimes shows incorrect negative exponential value

[bugzilla-daemon at bugs.documentfoundation.org]

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

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

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

--- Comment #8 from b. <newbie-02 at gmx.de> ---
@all: careful, long, but has info with solution potential! 

@jay: nice job in narrowing down, you had the fish hooked, but i think it was
too diverse an interaction to understand right away? 

imho there are five effects mixed here: 

1. calc doe's some 'rough' rounding, especially near zero results, but not or
less strict for the statusbar, 

2. bin representations of most fractional decimal values are not exact but
contain small deviations, 

3. these deviations may add up in calculations, 

4. especially when subtracting values of similar magnitude deviations are
boosted (powered) in their relative! effect on the result, and thus sometimes
become visible, 

5. above may give different results for computing the same operands in
different order, 

6. calc uses different calculation engines for the sheet and for the status bar
resulting in different calculation orders, and thus - sometimes - different
results, ... why? ... i don't know ... 
- that for the sheet starts bottom right and calculates rightmost column bottom
up, then continues with nextleft column, 
- that for the statusbar starts leftmost column top -> down, then continues
with nextright column, 
- ex$el deviates and starts top row left to right, continues with rows top
down, 
  (i just filed a bug for that as it undermines calc - ex$el compatibility,
tdf#140691) 
- ex$el statusbar / autosum - to be honest - i haven' yet deciphered, it's
sometimes by row left right top down, sometimes by column top down left right,
and looks depending on the field marked, only the cells to be calculated or
empty cells around included and whatever more ... might be also where you
started marking, might be depending if marked by mousedrag or keyboard ... not
yet understood, 

^^^^above: info for this bug - vvvv below: further reflections (important?) 

didn't check how google or similar work, but already within theese three it's
deviations enough as fp-math is not in all cases associative (in fact it's only
for very few cases), and thus different results come up, 

if you then try to achieve ex$el compatibility by rounding you may quickly come
to a status as calc is now, rounding here and there, and nobody knows why it
doesn't work ... 

the idiosyncrasy of calc dates back to 3.5.1.2 and may be former ver., i -
assume - 'inherited', 

imho an important decision, 

what do we want? achieve full ex$el compatibility, or keep calc backward
compatible? 

and the longer i think about i get the feeling - 'assume' (sorry at erAck) - that
such might be one of the root causes for multiple calc problems, users complain
about deviating from ex$el, devs don't understand why ... 'let's try some more
rounding', not good!, 

step one: check if i'm right with that analysis, 

step two: spread that info, 

step three: let the big brains think about and find a solution, 

(my proposal: an option switch and two modi, 'calc - correct math' or 'ex$el -
as compatible as possible', then you would have clear targets and can work
straightforward ...) 

besides: repro with: 

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:

-- 
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/20210312/88b8445e/attachment-0001.htm>