just terrible excel calculating error, please confirm if you have same result

[Alex Zilberberg]

Hello!

I spent many hours trying to understand what's going wrong with one complex
financial worksheet. I found very, very strange Excel behaviour. Try to do
this:

1. Open new excel workbook
2. Type "99.9" (without ") in A1 cell
3. Type "3" in A2 cell
4. Type "=A1/A2" in A3 cell
5. Type "=(ROUND(A3,1)-A3)" in A4 cell

I have result in A4 which is NOT ZERO, and it's not possible result! Because
99.9 / 3 IS EXACTLY 33.3 and 33.3 minus 33.3 IS EXACTLY ZERO!

If I enter "=ROUND(A3,1)-A3" result IS zero.
If I enter "=(ROUND(A3,1)-A3)" result is NEVER zero. Rusult is:

-7.1054273576010E-15


If I enter for example "=INT(ROUND(A3,1)-A3)" result is never correct.

Can anybody confirm or better explain this?

Alex

 

[Bucky]

I spent many hours trying to understand what's going wrong with one complex
financial worksheet. I found very, very strange Excel behaviour.

It's due to floating point arithemetic, an inherent issue with
computers.
http://www.google.com/search?q=excel+floating+point

I'm not exactly sure why "=round(...)" is different than
"=(round(...))", but somehow Excel treats it differently.

 

[Stephen Bye]

Tenths cannot be represented exactly in binary, in the same way that you
can't write down the result of dividing 1 by 3 without running out of pens
and paper. For an explanation of this see
http://support.microsoft.com/default.aspx?scid=kb;en-us;78113

When the last operation in an expression is addition or subtraction and the
result is very close to zero, Excel will set it to zero. In your step 5,
adding an extra (and unnecessary) layer of parentheses around the expression
means that the subtraction is now the second last operation, and so the
result is not set to zero.

 

[Bucky]

When the last operation in an expression is addition or subtraction and the
result is very close to zero, Excel will set it to zero.

Ah, yes, that explains it precisely. As for the original post, he was
using the parentheses for INT(ROUND(...)), but he removed the INT for a
simpler comparison.

 

[Saruman]

The first bracket of the formula is before the formula function, either
another formula function is missing or you are using too many parenthesis.


=(ROUND(A3,1)-A3)
=ROUND(A3,1)-A3

See the difference?

 

[glenton (glenton]

I'm surprised that this is the first time you've noticed this error. I've
found it to be quite common in large financial models, whenever you subtract
something down to 0. As a result it's very common to include
=IF(A1<0.01,0,A1) type of formulas in key places.

All the best
Glenton
Leviqqio Consulting

 

[joeu2004]

For an explanation of this see
http://support.microsoft.com/default.aspx?scid=kb;en-us;78113
When the last operation in an expression is addition or subtraction and the
result is very close to zero, Excel will set it to zero.

Fascinating! Does anyone know what Excel (2003) considers "close to
zero"?

 

[Bernie Deitrick]

Alex,

Starting with XL97, MS added code to round off small floating point errors
in XL arithmetic. The code is triggered under certain circumstances - such as

=ROUND(A3,1)-A3

and it is *not* triggered under other circumstances - such as
=(ROUND(A3,1)-A3)

See

Floating-point arithmetic may give inaccurate results in Excel
http://support.microsoft.com/kb/78113/EN-US/

particularly the example labeled
Example When a Value Reaches Zero

HTH,
Bernie
MS Excel MVP