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

[bugzilla-daemon at bugs.documentfoundation.org]

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

--- Comment #4 from Peter S Anderson <peter at pza.net.au> ---
Created attachment 170956
  --> https://bugs.documentfoundation.org/attachment.cgi?id=170956&action=edit
Excel Sheet showing Calculation differences to Calc

I have reproduced this in 7.0.4.6 (x64) and found the same result. 
Enter 99,999,999.9999999 in one cell and 99,999,999.9999998 in another. Format
to show 7 decimal places to confirm. In another cell enter a formula to
subtract the two cells. The result is 0.0000000 (not 0.0000001). In fact it
seems to be exactly that.

I then did the same thing in Excel and initially got the same result but if I
use the formula =(A1-A2) the result was changes to 0.0000000894. Very strange.
Changing the formula makes no difference in LibreCalc, ie. the result is still
zero and incorrect.

I have attached a spreadsheet created in Excel and included a number of
interesting examples. Column A includes another formula :
=((A1/100)-(A2/100))*100

Excel gives 0.0000000931 (arithmetically correct to 15 significant figures) vs
the correct answer of  0.0000001 whereas Librecalc gives 0.00000000 again.

Column B is that same as Column A except that the initial data values differ in
the 6th decimal place rather than the seventh, ie.  99,999,999.999999 and
99,999,999.999998
Interestingly, the calculated values in Excel and Calc are the same suggesting
different processing logic in the two columns.

In column C I have changed the two initial values to be 999,999,999,999,999 and
 999,999,999,999,998 with the same formulae as in column A. This time Librecalc
and Excel give the same correct value of 1.0 for C1-C2 and (C1-C2) BUT 
=((C1/100)-(C2/100))*100 incorrectly gives 0 in Calc but a more accurate
0.9765625000 in Excel.

Finally, Column D has the initial values of 99,999,999,999,999 and 
99,999,999,999,998. In this case Calc and Excel give exactly the same answers.

To me it is very clear that Excel and Calc are giving different answers.  The
question is what if anything to do about it. My view is that someone needs to
take a look at the code to see what it happening. My simplistic view is that
Excel is giving better answers, ie. correct to 15 significant figures
(notionally compliant to IEEE 754) but a more detailed examination is required.
If any change is made to arithmetic processing in Calc it will probably be
necessary to introduce a settings flag to allow legacy sheets to give the same
“incorrect” results that they previously did.

-- 
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/20210405/b40e05bc/attachment.htm>