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: