How to make the summation to zero?

[lubbamkt]

Hi,

I attached a sample in which the result of summation is not zero. Can anyone tell me why? and as I am using the summation result in a pivot table and I filter out those items whose value equal or less than 0 but this kind of result is annoying (0.0000000000002274) and of course I cannot take this item out. How can I solve this problem?

Thanks
sample.xlsx

[MarvinP]

Hi,

This looks like a rounding problem. Read about this well known concern at: http://www.cpearson.com/excel/rounding.htm

The idea is that when computers do work, they do it in Base 2 numbers. Computers can get as close as the number of digits it can deal with. You need to round your values to something that will make your result be zero.

[lubbamkt]

Thanks MarvinP, I tried the functions mentioned in the website, but none can get the result of 0 in my sample. Does this mean that no other methods can solve this problem?

Hi,

This looks like a rounding problem. Read about this well known concern at: http://www.cpearson.com/excel/rounding.htm

The idea is that when computers do work, they do it in Base 2 numbers. Computers can get as close as the number of digits it can deal with. You need to round your values to something that will make your result be zero.

[MarvinP]

Your question is like asking for the exact decimal value for 1/3. The .333... repeats ENDLESSLY. If you carry the decimal out forever it never ends.

When we humans convert numbers to base 2 and have computers work with our numbers, we get rounding errors if we go beyond the limits of the digits used. This is a well known problem and has many sites to explain how to round values or only display the appropriate number of digits to satisfy our exacting minds.

Try this link to see if it helps:
http://support.microsoft.com/kb/214118