LibreOffice Calc can't 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.
not 32-bit, but 64-bit doubles, with the same principle. tdf#128312 and also FAQ.
If 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?