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

updated 2020-09-01 13:06:33 +0200

Alex Kemp 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

3 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

Comments

hello @keme,
like your conclusive and sensitive answer,
just two points:
1. if! you put the burden of rounding on the user, you should inform him about it, not only when he has attracted unpleasant attention with a wrong report in his job ... calc should start with a red warning strip: 'Attention, due to limitations in this program results can easily be falsified up to completely nonsensical, this program calculates differently than you learned it in school, check all results and round sensibly!
2. you shouldn't!!! put the burden of rounding on the user, already David Goldberg stated 30 years ago: 'However, when using extended precision, it is important to make sure that its use is transparent to the user. For example, on a calculator, if the internal representation of a displayed value is not rounded to the same precision as the display, then the result of further operations ...(more)

newbie-02 gravatar imagenewbie-02 ( 2021-03-07 23:36:03 +0200 )edit

... you should inform him about it, ... shouldn't!!! put the burden of rounding on the user ...

With all due respect, you must also understand that you are barking up the wrong tree. ("Respect" not used ironically. Honestly, I see your point, and there is sense to it. However, I do not agree, as the proposed/implied change would make Calc less compatible with other spreadsheet applications and/or less predictable. Disagreement is not always bad...)

This place is for advising on the use of the apps in the suite. We are not the developers, and cannot assume responsibility for the behavior (including messages at install/initiation time) of the apps. We know, more or less, how they work, and our advice is relative to that. We have no powers to alter the app itself

If you want to request an alteration to the app, or to the messages given at ...(more)

keme gravatar imagekeme ( 2021-03-08 09:07:49 +0200 )edit
1

hello @keme,
again: 'like your conclusive and sensitive comment,'
yes, i agree that disagreement are mostly not bad but sharpen both minds, and
yes, i know about the difference between 'ask' and 'bugs',
what i'd like to change - a little - is that on some points supporters in ask use less energy to reject users with e.g. 'fp-math is inaccurate' and more energy to separate - cleanly - the occurring problems into 'handling' vs. 'understanding' vs. 'real' problems and to produce well presented 'bugs' for real problems,
users coming 'fresh' here mostly can't do the latter ...

newbie-02 gravatar imagenewbie-02 ( 2021-03-09 07:18:09 +0200 )edit

supporters in ask use less energy to reject users with e.g. 'fp-math is inaccurate' and more energy to separate - cleanly - the occurring problems into 'handling' vs. 'understanding' vs. 'real' problems and to produce well presented 'bugs' for real problems

I do not hesitate to call some people idiots.

Because - well, I sincerely regret trying to call to @newbie-02's brain. I assume all responsibility for asking them to join Bugzilla; to participate in the process; and the resulting flow of endless spam at every single point where math is ever mentioned. I used to keep track of the problems in that field; and I myself have found (and fixed) several places where the problems were in Calc code, where others could assume those are just FP math inherent inaccuracies (e.g., tdf#130725). But thanks to @newbie-02 (aka b. in Bugzilla), now I'm skipping all of that. So ...(more)

Mike Kaganski gravatar imageMike Kaganski ( 2021-03-13 13:33:39 +0200 )edit
2

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

JohnHa gravatar image

updated 2021-03-13 13:09:48 +0200

It would help if you uploaded your spreadsheet because when I type your numbers in, Calc does make the subtraction equate to 6498. I therefore assume that your numbers 10793.57 etc came from a calculation and were not typed in.

I need to see the spreadsheet file to explain exactly what you are doing wrong but, in general, it isn't that Calc cannot add properly - it is that you do not understand what you are doing.

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

You are making two errors.

  1. The fundamental error you are making is thinking that a decimal fraction can be represented exactly by a binary fraction. It cannot - the binary fraction will be a very, very, very close approximation to the decimal fraction but it is not exact. How close? Like measuring the distance between London and New York to minute fractions of a hair's width.

These are how IEEE 754 64-bit, used on all PCs, represents 0.125 and the three following numbers. Note that 0.125 = 1/8 and is represented exactly. The next numbers, obtained by incrementing the binary by 1 digit each time, are forced to be approximations. It is not possible to represent a number between 0.12500000000000000000000000 and 0.12500000000000002775557561.

0 01111111100 [1]0000000000000000000000000000000000000000000000000000 = 1/8 = 0.12500000000000000000000000
0 01111111100 [1]0000000000000000000000000000000000000000000000000001 =     = 0.12500000000000002775557561
0 01111111100 [1]0000000000000000000000000000000000000000000000000010 =     = 0.12500000000000005551115123
0 01111111100 [1]0000000000000000000000000000000000000000000000000011 =     = 0.12500000000000008326672684
  1. The second error you are making is trying to see if the result of combining different approximations is exactly equal. They will be very, very, very close but they will never be exactly equal.

