Peculiar Result with SUM

[Seanie]

I have 7 rows of data in one column, the SUM of which adds up to Zero,
as expected, except it doesn't. In the SUM cell formatting to 13
decimal place shows as 0000etc13. I've even retyped the 7 rows of data
(max of 2 decimal places) and my result is still the same. I even
pasted-special values ROUND after that and same result

How can that be?

 

[Lars-Åke Aspelin]

I have 7 rows of data in one column, the SUM of which adds up to Zero,
as expected, except it doesn't. In the SUM cell formatting to 13
decimal place shows as 0000etc13. I've even retyped the 7 rows of data
(max of 2 decimal places) and my result is still the same. I even
pasted-special values ROUND after that and same result

How can that be?

The reason is that Excel uses binary numbers with a finit number of
digits to store numbers. For decimal numbers that means they are often
rounded, i.e. the stored number is not exactly correct.
And when you do calculations, these small errors propagate and can
give results as you have experienced.

Here is a link to an article describing this in more detail
http://support.microsoft.com/kb/78113

Hope this helps / Lars-Åke

 

[Bernard Liengme]

Computers store numbers in binary (base 2) numbers. Some decimal (base 10)
numbers do not have an exact representation in binary. (Just as the fraction
1/3 has no exact decimal representation)
So we get minor errors such as the one you have found. You could avoid this
using =ROUND(SUM(A1:A7),2) to round your answer to 2 decimal places - since
this is the maximum precision of the data.

Want to learn more about the IEEE 754 standard?
Chip's clear explanation
http://www.cpearson.com/excel/rounding.htm

Floating-point arithmetic may give inaccurate results in Excel
http://support.microsoft.com/kb/78113/en-us

(Complete) Tutorial to Understand IEEE Floating-Point Errors
http://support.microsoft.com/kb/42980

What Every Computer Scientist Should Know About Floating Point
http://docs.sun.com/source/806-3568/ncg_goldberg.html
http://www.cpearson.com/excel/rounding.htm

Visual Basic and Arithmetic Precision
http://support.microsoft.com/defaul...port/kb/articles/Q279/7/55.ASP&NoWebContent=1

Others:
http://support.microsoft.com/kb/214118

http://docs.sun.com/source/806-3568/ncg_goldberg.html



Other replies may tell you about the option "Precision as Displayed" but
this can generate problems.
best wishes

 

[Seanie]

The funny thing is that as an e.g. I used the fomula below in my 7
rows of data, yet I still don't get to balance to Zero

=ROUND(214359.6,2)

 

[David Biddulph]

Work out the *exact* binary representation of 214359.6 and let us know your
answer.

You snipped the entire content of the message to which you were replying,
which gave many detailed references, but
=ROUND(SUM(A1:A7),2) is not the same as the array formula
=SUM(ROUND(A1:A7,2))