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

English
,
#1

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.

Bad precision in Calc
#2

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

#3

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?

#4

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!

#5

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 will depend on the hidden digits and appear unpredictable to the user. (what every computer sci …
#6

… 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 download time, the proper channel is bugzilla and redmine, respectively. See this page.

#7

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 …

#8

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 yes, please spend more time checking is that’s a real solvable problem, because - well, you are on your own.

#9

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

#10

@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 :wink:
10,793.57 ‘nearest’ in double is 0,56999999999970~, ~000 isn’t possible,

#11

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.

Curious if() result
#12

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 and calc hides digits from his view which it takes for calculation: calc to blame - ‘user tricking’ …

#13

… 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 …

#14

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 enough for your purpose.

Anyone not understanding that will continue make stupid errors.

#15

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 consider whether answers like yours are dangerous because they are wiping away the care for inaccuracies …

#16

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

#17

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 :wink: 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 … :frowning: 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,9999998’ to zero I will keep nagging :wink: and am happy about everyone who supports the demand for ‘decimal correct math’ …

[/edit]

reg.

b.

P.S. ‘solved marks’ and ‘likes’ welcome,
click the grey circled hook - ✓ - top left to the answer to turn it green if the problem is solved,
click the “^” above it if you ‘like’ the answer,
“v” if you don’t,
do not! use ‘answer’ to add info to your question, either edit the question or add a comment,
‘answer’ only if you found a solution yourself …

#18

as long as calc calculates ‘=999999,9999999 - 99999999,9999998’ to zero I will keep nagging :wink:

Who wouldn’t. Luckily, it doesn’t. Never did. (check your formula :wink: )

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.

#19

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 :slight_smile: :slight_smile: :slight_smile:

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.