The way SUM works

users
#1

Hello,

I have a problem with the way SUM works. At first I thought this was a bug, but then I saw Numbers on my iPad doing the same thing, so now I believe this is by design, but I have no idea why and how I can work around this.

Take a look at this example:

http://www.ode2.com/images/libreoffice_sum.tiff

The number format is currency (Polish Zloty), the numbers in the left column are entered by hand, the ones in the right one are calculated by multiplying the left number by 1,22.

The sum of the numbers in the right-hand column should be -1199,62, but it is off by 0,02 zł. It's pretty obvious this is because the program sums the numbers it has in memory and not the ones it displays.

If this behaviour is correct, how can I work around this? I just spent 40 minutes trying to find out why my balance is wrong only to find out the sums I was doing were giving me incorrect amounts :confused:

Thanks,

#2

The number displayed in a Calc cell is limited in decimal places by the
formatting preference. Set the number format for the cells to show more
decimal places and see the results.

In your case the sum calculation is based on the actual multiplication
result in the individual cells to a large number of decimal places, and
then the displayed sum shortened to show the decimal places set by the
formatting preference.

The (sum of the original entries) multiplied by (1,22) displayed to two
decimal places is not likely to display exactly the same as the sum of
(individual entries) multiplied by (the same multiplier).

Take a look at the 'ROUND' function.

#3

The number displayed in a Calc cell is limited in decimal places by the
formatting preference. Set the number format for the cells to show more
decimal places and see the results.

In your case the sum calculation is based on the actual multiplication
result in the individual cells to a large number of decimal places, and
then the displayed sum shortened to show the decimal places set by the
formatting preference.

The (sum of the original entries) multiplied by (1,22) displayed to two
decimal places is not likely to display exactly the same as the sum of
(individual entries) multiplied by (the same multiplier).

Take a look at the 'ROUND' function.

For each calculation in B use for example =round(a2*1.22,2) you will get a total of -1199.62
Rounding the sum of column A then rounding results in -1199.64

#4

nvrk wrote:

The number displayed in a Calc cell is limited in decimal places by the
formatting preference. Set the number format for the cells to show more
decimal places and see the results.

In your case the sum calculation is based on the actual multiplication
result in the individual cells to a large number of decimal places, and
then the displayed sum shortened to show the decimal places set by the
formatting preference.

The (sum of the original entries) multiplied by (1,22) displayed to two
decimal places is not likely to display exactly the same as the sum of
(individual entries) multiplied by (the same multiplier).

That pretty well SUMs it up. :wink:

#5

As has already been suggested, if these are currency amounts you should think twice about multiplying by 1.22 without any extra care. Setting the cell formatting to Currency affects the display but not calculation. You may want to ensure that the calculated amounts as stored are explicitly rounded in whatever way is appropriate.

But if you prefer, you can tell LibreOffice to do exactly what you want: to calculate using the values displayed, not those stored. To do this, go to Tools | Options... | LibreOffice Calc | Calculate, and tick "Precision as shown".

I trust this helps.

Brian Barker

#6

Hi,

There is a setting for calc where you can have the total as shown and not as calculated:

Settings

Go to tools, options, calc, calculate, and check the box "Precision as Shown"

Don

#7

Is this saved on a per spreadsheet basis or is it global (do I need to change it back and forth, if I open 2 sheets can one be "Precision as Shown" and the other normal.
steve

#8

Is this saved on a per spreadsheet basis or is it global (do I need to
change it back and forth, if I open 2 sheets can one be "Precision as
Shown" and the other normal.
steve

All options defined in *tools > options > LO calc > calculate* are stored in
the document,
these settings doesn't affect other files.

Gérard

#9

W dniu 21.12.2011 09:47, Gérard Fargeot pisze:

Is this saved on a per spreadsheet basis or is it global (do I need to
change it back and forth, if I open 2 sheets can one be "Precision as
Shown" and the other normal.
steve

All options defined in *tools> options> LO calc> calculate* are stored in
the document,
these settings doesn't affect other files.

Gérard

--
View this message in context: http://nabble.documentfoundation.org/The-way-SUM-works-tp3602412p3603495.html
Sent from the Users mailing list archive at Nabble.com.

All right, I believe this and the previous suggestions resolve my question perfectly.

Thank you all for your answers :slight_smile:

#10

Steve,

That is a global setting.

Don

#11

H Don. You are saying they global and Gérard is saying they are local. I might need to test to be sure.
steve

#12

Hi Steve,

I'm far from an expert. But, once something is selected in the tools, options........., that becomes the default for all new spreadsheets. It isn't something that you need to do every time you open a new sheet. I do settlement sheets. To have the right numbers, I need precision as shown. When I install LibreOffice, I always change calc for precision as shown. All new sheets are done that way unless I would go back and uncheck it. That is my definition of Global.

Don

#13

I'm far from an expert. But, once something is selected in the tools,
options........., that becomes the default for all new spreadsheets. It
isn't something that you need to do every time you open a new sheet. I
do settlement sheets. To have the right numbers, I need precision as
shown. When I install LibreOffice, I always change calc for precision as
shown. All new sheets are done that way unless I would go back and
uncheck it. That is my definition of Global.

Don

Hi,

We don't have the same définition of global.

You're right when you says "that becomes the default for all new
spreadsheets", when you create a new spreadsheet you need to define the
settings.
But other speadsheet (already save as) are not affected by this change.
That's why i wrote it is not global.

Gérard

#14

Thank you for the clarification. I never went back to test anything to see what changing that setting would do to a saved document.

Don

#15

Thanks, I understand. 90% of my sheets would be normal and 10% precision as shown. It is good to know that once saved I do not need to remember to change the setting to suit.
steve

#16

Hello,

I have a problem with the way SUM works. At first I thought this was a bug, but then I saw Numbers on my iPad doing the same thing, so now I believe this is by design, but I have no idea why and how I can work around this.

Take a look at this example:

http://www.ode2.com/images/libreoffice_sum.tiff

The number format is currency (Polish Zloty), the numbers in the left column are entered by hand, the ones in the right one are calculated by multiplying the left number by 1,22.

The sum of the numbers in the right-hand column should be -1199,62, but it is off by 0,02 zł. It's pretty obvious this is because the program sums the numbers it has in memory and not the ones it displays.

If this behaviour is correct, how can I work around this? I just spent 40 minutes trying to find out why my balance is wrong only to find out the sums I was doing were giving me incorrect amounts :confused:

Thanks,

Good evening Dominik,

The solution to calculate has you see (It's a wrong way to do that the result is false) consist in using the formula =ROUND("cell to multiply"*1,22;2) the result is an approximative result rounded 2 numbers after coma.

I thing that 22% is a tax so it would be better to trunk the result in te result cell in that exemple the resutant errror would be 0,82 cents in place of 1,82 cents if you round each result.

I hope to be right with your question..

  1,22
291,34 355,4348
109,29 133,334

  1199,6382

291,34 "=round(A8*B1;2) 355,43
291,34 "=round(A9*B1;2) 355,43
291,34 "=round(A10*B1;2) 355,43
109,29 "=round(A11*B1;2) 133,33

  "=SUM(C8:C11) 1199,62

Jacques CHAILLET