[Libreoffice-bugs] [Bug 126970] calc: calculation: cancellation and operand ordering effects in calc | was: Function "SUM()" work doesn't correct if final result is zerro.

[bugzilla-daemon at bugs.documentfoundation.org]

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

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

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |newbie-02 at gmx.de
            Summary|Function "SUM()" work       |calc: calculation:
                   |doesn't correct if final    |cancellation and operand
                   |result is zerro.            |ordering effects in calc |
                   |                            |was: Function "SUM()" work
                   |                            |doesn't correct if final
                   |                            |result is zerro.

--- Comment #7 from b. <newbie-02 at gmx.de> ---
consider some facts: 

1. a sum resulting in '0' normally is a subtraction, 

2. summing in fp-math produces small deviations, normally < 0,5 ULP of the
result and thus (fp-)precise results, 

3. in some rare cases sums produce bigger fails if the <0,5 ULP fails of the
operands sum up with a fail of 0,5 ULP regarding round-on or round-off of the
result to fit into (1+)52 bits of the mantissa, e.g. 0,1 + 0,2 ->
0,30000000000000004, 

4. subtractions in fp-math suffer from cancellation, which produces much bigger
deviations, especially 'catastrophic cancellation' at subtraction of nearly
identical values, 

5. both can be managed by 'smart rounding', but with some difficulties, 

5a. calc and excel perform some multi-step-decimal rounding while IEEE 754
already had performed a simple binary rounding, multi-step-rounding is nice for
some cases and evil for others, commonly 'no good practice',  

5b. calc and ex$el don't invest too much effort in this area as ... it's
difficult and is an effort that is usually not noticed or rewarded by the user

6. plenty influences mix up in a spreadsheet result, besides dec -> bin and bin
-> dec conversion rounding, the operands (binary 'even' representation or
endless fraction), fp-math operation fails (predominantly accumulations or
reinforcements of the deviations of the binary representation), weak libraries,
weak compilers or evil compiler options, programs rounding, and others also the
order in which operands are processed as @Oliver Brinzing pointed to, thus
expect deviations spread randomly, 

6a. calc calculates - where it is not given by formulas or calculation rules  -
in different order for a range in the sheet ('=SUM(Xn;Yn)': column by column
from right to left, in the columns bottom up) and in the statusbar if you
select that range (inverse sequence), 

6b. calc calculates - where it is not given by formulas or calculation rules  -
in a different order than ex$el does (row by row, top down, in the rows left to
right), which may result in different results, sometimes visible deviations,
sometimes invisible deviations, sometimes identical wrong results, in rare
cases identical exact results,

-- 
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/20210417/7d8482d7/attachment.htm>