[Libreoffice-bugs] [Bug 130221] New: calculation: problems with accuracy of 'decimal values', which IEEE format is used by calc?

Mon Jan 27 13:12:37 UTC 2020

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

            Bug ID: 130221
           Summary: calculation: problems with accuracy of 'decimal
                    values', which IEEE format is used by calc?
           Product: LibreOffice
           Version: Inherited From OOo
          Hardware: All
                OS: All
            Status: UNCONFIRMED
          Severity: normal
          Priority: medium
         Component: Calc
          Assignee: libreoffice-bugs at lists.freedesktop.org
          Reporter: newbie-02 at gmx.de

Description:

hello @all, 

since someone found it appropriate to suppress my comment 
https://bugs.documentfoundation.org/show_bug.cgi?id=129606#c12 
as 'off topic' i feel compelled to open a new bug, sorry @xisco, 

in #129606 @Mike solved a problem of unprecise and 'user irritating' decimal -
floating.point - binary - floating-point - decimal conversion in calc, but only
one special case? (dragfill series)

the problem seems more general, see red marked cells in sheet attached with
next comment. 

(e.g. '=(1234+0,12)-1234' which a simple minded user would expect to result in
0,12 produces 0,11999999999989100000 instead, with as well showing the rounding
problem as the irritating zeros behind it pretending the result to be more
accurate than it really is. it's neccessary to set display format to 13+
decimal places to see this happening, otherwise rounding steps in and a
seemingly but not really exact result is shown)

there are plenty irritations, comments and questions about this behaviour, as
well as several proposals and workarounds like 'use rounding for results' or
set 'calculation - precision as shown'. 

thoose are just 'crutches', and normally only used by users after having been
trapped. the rounded display as well is some help, but as normally a beginner
wouldn't use any of the workarounds calc will internally continue to calculate
with values affected by rounding issues, and at some point it'll come to the
surface and irritate users. 

i couldn't find one special old discussion about that problem in short time
(ending in 'learn floating point first'), instead one other which talks about
the problem and! a possible solution?. 

you can find it there: 

https://www.libreoffice-forum.de/viewtopic.php?f=6&t=16543&hilit=floating+point&start=10#p43772

the point: 

'A 'simple' solution to the dilemma would be to use the newer floating point
format IEEE 754-2008, because here an exact conversion of decimal values would
be possible even with decimal places.'

sounds somewhat promising to me, but i can't say how far it would reach. 

neither can i say how much work it would be to implement it and how deep the
compatibility cut would be. 

(at all, as the 'new' standard is quite old already, it might be that it or
parts of it are adopted already?)

wikipedia talks about an even newer version there: 
https://en.wikipedia.org/wiki/IEEE_754#2019

maybe there is a possibility to improve calc and get rid of user complaints
about unprecise calculations / rounding errors? correct exact calculations is
the first thing a user expects from a spreadsheet. 

as the discussion linked above is in german i tried a translation with deepl: 

************
Re: Calculation error / imprecision LO Calc

Contribution by Mr. Hobbybyte " Thu Aug 4, 2016, 10:45
after several attempts to narrow the whole thing down a bit,
I have come to the following conclusion :

- Calc calculates internally with 32 bit floating point values
- Intermediate values are stored in the (old) floating point format according
to IEEE754

and thus the problem is causal, as mikele above already correctly suspected,
was caused by an inaccurate conversion to the (old) floating point format.
This then leads to the inaccuracies described above when performing additions.
However, I would have suspected that calculations are either caused by an own
arithmetic or at least with the help of more precise intermediate values
take place. However, this is not (yet) the case.

@mikele
Your test shows exactly the problem with the intermediate values.
A 'simple' solution to the dilemma would be to use the newer floating point
format IEEE 754-2008, because here an exact conversion of decimal values would
be possible even with decimal places.
Since I haven't found the time to look at the source more closely, I don't know
if this is also would be easy to implement (bfp754.h), since all basic
functions are affected.

@Hiker
The "Accuracy as displayed" option may obscure the error under certain
circumstances, but in the calculation with fractions even produce new errors.
During the tests this option was shut down everywhere.
I have tested with different Excel versions from 97 - 2013 and found different
(in)accuracies has been determined. I even found some effects of the operating
system used in Excel (32 or 64 bit)
A 64 Bit Excel was not available for testing
According to my current knowledge, the problem of Excel is also only solved by
clever rounding with the help of the last Place(s) bypassed, but not really
solved.

Thanks and best regards
************

reg. 

b. 

Steps to Reproduce:
1. see red marked cells in sheet attached with next comment showing results
deviating from simple school mathematics and simple minded user expectations, 

Actual Results:
results inaccurate, affected by rounding / floating point representation
issues?

Expected Results:
mathematical correct results

Reproducible: Always

User Profile Reset: No

Additional Info:
Version: 7.0.0.0.alpha0+ (x64)
Build ID: 07b1159b79135857dd9a450c3bb9ae0a944ebcf9
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:

-- 
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/20200127/ff0f55a5/attachment-0001.htm>