Sum error

[Mark]

I am adding a column of about 12 numbers, some of which
are coming from another linked spreadsheet. The summed
total is off by 2! I don't want to puublish a budget
document that isn't adding correctly. Anyone got any
ideas as to what may be casuing the error? I used a Mac
version several years ago and it had an "SSum" command
that fixed what I suspect is a rounding issue. Any advice
would be appreciated

 

[Andy B]

Mark

It sounds like a rounding issue to me. Your cells are probably set to
display 2 decimal places, but the actual number in the sum is not rounded to
2 places. Excel will sum the actual figure in the cell, not the displayed
value. If you don't want to round the actual values themselves, use a helper
column to round them, and that should correct your total.

Andy.

 

[Paul]

I am adding a column of about 12 numbers, some of which
are coming from another linked spreadsheet. The summed
total is off by 2! I don't want to puublish a budget
document that isn't adding correctly. Anyone got any
ideas as to what may be casuing the error? I used a Mac
version several years ago and it had an "SSum" command
that fixed what I suspect is a rounding issue. Any advice
would be appreciated

When you format cells to show a particular number of decimal places
(including 0 to show as integers), the underlying values in the cells are
not affected. All that changes is what is displayed. But the underlying
values in the cells are used in calculations. If you don't want this, you
should round each value, preferebly in the formula that calculates it. The
alternative is to do the rounding in the SUM formula which will then need to
be array-entered (Ctrl+Shift+Enter rather than just Enter), for example:
=SUM(ROUND(A1:A20,0))