[Solved] Incorrect results from simple math

[ttimelo]

First, apologies if this is the wrong place to ask this.

I'm using LibreOffice 6.0.2.1.0+.

I stumbled across a strange result when I subtracted one cell from another. Specifically, I have 1118.77 in A1, 1120.21 in B1, and =B1-A1 in C1. When I click on C1, it shows at the bottom of the window Average: -1.44000000000005; Sum: -1.44000000000005. Why does this happen? To be clear the cell it shows the correct result of -1.44, but the sum shown at the bottom is off.

If I replace the cells with 118.77 and 120.21 the sum shows up correct at the bottom, but 218.77 and 220.21, it shows -1.44000000000005 again.

[FJCC]

This is a limitation of doing math on a computer. The computer does math in base-2, not the base-10 notation that is used for display, and is limited to a certain number of digits in a number. The computer cannot express certain numbers exactly, though they may look "simple" in decimal notation. It is the same problem that decimal notation has expressing the value of 1/3. It cannot be done exactly with a finite number of digits. This can lead to tiny errors in common calculations and is a big concern when a great number of calculations are needed.

[Zizi64]

Use the round function in your calculation:

Code: Select all

=ROUND(B1-A1;2)

[RusselB]

A couple of other options you could try are in Tools -> Options -> OpenOffice Calc -> Calculate
Namely Precision as shown and/or Limit decimals for general number format.

[ttimelo]

This is a limitation of doing math on a computer. The computer does math in base-2, not the base-10 notation that is used for display, and is limited to a certain number of digits in a number. The computer cannot express certain numbers exactly, though they may look "simple" in decimal notation. It is the same problem that decimal notation has expressing the value of 1/3. It cannot be done exactly with a finite number of digits. This can lead to tiny errors in common calculations and is a big concern when a great number of calculations are needed.

I understand that the math is done in base-2, but why are the results not consistant? As an experiment, I put 40.01 in A1, 40.02 in A2, selected both and dragged down until results started to break and they did at A12 where the result became 40.1200000000001. I do the same with 30.01 in C1, 30.02 in C2, select both and drag down. I drag to 40.12 and result is not broken. I keep going past 50 and it is still not broken. So what happened?

[Zizi64]

I understand that the math is done in base-2, but why are the results not consistant?

Because you can type-in two STRINGS but not two NUMBERS. (4 0 . 0 1 and 4 0 . 0 2)
The AOO will convert the typed-in strings to numeric values before it make any calculation. (And the FILL function must calculate the increase value based on the first two VALUES...) The difference is 0.01.

The number 0.01 (in decimal) seems as a "simple" number... But it equals (in the binary system) to

2^(-7) + 2^(-9) + 2^(-13) + 2^(-14) + 2^(-15) +2^(-16) + 2^(-18) + 2^(-20) + 2^(-21) + 2^(-22) ...
displayed it as a binary number: 0.0000001010001111010111

