Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2007 SP2 Addition of Negative Numbers Error
I have a simple formula in e2:
"=+a2+b2+c2+d2" or "=sum(a2:d2)" The format has 20 decimal places and values are as follows: a2 = -2048.01 b2 = 1 c2 = 2048.02 d2 = -1 e2 (the result of the formula) = 0.00999999999976353 I have played with this quite a bit. It appears that if you add a positive and a negative number with a decimal and the result is within 100+/-, you start getting the calculation error. I have tried this on XP, Vista and Windows 7. So, this works: (2048.01) + 1948.01 = 100 but (2048.01) + 1949.01 = -99.0000000000002 This is really bad for all accountants trying to balance a work sheet. Does anyone know if there is already a patch for SP2? Tim |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2007 SP2 Addition of Negative Numbers Error
=ROUND(SUM(A2:D2),2)
-- Gary''s Student - gsnu200851 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2007 SP2 Addition of Negative Numbers Error
That may help me work around the issue, but if Excel is unable to properly
add a positve and a negative number, can I trust it to round properly? I have tried several different numbers, and it throws out some really interesting results to the right of the decimal. -- Tim "Gary''s Student" wrote: =ROUND(SUM(A2:D2),2) -- Gary''s Student - gsnu200851 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2007 SP2 Addition of Negative Numbers Error
This is simple rounding error. Avoiding it with:
"Precision as displayed" option is dangerous for thing like compound interest, etc. =ROUND() is better. -- Gary''s Student - gsnu200851 "tboggs13" wrote: That may help me work around the issue, but if Excel is unable to properly add a positve and a negative number, can I trust it to round properly? I have tried several different numbers, and it throws out some really interesting results to the right of the decimal. -- Tim "Gary''s Student" wrote: =ROUND(SUM(A2:D2),2) -- Gary''s Student - gsnu200851 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2007 SP2 Addition of Negative Numbers Error
One of my co-workers found the KB article explaining the situation and
although I understand it now, I can't imagine the average user expecting to encounter this when dealing with monetary values that always consist of two decimal places. Thank you for your assistance http://support.microsoft.com/default.aspx/kb/78113 -- Tim "Gary''s Student" wrote: This is simple rounding error. Avoiding it with: "Precision as displayed" option is dangerous for thing like compound interest, etc. =ROUND() is better. -- Gary''s Student - gsnu200851 "tboggs13" wrote: That may help me work around the issue, but if Excel is unable to properly add a positve and a negative number, can I trust it to round properly? I have tried several different numbers, and it throws out some really interesting results to the right of the decimal. -- Tim "Gary''s Student" wrote: =ROUND(SUM(A2:D2),2) -- Gary''s Student - gsnu200851 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2007 SP2 Addition of Negative Numbers Error
I am an accountant and I am trying to prepare a txt or csv file for import.
I have 4600 lines for journal entries that have to balance to zero. I get an error message that says I cannot post because the journal entry is out of balance. It says 5,242,232.21 does not equal 5,242,232.21. When I total the journal entry it totals to 5,242,232.210000001 debit and 5,242,232.2100000002 credit. They do not balance. I have checked to the 20th place after the decimal and all the line have zeros after the the digits for cents for example 4.22000000000000000000 plus (4.22000000000000000000) equals (0.0000000000156897). All numbers display with values only in the two decimals for cents and then zeros to the 20th decimal but they do not balance to zero. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2007 SP2 Addition of Negative Numbers Error
Use the ROUND() function to make sure that only 2 decimals are used.
"Bzltyr" wrote in message ... I am an accountant and I am trying to prepare a txt or csv file for import. I have 4600 lines for journal entries that have to balance to zero. I get an error message that says I cannot post because the journal entry is out of balance. It says 5,242,232.21 does not equal 5,242,232.21. When I total the journal entry it totals to 5,242,232.210000001 debit and 5,242,232.2100000002 credit. They do not balance. I have checked to the 20th place after the decimal and all the line have zeros after the the digits for cents for example 4.22000000000000000000 plus (4.22000000000000000000) equals (0.0000000000156897). All numbers display with values only in the two decimals for cents and then zeros to the 20th decimal but they do not balance to zero. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2007 SP2 Addition of Negative Numbers Error
Thanks.
I have tried roundup(a1,2), rounddown(a1,2), mround, ceiling, changing the number to text and then back, =trunc(a1,2). I can look at the value in the cell and there are nothing but zeros from the third to the twentieth decimal point and still it does not calculate to exactly zero. Somtimes there are ten zeros after the total and the a number for example: 4.22000000000000000000+(4.22000000000000000)=(0.00 000000001268973) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel, change column of negative numbers to positive numbers? | New Users to Excel | |||
Excel 2002 : Convert Positive Numbers to Negative Numbers ? | Excel Discussion (Misc queries) | |||
Excel Addition Error | Excel Discussion (Misc queries) | |||
Excel Formula - Add column of numbers but ignore negative numbers | Excel Worksheet Functions | |||
Erf (the error function) for both negative and positive numbers | Excel Discussion (Misc queries) |