Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenWed, 12 Feb 2020 03:33:38 +0100LibreOffice Calc can't even add decimals to 2dp accuratelyhttps://ask.libreoffice.org/en/question/228979/libreoffice-calc-cant-even-add-decimals-to-2dp-accurately/A spreadsheet is often used for finances and for people with much larger sums than me, yet it can't add accurately even small amounts.
- Cell A1: 10793.57
- Cell A2: 2009.31
- Cell A3: 6304.88
- Cell A5: =A1+A2-A3
Cell A5 *should* evaluate to 6498, but Calc in fact returns 6497.99999999998. Yes, a tiny difference, but when I'm then using conditional formatting to check the whole thing balances by comparing this to a value in another cell, it doesn't. And when the cell is already formatted for currency, the cause of the problem isn't even visible.
I'm sure someone's going to reply talking about 32-bit floats (which I understand the principle of), but why use a datatype which isn't good for the job. And if that is the case, is there way to force a better data type or I have really got to start incorporating ROUND into things?
Interestingly, if I omit the decimal points (i.e. multiply each value by 10) it does evaluate to precisely 649,800.
I am using Version: 6.3.1.2 (x64) — yes, so 64 bits it can play with, so it seems as if it should be doable.Tue, 11 Feb 2020 21:20:52 +0100https://ask.libreoffice.org/en/question/228979/libreoffice-calc-cant-even-add-decimals-to-2dp-accurately/Comment by ve3oat for <p>A spreadsheet is often used for finances and for people with much larger sums than me, yet it can't add accurately even small amounts.</p>
<ul>
<li>Cell A1: 10793.57</li>
<li>Cell A2: 2009.31</li>
<li>Cell A3: 6304.88</li>
<li>Cell A5: =A1+A2-A3</li>
</ul>
<p>Cell A5 <em>should</em> evaluate to 6498, but Calc in fact returns 6497.99999999998. Yes, a tiny difference, but when I'm then using conditional formatting to check the whole thing balances by comparing this to a value in another cell, it doesn't. And when the cell is already formatted for currency, the cause of the problem isn't even visible.</p>
<p>I'm sure someone's going to reply talking about 32-bit floats (which I understand the principle of), but why use a datatype which isn't good for the job. And if that is the case, is there way to force a better data type or I have really got to start incorporating ROUND into things?</p>
<p>Interestingly, if I omit the decimal points (i.e. multiply each value by 10) it does evaluate to precisely 649,800.</p>
<p>I am using Version: 6.3.1.2 (x64) — yes, so 64 bits it can play with, so it seems as if it should be doable.</p>
https://ask.libreoffice.org/en/question/228979/libreoffice-calc-cant-even-add-decimals-to-2dp-accurately/?comment=228982#post-id-228982If all the values in your data have only two decimal places, why are you worried about an answer with 11 decimal places? Remember that the *precision* of your original data is only 2 decimal places. What happens when you format cell A5 to have only 2 or 3 or even 4 decimal places, so as to be more consistent with the *precision* of your original data?Tue, 11 Feb 2020 22:11:40 +0100https://ask.libreoffice.org/en/question/228979/libreoffice-calc-cant-even-add-decimals-to-2dp-accurately/?comment=228982#post-id-228982Comment by Mike Kaganski for <p>A spreadsheet is often used for finances and for people with much larger sums than me, yet it can't add accurately even small amounts.</p>
<ul>
<li>Cell A1: 10793.57</li>
<li>Cell A2: 2009.31</li>
<li>Cell A3: 6304.88</li>
<li>Cell A5: =A1+A2-A3</li>
</ul>
<p>Cell A5 <em>should</em> evaluate to 6498, but Calc in fact returns 6497.99999999998. Yes, a tiny difference, but when I'm then using conditional formatting to check the whole thing balances by comparing this to a value in another cell, it doesn't. And when the cell is already formatted for currency, the cause of the problem isn't even visible.</p>
<p>I'm sure someone's going to reply talking about 32-bit floats (which I understand the principle of), but why use a datatype which isn't good for the job. And if that is the case, is there way to force a better data type or I have really got to start incorporating ROUND into things?</p>
<p>Interestingly, if I omit the decimal points (i.e. multiply each value by 10) it does evaluate to precisely 649,800.</p>
<p>I am using Version: 6.3.1.2 (x64) — yes, so 64 bits it can play with, so it seems as if it should be doable.</p>
https://ask.libreoffice.org/en/question/228979/libreoffice-calc-cant-even-add-decimals-to-2dp-accurately/?comment=228980#post-id-228980not 32-bit, but 64-bit doubles, with the same principle. tdf#128312 and also [FAQ](https://wiki.documentfoundation.org/Faq/Calc/Accuracy).Tue, 11 Feb 2020 21:23:47 +0100https://ask.libreoffice.org/en/question/228979/libreoffice-calc-cant-even-add-decimals-to-2dp-accurately/?comment=228980#post-id-228980Answer by JohnHa for <p>A spreadsheet is often used for finances and for people with much larger sums than me, yet it can't add accurately even small amounts.</p>
<ul>
<li>Cell A1: 10793.57</li>
<li>Cell A2: 2009.31</li>
<li>Cell A3: 6304.88</li>
<li>Cell A5: =A1+A2-A3</li>
</ul>
<p>Cell A5 <em>should</em> evaluate to 6498, but Calc in fact returns 6497.99999999998. Yes, a tiny difference, but when I'm then using conditional formatting to check the whole thing balances by comparing this to a value in another cell, it doesn't. And when the cell is already formatted for currency, the cause of the problem isn't even visible.</p>
<p>I'm sure someone's going to reply talking about 32-bit floats (which I understand the principle of), but why use a datatype which isn't good for the job. And if that is the case, is there way to force a better data type or I have really got to start incorporating ROUND into things?</p>
<p>Interestingly, if I omit the decimal points (i.e. multiply each value by 10) it does evaluate to precisely 649,800.</p>
<p>I am using Version: 6.3.1.2 (x64) — yes, so 64 bits it can play with, so it seems as if it should be doable.</p>
https://ask.libreoffice.org/en/question/228979/libreoffice-calc-cant-even-add-decimals-to-2dp-accurately/?answer=229013#post-id-229013It isn't that Calc cannot add properly - it is that **you do not understand what you are doing**.
LO Calc calculates or stores a number to 20? decimal places but **the user tells Calc how many decimal places to display**.
Hence the **displayed number you see** is almost always different from the **number Calc is calculating with**.
Put A1 =1/3, B1=1/3 and C1=1/3. The value stored in each cell is 0.33333333333333333333 but if **you** choose to display only 1 decimal place, **you** will **see** 0.3 in each cell. Calc however has the value 0.33333333333333333333 in each cell and calculates with it.
If Calc now adds all three cells, each of which **displays** 0.3, Calc will **not** get 0.9, which is wrong. Calc will get 1.0, which is correct.
So, if you want your value 10,793.57, which is displayed to 2 places, to be exactly 10,793.57 (which is 10,793.57000000000000000000) **you** must round (or truncate) the stored value to 2 decimal places so that the **stored value** and the **displayed value** are equal.
If you increase the number of decimal places being displayed you will see that your values are not the exact 2 decimal place values **you have told Calc to display**.
Finally of course "someone's going to reply talking about 32-bit ..." because you are representing numbers by binary digits and there is a finite change when a digit is changed. Whereas 1/2 can be **exactly** represented as the binary number 0.1, 1/3 **cannot be exactly** represented as a binary number with a finite number of digits.Wed, 12 Feb 2020 03:33:38 +0100https://ask.libreoffice.org/en/question/228979/libreoffice-calc-cant-even-add-decimals-to-2dp-accurately/?answer=229013#post-id-229013Answer by keme for <p>A spreadsheet is often used for finances and for people with much larger sums than me, yet it can't add accurately even small amounts.</p>
<ul>
<li>Cell A1: 10793.57</li>
<li>Cell A2: 2009.31</li>
<li>Cell A3: 6304.88</li>
<li>Cell A5: =A1+A2-A3</li>
</ul>
<p>Cell A5 <em>should</em> evaluate to 6498, but Calc in fact returns 6497.99999999998. Yes, a tiny difference, but when I'm then using conditional formatting to check the whole thing balances by comparing this to a value in another cell, it doesn't. And when the cell is already formatted for currency, the cause of the problem isn't even visible.</p>
<p>I'm sure someone's going to reply talking about 32-bit floats (which I understand the principle of), but why use a datatype which isn't good for the job. And if that is the case, is there way to force a better data type or I have really got to start incorporating ROUND into things?</p>
<p>Interestingly, if I omit the decimal points (i.e. multiply each value by 10) it does evaluate to precisely 649,800.</p>
<p>I am using Version: 6.3.1.2 (x64) — yes, so 64 bits it can play with, so it seems as if it should be doable.</p>
https://ask.libreoffice.org/en/question/228979/libreoffice-calc-cant-even-add-decimals-to-2dp-accurately/?answer=228998#post-id-228998> A spreadsheet is often used for finances
Correct
> and for people with much larger sums than me
Correct
> yet it can't add accurately even small amounts
Wrong.
It can accurately add some kinds of small values, namely integers and any fraction which can be reduced to powers of two. This is because the internal data types use purely binary positional digits, which can only represent powers of two. This is perfectly analogous to the decimal system, which can accurately represent only fractions which can be broken down to powers of 2 and 5. For financial transactions, it is customary to impose the two decimals constraint, which means that only 4 fractions can be represented exactly in binary form (0.0, 0.25, 0.5, 0.75). All the 96 other allowed decimal fractions suffer from some tiny rounding error when stored in a spreadsheet.
You say that you understand the principle of floats, so I won't elaborate further. A double is just a higher precision float, so the same principle applies.
> why use a datatype which isn't good for the job
The datatype is not the problem. Spreadsheets select data type automatically. They are simple tools for the average user. To work efficiently, the most efficient data type has been chosen. Any spreadsheet user using such a tool for important work should know the tool's limitations and use the means provided to avoid error. Requiring users to predefine data types based on the nature of their data would be asking for trouble. Automatic selection between numerical data types would be asking for even more trouble.
> is there way to force a better data type
Yes, but not in any spreadsheet application that I know of. Several databases and programming languages support some variety of "decimal" data type, which will represent decimal fractions exactly as typed. Whether this is "better" is a matter of discussion. When you use data types not handled by CPU internals, there will be significant tradeoffs. Calculations will be slower (at a factor from 3 to 100, depending on platform and precision), and you need more bits to have numbers handling the same range and precision.
> I have really got to start incorporating ROUND into things?
Yeah! You got this.
The proper way in a binary context to deal with "artificial fractions" imposed by our number systems is to use rounding functions. They will not eliminate rounding errors, but can negate the effect of **accumulated** rounding errors. For decimal rounding, like in financial transactions which usually are "to the penny", ROUND(x;2) should be present far more frequently than is the case.
Likewise if you use spreadsheet time values for worker timesheets, MROUND(x;1/1440) will strip off accumulated errors and keep times "to the minute". For astronomical and other angle measures, MROUND(x;1/3600) will keep angles "to the arc second".
Start rounding your results to a sensible precision. Begin right now. Your life will be so much better for it. It may also in some contexts make you more conscious of your numbers when it comes to resolution/precision and error margins. Consciousness is good. Be happy!Wed, 12 Feb 2020 00:27:54 +0100https://ask.libreoffice.org/en/question/228979/libreoffice-calc-cant-even-add-decimals-to-2dp-accurately/?answer=228998#post-id-228998