If I measure the distance between London and New York twice and I find that one measurement is a minute fraction of a hair's breath longer than the other, I would be a fool were I to insist that the distances were different in terms of calculation how long it would take me to fly at 500 mph. Use values appropriate for your needs.

What is the solution for you?

Solution 1: Don't ask if two fractions are equal - ask if two fractions are close enough for your purpose. Ask "is mod(a-b) < 0.000001", where you set 0.000001 to be as accurate as you need.

Solution 2: Round the numbers to, say, 6 places after the decimal point. Now compare them.

Solution 3: Read the manual and see what Tools > Options > Calc > Calculate > Precision as shown does.

In more general terms ...

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

edit flag offensive delete link more

Comments

@JohnHa: besides your explanations are not completely wrong ... did you check what you are talking about in this special case?
the values and calculation in the OP yields in 6498 + an invisible - likely fp-math originating - deviation of -9,0949~E-13,
the OP complains about a deviation of about -2,0008~E-11, that's about the 20-fold of that,
and ... decimal correct math is! possible with fp-figures, you just need to round at the right time to the correct amount of decimals,
and that should be carried out by the program instead of burdening the user and making it still largely impossible by implementing hidden falsifying rounding here and there ...

Solution 1: afaik calc already doe's 'approximate equality',
Solution 2: did you spot that calc sometimes injects errors when rounding (may be better now, have to check),
Solution 3: ask our friend @Lupp what he thinks about this crutch ;-)
10 ...(more)

newbie-02 gravatar imagenewbie-02 ( 2021-03-09 17:27:18 +0200 )edit

Spreadsheets are a compromise between ease of use and functionality.

If you want to explore resolutions of 1 part in 10^13 then don't use a spreadsheet because its rounding and its use of IEEE 754 floating point representations does not provide that level of resolution. 1 part in 10^13 is measuring the distance between London and New York to a resolution of 19 millionths of an inch!

There are many anomalous results when working at the very limits of IEEE FP representations - eg see Accuracy problems which says

"The fact that floating-point numbers cannot precisely represent all real numbers, and that floating-point operations cannot precisely represent true arithmetic operations, leads to many surprising situations. This is related to the finite precision with which computers generally represent numbers."

See Floating-point arithmetic.

JohnHa gravatar imageJohnHa ( 2021-03-09 20:33:27 +0200 )edit

hello @JohnHa, nobody asked to measure tenth of a hair, i told you that the fp-deviatons you are propagating are in that range and the OP's problem 20-fold bigger, thus there must be an additional problem,
and for fp-math: it's not unproblematic, but that's no reason to propagate wrong info about it, and no reason to stop searching for improvements,
improvements are! possible and at the moment it's more calcs handling of IEEE's than IEEE in itself causing trouble,
and a program which offers that fine numbers should calculate correctly with them, ans esp. should calculate correctly with bigger values,
any program as well as simple spreadsheets should - at least try to! - produce correct math,
the OP didn't ask for atomic precision, he wants 'allday money amounts' calculated correctly,
if he has a too small cell format - his problem, if it's wide enough ...(more)

newbie-02 gravatar imagenewbie-02 ( 2021-03-10 00:57:52 +0200 )edit

... already David Goldberg stated 30 years ago:
'The standard puts the most emphasis on extended precision, ... should support the extended format corresponding to the widest basic format supported, ...'
'Extended precision in the IEEE standard serves a similar function. ... However, when using extended precision, it is important to make sure that its use is transparent to the user. For example, on a calculator, if the internal representation of a displayed value is not rounded to the same precision as the display, then the result of further operations will depend on the hidden digits and appear unpredictable to the user.
[What Every Computer Scientist Should Know About Floating-Point Arithmetic ]
often mentioned but seldom heeded ...

newbie-02 gravatar imagenewbie-02 ( 2021-03-10 01:07:33 +0200 )edit

newbie

You say "nobody asked to measure the [width] of a hair,"

That is exactly what the poster is asking.

The poster has added decimal fractions 10793.57 + 2009.31 + 6304.88. None of these numbers can be represented exactly - they are all "the closest number which IEEE 754 can achieve".

He then asks why his result 6497.99999999998 is different from 6498.

It is different by a tiny, tiny, tiny, tiny, tiny amount because all PCs use binary representation and decimal fractions (except powers of two like 1/2, 1/4, 1/8 etc) cannot be represented exactly. The amount is insignificant - it is like measuring the distance from London to New York to a resolution of two thousandths of an inch, a fractions of a hair's width.

You should never ask if two floating point number decimal fractions are equal - you should ask is they are close ...(more)

JohnHa gravatar imageJohnHa ( 2021-03-13 12:01:57 +0200 )edit

