Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenSat, 08 Jul 2017 01:50:04 +0200Incorrect results produced by simple subtractionhttps://ask.libreoffice.org/en/question/109372/incorrect-results-produced-by-simple-subtraction/To reproduce:
In cell A1 enter the value 65941.152
In cell B1 enter the formula A1-65536
The value displayed in cell B1 will be 405.152000000002
Libre Office 5.3.1.2 Linux 4.9.18 64bit
Fri, 07 Jul 2017 21:16:32 +0200https://ask.libreoffice.org/en/question/109372/incorrect-results-produced-by-simple-subtraction/Comment by CC2099 for <p>To reproduce:</p>
<p>In cell A1 enter the value 65941.152
In cell B1 enter the formula A1-65536
The value displayed in cell B1 will be 405.152000000002</p>
<p>Libre Office 5.3.1.2 Linux 4.9.18 64bit</p>
https://ask.libreoffice.org/en/question/109372/incorrect-results-produced-by-simple-subtraction/?comment=109374#post-id-109374I should have asked an actual question rather than just showing how to obtain an incorrect result. Question: How do I obtain a correct result?Fri, 07 Jul 2017 22:01:34 +0200https://ask.libreoffice.org/en/question/109372/incorrect-results-produced-by-simple-subtraction/?comment=109374#post-id-109374Comment by Mike Kaganski for <p>To reproduce:</p>
<p>In cell A1 enter the value 65941.152
In cell B1 enter the formula A1-65536
The value displayed in cell B1 will be 405.152000000002</p>
<p>Libre Office 5.3.1.2 Linux 4.9.18 64bit</p>
https://ask.libreoffice.org/en/question/109372/incorrect-results-produced-by-simple-subtraction/?comment=109376#post-id-109376This is the correct result, in the sense that any calculations with floating-point values using computers are necessarily approximate, the error depends on hardware limitations. You should get familiar with this (see e.g. https://en.wikipedia.org/wiki/Floating-point_arithmetic#Accuracy_problems), and be prepared.Fri, 07 Jul 2017 22:32:51 +0200https://ask.libreoffice.org/en/question/109372/incorrect-results-produced-by-simple-subtraction/?comment=109376#post-id-109376Comment by Lupp for <p>To reproduce:</p>
<p>In cell A1 enter the value 65941.152
In cell B1 enter the formula A1-65536
The value displayed in cell B1 will be 405.152000000002</p>
<p>Libre Office 5.3.1.2 Linux 4.9.18 64bit</p>
https://ask.libreoffice.org/en/question/109372/incorrect-results-produced-by-simple-subtraction/?comment=109379#post-id-109379The OQer may get some additional hints from the secondary answer I added, in specific from the links placed there. [Yet another link](https://en.wikipedia.org/wiki/Double-precision_floating-point_format).Fri, 07 Jul 2017 23:14:43 +0200https://ask.libreoffice.org/en/question/109372/incorrect-results-produced-by-simple-subtraction/?comment=109379#post-id-109379Answer by Mike Kaganski for <p>To reproduce:</p>
<p>In cell A1 enter the value 65941.152
In cell B1 enter the formula A1-65536
The value displayed in cell B1 will be 405.152000000002</p>
<p>Libre Office 5.3.1.2 Linux 4.9.18 64bit</p>
https://ask.libreoffice.org/en/question/109372/incorrect-results-produced-by-simple-subtraction/?answer=109373#post-id-109373https://ask.libreoffice.org/en/question/90925/odd-behaviour-in-libreoffice-calc-sum-function/#90945
If you do the calculations with Excel, and set its precision to enough decimal digits, you will see the error (happening due to binary rounding), too.Fri, 07 Jul 2017 21:29:57 +0200https://ask.libreoffice.org/en/question/109372/incorrect-results-produced-by-simple-subtraction/?answer=109373#post-id-109373Answer by Lupp for <p>To reproduce:</p>
<p>In cell A1 enter the value 65941.152
In cell B1 enter the formula A1-65536
The value displayed in cell B1 will be 405.152000000002</p>
<p>Libre Office 5.3.1.2 Linux 4.9.18 64bit</p>
https://ask.libreoffice.org/en/question/109372/incorrect-results-produced-by-simple-subtraction/?answer=109378#post-id-109378(The answer expects everybody to *know* that "computers calculate with binary numbers". The answer below uses the more correct term "dyadic representation".)
There are two effects concerning non-integer arithmetic involved:
-1- There is no way to get generally closed round-trips by conversion between the dyadic and the decimal representation of numbers with fixed lengths (numbers of digits) for either system. Exact conversion from decimal to dyadic is strictly impossible for most non-integer starting values. Exact conversion from dyadic to decimal is only generally possible (in an impractical sense) if as many decimal digits are allowed as dyadic digits were used (which are in practice 52 for most computer applications).
-2- Cancellation of significant digits by subtraction. See **[this](https://en.wikipedia.org/wiki/Loss_of_significance#Demonstration_of_the_problem)** paragraph in the English wikipedia and the article it is contained in. The effect may manifest itself as a **stepping forward of insignificant digits**.
Concerning your example this was the final effect.
You may also try `=65941.008- 65941.0016` with 18 or more decimal places displayed.Fri, 07 Jul 2017 23:11:47 +0200https://ask.libreoffice.org/en/question/109372/incorrect-results-produced-by-simple-subtraction/?answer=109378#post-id-109378Answer by m.a.riosv for <p>To reproduce:</p>
<p>In cell A1 enter the value 65941.152
In cell B1 enter the formula A1-65536
The value displayed in cell B1 will be 405.152000000002</p>
<p>Libre Office 5.3.1.2 Linux 4.9.18 64bit</p>
https://ask.libreoffice.org/en/question/109372/incorrect-results-produced-by-simple-subtraction/?answer=109397#post-id-109397From 5.4 there is a new function ROUNDSIG() [Add function to Calc that rounds to significant digits](https://bugs.documentfoundation.org/show_bug.cgi?id=105931), as the issue usually happens on the last number of the number's precision (15 digit plus sign), a `=ROUNDSIG(A1-65536;14)` should solve much of the cases, like this one, with only one significant digit it's lost.
Sat, 08 Jul 2017 01:50:04 +0200https://ask.libreoffice.org/en/question/109372/incorrect-results-produced-by-simple-subtraction/?answer=109397#post-id-109397