The sum of the displayed parts above equals 0.009999990463257
Read: a 22 bit binary numeric representation can not equals to the "simple" two decimals 0.01 number.
(22 bit, because the visible parts above have a coefficient value 1, and the invisible parts (2^(-1), 2^(-2), 2^(-3), ...etc) have a coefficient with 0 value.

The AOO/LO Calc works with 64 bit floating point numbers. They are really "wide" numbers, but they are not "infinite wide" representations.

It is pure mathematics, and it is not related DIRECTLY to the quality of the AOO/LO Calc (or any other software)...

[Zizi64]

Here is a better explanation:
http://www.exploringbinary.com/why-0-po ... ing-point/

and you can try it here (type-in 0.1 or 0.01 and convert it):
http://www.exploringbinary.com/binary-converter/

[ttimelo]

Thank you for the explanations and links. I've never noticed this before with other programs, I guess because they lied to me better?

Converted through that site, 30.01, 30.02, 40.01, and 40.02 gave:

11110.00000010100011110101110000101000111101011100001010001111010111...
11110.00000101000111101011100001010001111010111000010100011110101110...

101000.00000010100011110101110000101000111101011100001010001111010111...
101000.00000101000111101011100001010001111010111000010100011110101110...

I can't make out the difference in binary. But there was also a decimal to floating point converter. Perhaps LO uses the same conversion? It gave:

30.010000000000001563194018672220408916473388671875
30.019999999999999573674358543939888477325439453125

40.00999999999999801048033987171947956085205078125
40.02000000000000312638803734444081783294677734375

From that, the difference between 40.02 and 40.01 is wider than 30.02 and 30.01. If that wider difference is being used by fill and each iteration retains that little extra, it explains why after 12 iterations an unexpect (to me) result can show up. But it is curious that the less wide .01 doesn't seem to cause a similar issue.

[Zizi64]

I guess because they lied to me better?

Yes. The other name of the such lying is: Rounding.

[Zizi64]

Perhaps LO uses the same conversion?

Not same, but similar. The Floating point type representation is more difficult. You want to use numbers between (for example) 0.0000000123 to 1230000000.0 . Both of these numbers have 11 digits. Right? But what about the numbers like 1230000000.0000000123 ? You can represent them with 20 digit.
The floating point numbers descript the numbers with a mantissa, an exponent, and the +/- sign of the mantissa, and the exponent, Therefore the huge numbes and the very low value number will be described with same 64 bit width "numbers":

https://en.wikipedia.org/wiki/Double-pr ... int_format

That means for you: When you add a huge and a very low value number, somtime the decimal fraction part of result number will be lost!


Otherwise the 64 bit Floating point representation is enough for the most precise engineering calculations too. And the Calc uses such type of the numbers.

[Alex1]

40.01 needs one bit more for its integer part than 30.01. So 30.01 has one bit more available for its fraction, causing greater precision.

[keme]

For perspective:
Some 25 years ago a colleague of mine created a timesheet to keep track of personal working hours. All the office employees with flexible hours started using it. Three and a half years later, and all within a week or so, more than half of the users suddenly complained that they "lost" one minute.

This was just after an upgrade of Excel from v4 to v5, so naturally everyone blamed a bug in the software. Not so. Accumulated rounding error it was.

While this happened with MS Excel, it would have been the same with OpenOffice Calc (except ... at the time it would of course have been StarOffice Calc).

[Zizi64]

40.01 needs one bit more for its integer part than 30.01.

It is true in fixedpoint type representation of the numbers. But the AOO/LO uses floating point numbers

Here is the bit sequence of both numbers (as double precision floating point numbers):

Code: Select all

Decimal_sign___exponent_______________________mantissa__________________________
40.01 = __0___10000000100___0100000000010100011110101110000101000111101011100001
30.01 = __0___10000000011___1110000000101000111101011100001010001111010111000011

the difference IS NOT one bit in this case!

30.01 : Most accurate representation = 3.00100000000000015631940186722E1 it is more than 30.01
40.01 : Most accurate representation = 4.00099999999999980104803398717E1 it is less than 40.01

There is no way to achieve higher precision with the double precision (64 bit) floating point numbers!

you can try it:
http://www.binaryconvert.com/result_dou ... 8046048049

[jrkrideau]

I believe that if one needs extreme precision beyond what one can achieve with floating point numbers one needs to move to something like the commercial packages Maple or Mathematica. If memory holds, the Open Software Sage may also do this.

[Lupp]

@jrkrideau: You surely wanted to point to the Sage mathematical software (See https://www.sagemath.org/). Users searching for "Sage" on the web may be led to the commercial software offered by the Sage Group.

(For the bit of math I still do, I personally use wxMaxima. I don't know Sagemath and can't compare the solutions therefor.)

[jrkrideau]

@jrkrideau: You surely wanted to point to the Sage mathematical software (See https://www.sagemath.org/). Users searching for "Sage" on the web may be led to the commercial software offered by the Sage Group.

(For the bit of math I still do, I personally use wxMaxima. I don't know Sagemath and can't compare the solutions therefor.)

\
Oh, thanks Lupp, I was not aware of the commercial Sage software. My only experience with the https://www.sagemath.org/ software was downloading it for a friend. And I don't think she ever needed it while she was here. It looks like wx Waxima would have been as good a choice as Sagemath but I stumbled over Sage first.

I have never had a use for it. I am not that sophisticated mathematically.

[keme]

By the looks of it, all the titles listed above, and quite a few others, offer "arbitrary precision". IOW, you can have as many decimals as you like. This does not eliminate the rounding error. It will still be a potential threat on the last digits, and sums may not add up as expected. We just push it farther away from view.

We are used to think in decimal, where any fraction can be represented exactly as long as the denominator is a product of 2's and 5's.Computers do it in binary, where fractions where the denominator is a product of 2's can be represented exactly. Other fractions will lose precision when using binary arithmetic.

I did a bit of software testing/investigation for the school some years back, and remember using some software which could calculate with any rational fraction without losing precision. It did this by keeping separate numerator and denominator stored for each variable, and performed fraction calculations. I think it must have been Maxima or R, but I am not absolutely certain.

[Lupp]

We were wandering way off the original topic. Nonetheless all the mathematical software that was mentioned can work with integers beyond the limitations of the standards of computer arithmetic (processor-bound). Rational numbers are (by default) represented as cancelled fractions then. This again cannot strip off, however, the fetters concerning irrational numbers except for very special subsets.

[jrkrideau]

I think it must have been Maxima or R, but I am not absolutely certain.

Not R, at least not base R. The R FAQ has an entry---Why are these two numbers not equal?---for the precision problem. Floating point accuracy is the same in R & Calc.