[Libreoffice-bugs] [Bug 140695] calc: wrong results and less precise than excel

Sun Mar 7 17:17:30 UTC 2021

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

--- Comment #3 from b. <newbie-02 at gmx.de> ---
@Peter S Anderson, thanks for checking, pls. do me a favour and set to 'new', 

(In reply to Aron Budea from comment #2)
> It's not a matter of rounding or precision. I haven't checked the code, but
> I'm guessing the case here is when to treat the result of a calculation as
> zero.

hello @Aron Budea, 

i assume - only assume - it's a matter of 'rounding by scaling' or similar in
something @erAck in 'ask' mentioned as 'approxAdd', the idea was indeed to let
0.1 + 0.2 look like 0.3, or 0.3 - 0.2 - 0.1 be '0', 

https://ask.libreoffice.org/en/question/274247/calc-wrong-calculation-would-like-a-recheck/

and then it's not a 'new' complaint from me but just another flavor, 

filed that as i thought showing difference and weakness 'behind ex$el' might
get some more attention by devs ... 

as far as i understood @erAck, it is not done by rounds but by truncation, i
didn't analyze it exactly because i was - first - too annoyed that a program
claims 'IEEE accuracy' for itself, and then throws away 4 bits and reduces
'doubles' to 48 bits, 

@@all: to eliminate errors in the last bit! with such methods might ???? make
sense / be allowed, but it is hard to catch this last bit when it has moved
forward by 'domino rounds', so it would need a more intelligent algorithm?, 

larger deviations should be fought by finding and eliminating their causes, 

'mayonnaise rounds' by more than one bit inevitably lead to errors in other
places, already one bit is dangerous, 

... all 'imho', corrections by experts welcome ... 

@erAck mentioned scaling 'too early', is it done by scaling the operands?
(yes?, brutalizing the last bits of the result wouldn't harm that much?) crazy?
in subtractions the last bits of the operands become quite important when
shifted left by 'cancellation', one shouldn't touch them without exactly
knowing how and why, 

and ... what might be good for additions ... might be bad for subtractions ... 

'weitz' is not the holy grail, but better than calc and ex$el for this case, it
evaluates '8.940696716308594E-8', thus either that or a correctly rounded
'1E-7' is acceptable as result for '=99999999,9999999 - 99999999,9999998',
nothing else ...  

calc knows! what is correct, just try 
'=RAWSUBTRACT(99999999,9999999;99999999,9999998)' or better 
'=ROUND(RAWSUBTRACT(99999999,9999999;99999999,9999998);15-MAX(LOG(99999999,9999999);LOG(99999999,9999998))) 
it's just a little uncomfortable to think of and type such complex formulas in
everyday use, programmed one time it could work for millions of users thousends
of times every day, 

did - again - read 'what every computer scientist should know ...': 

1. 'The standard puts the most emphasis on extended precision, making no
recommendation concerning double precision, but strongly recommending that
Implementations should support the extended format corresponding to the widest
basic format supported, ... ', 

2. 'However, when using extended precision, it is important to make sure that
its use is transparent to the user. For example, on a calculator, if the
internal representation of a displayed value is not rounded to the same
precision as the display, then the result of further operations will depend on
the hidden digits and appear unpredictable to the user.' 

there it is!, all both sins of calc in two sentences ... hey guys, that stuff
is 30 years old! 

calc doesn't use the 'extended precision' of the FPU (80 bit) but tries to make
it's own 'extension' by taking some (but too few) bits / digits away from the
normal result, hiding them from the user, and does not! make it 'transparent'
by 'correcting' the value used for calculations to that used for display, but
introduces a multi-fold / multi faced model combining a background of sometimes
'precise', sometimes 4-bit rounded/truncated, and in other places rounded to 12
sig. decimal digits values with an already inconsistent in itself UI of
sometimes 16 (integers) and sometimes 15 digit display (other values) ... 

that is a sixpack of ways to trick the user in a guessing game

sometimes adapting the calculation value and sometimes not: twelve ways ... 

!!! then the result of further operations will depend on the hidden digits and
appear unpredictable to the user !!!

-- 
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/20210307/a0738eed/attachment-0001.htm>