hello @JohnHa:
'That is exactly what the poster is asking.' - no, he's asking two values with four integer and two fractional decimal digits being equal being treated and shown as equal by a piece of software, or 'calc shows a difference, how come?' that's a meaningful question, and your answer 'just fp imprecision' is misleading by a factor of ~20,
you also commented on that:
'You should never ask if two floating point number decimal fractions are equal - you should ask is they are close enough for your purpose.' - or you should ask the software to do better calculations and / or check your input!
pls. read my answer from 'besides: tried your figures in calc 7.2.0.0.a0+, looks correct even with 20dp, also in 4.1.6.2 and 6.2.8.2, ' on, check if i'm correct, and if take some time to ...(more)

newbie-02 gravatar imagenewbie-02 ( 2021-03-13 12:43:40 +0200 )edit

I shall waste no more of my time as everything is explained above.

JohnHa gravatar imageJohnHa ( 2021-03-13 12:47:01 +0200 )edit
0

answered 2021-03-07 23:19:53 +0200

newbie-02 gravatar image

updated 2021-03-13 12:20:49 +0200

hello @WebSmithery,

hello @all, sorry for long, the more confusion accumulates, the longer it takes to resolve it ...

calc is not bad, but it's a bad situation,

users expect 'school math compliant' results,

[edit] - edit 2021-03-09 07:30

users expect 'ex$el compatibility' too,

and that's sometimes different to 'correct math',

calc's alternating higher-order compatibility and school-math requirements and sometimes 'third ways' cannot fully satisfy any of all these desires

IEEE 754 - as used by calc - doesn't deliver - school math,

[/edit]

calc uses this datatype reg. 'performance',

if users complain they are asked to round, which has two drawbacks:

  1. the performance is gone,
  2. it's often difficult to decide which rounding to apply, it's much and error prone work for the users,

most critical operation is subtraction of figures of similar magnitude, if you don't want to do too much handcrafting you may try the following formula or similar, it'll 'adapt' to the magnitude of the operands and somewhat automatically round of fp-artefacts while leaving valueable content untouched,

'=ROUND(RAWSUBTRACT(B60;B61);14-MAX(INT(LOG(B60));INT(LOG(B61))))'

be aware that rounding had it's own shortcomings in calc, improved shortly but i'm not sure if they are all gone ...

besides: tried your figures in calc 7.2.0.0.a0+, looks correct even with 20dp, also in 4.1.6.2 and 6.2.8.2,

it has! a small deviation of -9,09494701772928E-13 but that's kept invisible by calc (but may affect comparisons), you complain about 20 times bigger deviation,

could it be your input values are not exact keyed in but somewhat calculated and different from what you posted?

check if they contain formulas, check content with wider cell format and format display to more decimal places, or use 'rawsubtract',

that is another big issue, accuracy problems are often wiped away with the general phrase 'fp-mat is imprecise' without going into the individual case in more detail ... because the cases are so frequent and similar, but they are not all the same ...

don't worry, you are still right, calc makes errors, even in the 2dp range, try '=1234,12 - 1234' ...

[edit] - edit 2021-03-09 06:47

as long as calc calculates '=999999,9999999 - 99999999,9999998' to zero I will keep nagging ;-) and am happy about everyone who supports the demand for 'decimal correct math' ...

was most likely not a typo but a fault of deepl (www.deepl.com) which i sometimes use to help me with my 'modest english', it likes not only 'bit-eating' from strings like '1111111111', but also zeros and as i see now '999999's as well ... :-( another thing in this world which is as it shouldn't be, if you retest: I recently noticed that the number must be followed by text so that it is screwed up, and ... it happens not always but only from time to time ...

the correct sentence was meant as:

as long as calc calculates '=99999999,9999999 - 99999999 ... (more)

edit flag offensive delete link more

Comments

2

as long as calc calculates '=999999,9999999 - 99999999,9999998' to zero I will keep nagging ;-)

Who wouldn't. Luckily, it doesn't. Never did. (check your formula ;-) )

Seriously, though, nagging about it in this place is like complaining to the postman that you can't understand the letters from your pen pal when he's Russian and you never learned cyrillic.

Generally, this place is populated by volunteers who know how Calc & al. works, and offer help based on that. Most of us have no role in the development of the software, nor in the running of the website offering downloads of said software. There are notable exceptions, but my guess is that they will not act upon your nagging unless you perform it in the sites designated for that.

To advance your nagging effort towards something effective, I suggest you start here.

keme gravatar imagekeme ( 2021-03-08 18:02:17 +0200 )edit
1

hello @keme,
again: 'like your conclusive and sensitive style, and your sharp eyes,'
thanks for the formula hint, corrected the question,
to expand the postman analogy: if i - or users - would write a letter back requesting the sender to write in german, and write that in german, and the sender can't read or write that we are lost ...
but if the postman is multi-lingual and can talk to both of us it's a possibility to ask him to translate, or get help from him to find another language both writers do understand or whatever ... he is the connector between us, and besides one can see it as overstretching his role he could be of great help ... as you can :-) :-) :-)

newbie-02 gravatar imagenewbie-02 ( 2021-03-09 07:29:14 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

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

Seen: 513 times

Last updated: Mar 13