# calc: are decimal correct calculations possible?

hi @all,

!!! Caution! this is 'in progress', and I just noticed errors where I have to sort what are thinking errors of me and what are inadequacies of calc or tools (rawsubtract), see comments, I will adjust the question and example sheets based on my progress, sorry, maybe I started the discussion too early, but I need it - the discussion, and the results so far look so good that they certainly still have errors, this is normal, but probably will not fail completely ... !!!

there are a few - imho too many - questions on the internet 'why doesn't my spreadsheet calculate correctly?'

a few of the 'classics' are:

'=0,1 + 0,2 ' results in 0,300000000000004 instead of 0,3,

'=1234,12 - 1234' results in 0,119999999989100000 instead of 0,12 and

'=0.043 - 0.042' results in 0.000999999999999400 instead of 0.001,

attached is a sheet which calculates (more) correctly with a few primitive tricks, I would be glad if people with knowledge of the matter would have a look at it,

sample sheet, click for download

[edit - adding another version of the sheet, added a macro formula for calculation, and saved in tdf 1.2 extended format]

sample sheet with macro and in 1.2 format, click to download

i hope i included all neccessary macro help functions ...

[/edit]

mostly such questions are answered with 'that's how floating point maths is, but it's fast', 'learn first what that is (floating point mathematics)', 'it must be like that', 'we can't change it', 'look at Weitz (www.weitz. de/ieee), "the error is correct"', 'ex$el calculates the same way' and so on, the claim that a spreadsheet should and could calculate as 'correctly' as we once learned it in school is simply negated, either with 'impossible', or with 'performance',

I find this after 35 years of standard (IEEE 754) and 35 years of development (of LO calc) 'a bit weak', so it gets on my nerves so much that even comments like 'spam', 'ignorant person', 'your pet peeves' etc. didn't deter me to think about it,

I think at least many of the problems 'work better', and users should be allowed to get real results even if they are different from ex$el,

attached a sheet what shows that, red marked: wrong, green marked: better,

I don't say that this is one or 'the!' final solution or 'the last word', but it is a clear proof that:

- for many cases better results are possible,

- they can be achieved with relatively little effort,

- for the users much more comfortable and

- are much less error-prone than if the users always have to stumble over errors first and then have to manage the rounding themselves after recommendations for workarounds,

I would like to have an open discussion about whether it would be better to implement such calculation - at least as an option - in calc, and if not then why not,

in this discussion could be discussed / clarified ...

The problem is, that you expect insane decimal precision from an IEEE-754 40-bit or 80-bit FPU. Even though 80-bit floating-point has a high degree of precision, it still is limited to, you guessed it, 80 bits.

No one would use that many digits in practice, we're talking about decimal fractions that are completely impossible in the real world.

This is why exponential notation was invented.

It reminds me a bit of very green engineering students measuring a 5% resistor and then calculating the value to the 20th decimal.

Funny that you send us an Excel spreadsheet on a Libre Office site, though.

hello @ml9104,

`that you expect insane decimal precision`

... no!, what i'm fighting is use of such hardware in a way producing 'visible' fails,64-bit doubles have space for 1 micrometer accuracy for the distance earth - moon, calc fails on 0,043 - 0,042,

`we're talking about decimal fractions that are completely impossible in the real world`

... no!, i'm talking about calculating money to cents, distances to millimeters, times to seconds, correct!,`measuring a 5% resistor and then calculating the value to the 20th decimal.`

... bridge construction: calculate to the seventh decimal and then take it 'times two', that schould hold,`Funny that you send us an Excel spreadsheet on a Libre Office site, though.`

... häh? it's an *.ods made with LO calc 7.2.0.0.a0+, perhaps it's saved as 'tdf1.3', would you like it in 1.2?