Ask Your Question
0

LibreOffice Calc can't even add decimals to 2dp accurately

asked 2020-02-11 21:20:52 +0200

WebSmithery gravatar image

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.

edit retag flag offensive close merge delete

Comments

1

not 32-bit, but 64-bit doubles, with the same principle. tdf#128312 and also FAQ.

Mike Kaganski gravatar imageMike Kaganski ( 2020-02-11 21:23:47 +0200 )edit

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?

ve3oat gravatar imageve3oat ( 2020-02-11 22:11:40 +0200 )edit

2 Answers

Sort by » oldest newest most voted
3

answered 2020-02-12 00:27:54 +0200

keme gravatar image

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 ... (more)

edit flag offensive delete link more
1

answered 2020-02-12 03:33:38 +0200

JohnHa gravatar image

updated 2020-02-12 04:00:07 +0200

It 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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-02-11 21:20:52 +0200

Seen: 109 times

Last updated: Feb 12