Ask Your Question
0

Odd behaviour in LibreOffice Calc SUM function

asked 2017-03-23 03:44:27 +0200

bmclaren gravatar image

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

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
0

answered 2017-03-23 09:48:47 +0200

edit flag offensive delete link more

Comments

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.

bmclaren gravatar imagebmclaren ( 2017-03-25 17:37:15 +0200 )edit

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.

Mike Kaganski gravatar imageMike Kaganski ( 2017-03-25 18:13:19 +0200 )edit

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
Mike Kaganski gravatar imageMike Kaganski ( 2017-03-25 18:25:12 +0200 )edit

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

bmclaren gravatar imagebmclaren ( 2017-03-27 19:19:13 +0200 )edit

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.

Mike Kaganski gravatar imageMike Kaganski ( 2017-03-27 19:32:16 +0200 )edit

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

Touko gravatar imageTouko ( 2018-04-13 08:10:37 +0200 )edit

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.

MattHorner gravatar imageMattHorner ( 2019-02-10 23:47:32 +0200 )edit

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.

Mike Kaganski gravatar imageMike Kaganski ( 2019-02-11 06:55:48 +0200 )edit
0

answered 2017-03-23 07:48:29 +0200

pierre-yves samyn gravatar image

Hi

I reproduce if ToolsOptionsCalcCalculateLimit decimals for general number format unticked

Regards

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

2 followers

Stats

Asked: 2017-03-23 03:44:27 +0200

Seen: 265 times

Last updated: Mar 23 '17