Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum does not sum to zero - leaves very small remainder
Hi,
I'm using SUM on a large spreadsheet of dollar type amounts that should only have information to 2 decimal places and should sum to zero. The problem is the sum function sums to a small remainder - in this case : 0.000000000069121597334742500000 I've looked at each value and none of them have any decimal values greater than 2 decimal places. Any ideas about why this is occuring ? Regards Michael |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum does not sum to zero - leaves very small remainder
When dealing w/decimals, it may be necessary to use the round function. See
Chip's site for discussion about this issue. http://www.cpearson.com/excel/rounding.htm "Michael" wrote: Hi, I'm using SUM on a large spreadsheet of dollar type amounts that should only have information to 2 decimal places and should sum to zero. The problem is the sum function sums to a small remainder - in this case : 0.000000000069121597334742500000 I've looked at each value and none of them have any decimal values greater than 2 decimal places. Any ideas about why this is occuring ? Regards Michael |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum does not sum to zero - leaves very small remainder
NG says the post wasn't received - so will try again:
When dealing w/decimals, it may be necessary to use the round function. See Chip's site for discussion about this issue. http://www.cpearson.com/excel/rounding.htm "Michael" wrote: Hi, I'm using SUM on a large spreadsheet of dollar type amounts that should only have information to 2 decimal places and should sum to zero. The problem is the sum function sums to a small remainder - in this case : 0.000000000069121597334742500000 I've looked at each value and none of them have any decimal values greater than 2 decimal places. Any ideas about why this is occuring ? Regards Michael |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum does not sum to zero - leaves very small remainder
Excel (and almost all other software) does binary math. In binary, the only
2-decimal fractions that can be exactly represented are .00, .25, .50, and ..75. All other 2-decimal fractions can only be approximated as a binary fraction, just as 1/3 can only be approximated as a decimal fraction. In 4-figure finite precision decimal, 1 - 1/3 -1/3 -1/3 would be 1 -0.3333 -0.3333 -0.3333 = 0.0001. In the same way, when you do finite precision binary math, there can be predictable minor departures from infiinte precision, such as you have seen. Since the issue is approximations to binary numbers that need infinite precision to represent exactly, and since you are only adding and subtracting 2-decimal fractions, rounding results to 2 decimal places does no violence to the calculations and can avoid the impact of the binary approximations. Jerry "Michael" wrote: Hi, I'm using SUM on a large spreadsheet of dollar type amounts that should only have information to 2 decimal places and should sum to zero. The problem is the sum function sums to a small remainder - in this case : 0.000000000069121597334742500000 I've looked at each value and none of them have any decimal values greater than 2 decimal places. Any ideas about why this is occuring ? Regards Michael |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Upgrading from Office 2003 Small Bus to Office 2003 Pro | Setting up and Configuration of Excel | |||
How to change cases from small to capital in all cells? | Setting up and Configuration of Excel | |||
Size of comment box in xml stored spreadsheet becoming small | Excel Discussion (Misc queries) | |||
Rolling over a Remainder | Excel Worksheet Functions | |||
small help can save mee!!!!!!!! | Excel Discussion (Misc queries) |