Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Sum Function has small remainder
HI.
I am using the sum function to add three numbers that I know add to zero. I have typed these numbers in using the keyboard and none of these numbers have more than two decimal places. The numbers are 9,375.73 (9,362.27) (13.46) The answer given by the sum function is (0.00000000000087396756498492300000) I am formatting all these numbers as comma style. In comma style all true zeros show up as "-" and all numbers between (.005) and .005 exclusively are displayed as "(0.00)" This causes inconsistencies in how zeros are displayed in my report. I am using Excel 2003 on Windows 7 Thank you for your help Michael |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum Function has small remainder
"Hi I am Michael" wrote:
I am using the sum function to add three numbers that I know add to zero. I have typed these numbers in using the keyboard and none of these numbers have more than two decimal places. The numbers are 9,375.73 (9,362.27) (13.46) The answer given by the sum function is (0.00000000000087396756498492300000) This is not uncommon. Before I explain the problem, let me explain the solution. Whenever you expect arithmetic with non-integers to be accurate to a specific precision, always explicitly round the result. For example, if those numbers are in A1:A3, then: =ROUND(SUM(A1:A3),2) will avoid the problem. Alternatively, you could set the calculation option "Precision as displayed" (PAD) and format all of the cells as Number with 2 decimal places. But I do not recommend PAD for several reasons. The problem arises because Excel relies on the native computer binary representation and arithmetic, the industry-standard 64-bit binary floating-point. Consequently, most non-integers cannot be represented exactly. And this leads to infinitesimal differences when they are combined arithmetically. Sometimes the differences cancel out; sometimes they compound and become significant. In your case, the following shows the exact representations internally: 9375.73 9375.72999999999,956344254314899444580078125 -9362.27 -9362.27000000000,043655745685100555419921875 -13.46 -13.4600000000000,008526512829121202230453491210937 5 =SUM(A1:A3) -0.000000000000873967564984923,22862148284912109375 If you use paper and pencil to add the numbers on the right, you would probably get the result shown on the right in the last line. |
#3
|
|||
|
|||
Quote:
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to use small function? | Excel Discussion (Misc queries) | |||
Small Function | Excel Worksheet Functions | |||
Sum does not sum to zero - leaves very small remainder | Excel Discussion (Misc queries) | |||
SMALL function if 0 | Excel Worksheet Functions | |||
Remainder Zero Function? | Excel Worksheet Functions |