Basic Calculation Error

[Guest]

Hi Folks,
One of my users came across a strange situation in which Excel (2000 SR1 and
2003 SP1) does not correctly calculate a basic sum.
As yet I am unable to find a pattern as to where the error may lie.
To replicate - open a worksheet and format the cells you wish to use as
number and decimal places to 16 or above.
In the first column type in 0.7, in the second column type in 0.68 and in
the third column use the basic formula of =A1-B1 and sit back and question
the result.
For some or other reason Excel reports 0.01999999999999990000 and not
0.02000000000000000000 (u get the idea).
This is a very scary error IMHO as how does one trust the figures being
generated. I cannot find anything on Microsofts website and intend to advise
them accordingly after receiving feedback here.
Go ahead and try it - I would appreciate comments and the like.
Regards, Kip

 

[Rob Bovey]

Hi Kip,

Chip Pearson has a good explanation of this phenomenon here:

http://www.cpearson.com/excel/rounding.htm

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

 

[Guest]

Interesting article Rob, appreciate the post, definitely makes things clearer
for me.
I will say that in my mind it still remains a scary thought that this is how
things work.