Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Peculiar Result with SUM
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Peculiar Result with SUM
On Tue, 30 Dec 2008 06:34:06 -0800 (PST), Seanie
wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Peculiar Result with SUM
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/default...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 -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Seanie" wrote in message ... 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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Peculiar Result with SUM
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) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Peculiar Result with SUM
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)) -- David Biddulph "Seanie" wrote in message ... 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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Very Peculiar DV error Q | Excel Worksheet Functions | |||
Peculiar Date problem | New Users to Excel | |||
Peculiar date format behavior when replacing parts of date | Excel Discussion (Misc queries) | |||
How to do a peculiar sort? | New Users to Excel | |||
A peculiar PDF from Excel problem | Excel Discussion (Misc queries) |