Excel 2007 SP2 Addition of Negative Numbers Error

[tboggs13]

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

 

[Gary''s Student]

=ROUND(SUM(A22),2)

 

[tboggs13]

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.

 

[Gary''s Student]

This is simple rounding error. Avoiding it with:

"Precision as displayed"

option is dangerous for thing like compound interest, etc.



=ROUND() is better.

 

[tboggs13]

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

 

[Bzltyr]

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.

 

[Niek Otten]

Use the ROUND() function to make sure that only 2 decimals are used.

 

[Bzltyr]

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.00000000001268973)