sum of cells

[Tricia]

When I get the sum of 10 cells it comes up 2 cents short of what it should be
when checked on the calculator. Is this a bug?

 

[Conan Kelly]

Tricia,

Probably not. Remember that the value of a cell and what it displays are
not alwasy the same. What is showing in the cell could be rounded up from
the actual value. And what you are adding on the calculator is probably
what is displayed.

If these cells are not fomulas, click on each cell and look in the formula
bar to see its actual value. If they are fomulas, copy the cells and paste
special as values in unused cells off to the side and then check their
values.

If you are working with decimal values, run the decimal places out 2, 3, 4,
or more decimal places then it is already set at.

HTH,

Conan

 

[David Biddulph]

No, it's not likely to be a bug.
If your 10 source cells are currently showing values to 2 decimal places,
try seeing what happens if you reformat them to show more decimal places.
If you have values which you are rounding to 2 decimal places on display,
and you want to use in your addition those rounded values (rather than the
more precise values that are actually in the cell) there are at least 2
options.
One is to round each value before the addition, either by using a helper
column with =ROUND(A2,2) and then summing those values, or by using the
*array formula* =SUM(ROUND(A2:A11,2)).
Another option is to use Tools/ Options/ Calculation/ Precision as
displayed, but I wouldn't recommend that as it can give you surprising
results elsewhere.

 

[FSt1]

hi
Possibly not. you may be experiencing a phenomenon known as round off.
hard to tell since you didn't say how you got the numbers in excel.
extend the decimal places in excel on your sum range to make sure. remember
excel adds everything, not just what's formated to show.

regards
FSt1

 

[Dave Peterson]

J.E. McGimpsey explains it and offers a solution:
http://mcgimpsey.com/excel/pennyoff.html

 

[Gord Dibben]

Have a look here for the "why" and workarounds.

http://www.mcgimpsey.com/excel/pennyoff.html


Gord Dibben MS Excel MVP