Odd behaviour in LibreOffice Calc SUM function

[bmclaren]

Ok, i have a weird one here. Calc has always done what was expected, until I ran into a strange calculation.

I am running the standard Ubuntu distributed LibreOffice - Build ID: 1:5.1.6~rc2-0ubuntu1~xenial1

With the following spreadsheet

Beginning at A1, going down through A8. (numbers validated for accuracy - no hidden formatting - I initially thought I entered something wrong the first time, so manual entered it into a new sheet with the same result)

 9.99
 14.99
 775.18
-26.44
-1.13
-280.24
-280.24
-212.11

In Cell A9, I put.

=SUM(A1:A8)

Cell A9 should contain 0, but instead shows

-1.22568621918617E-13

If I change cell A8 to

-212.1

Cell A9 shows 0.01 as expected.

Change it back to -212.11, and you get the weird result again.

Placing it in a single statement like…

=SUM(9.99,14.99,775.18,-26.44,-1.13,-280.24,-280.24,-212.11)

Also results in the correct answer of 0.

Formatting on all cells is “number → general”

If I force the format to “number → -1234”, the display is correct.

Any ideas what’s going on?

Thanks,

Brent

[PYS]

Hi

I reproduce if Tools▸Options▸Calc▸Calculate▸Limit decimals for general number format unticked

Regards

[mikekaganski]

https://bugs.documentfoundation.org/show_bug.cgi?id=70251

[bmclaren]

How can this be a hardware limitation. This is simple addition/subtraction. Computers get this right all the time. I could see if you did division with a repeating decimal being rounded and being limited by the number of decimal paces that were being calculated leading to a small error, but this is simple stuff you can get 100% right with a pencil and piece of paper.

[mikekaganski]

It’s just because you presume that numbers are handled by your computes with pencil and paper inside. Actually, they are not. They aren’t stored as decimals, that have finite representation as you input them. They are stored as binary numbers, that do have infinite representation, and so have rounding errors here.

To see it, you may use =SUM(A2:A9), and have -9,99000000000013. Excel will show -9,99000000000012. They differ because of opposite direction of computation, but error is there.

[mikekaganski]

The following formulas give exactly same results in Calc and Excel:

• =SUM(A8;A7;A6;A5;A4;A3;A2) → -9,99000000000013
• =SUM(A2;A3;A4;A5;A6;A7;A8) → -9,99000000000012
• =SUM(A8;A7;A6;A4;A3;A2) → -8,86000000000014
• =SUM(A8;A7;A6;A3;A2) → 17,5799999999999
• =SUM(A8;A7;A6;A4;A3) → -23,8500000000001
• =SUM(A8;A7;A6;A3) → 2,58999999999992

[bmclaren]

I can’t reproduce in Excel 14.0.7177.5000 (32-bit). Is there a setting in excel to make it exhibit the same behavior?

either way, I will change my setting to enabled for

Tools▸Options▸Calc▸Calculate▸Limit decimals for general number format

and call it a day.

If this is the answer, I believe the default should be changed. The results provided are not intuitive and would limit many peoples confidence in LibreOffice to provide accurate results.

Just my 2 cents

[mikekaganski]

To see the results above, you’d need to increase number of decimal places.

And you may just enter your data to Excel in reversed order, and see the sum.

[Touko]

As a update to @bmclaren 's answer: In my Mac UI the setting was behind File → Preferences → LibreOffice Calc → Calculate

[MattHorner]

Cannot believe, this ‘hardware limitation’ is still not worked around by default. as software engineer i can say, this is a typical problem, where the devs dont give a damn what users are experiencing. thank you bmclaren, your workaround helped me out.

[mikekaganski]

Working around this hardware limitation by default would at least (1) slow down the program manyfold, and (2) break existing documents. So please don’t treat not changing this as what you typically do being a software engineer.