View Single Post
  #2   Report Post  
 
Posts: n/a
Default Why do I get calculation errors with Athlon and Excel 2003?

"Rick1459" wrote:
The calc adds either .1 or .2 to the previous result depending on a condition.
After the calc runs on the first 15 or so rows, instead of adding a .1 it adds
a .100000000000001 which, although small, creates an error and later "IF"
statements fail a test that should be true.
Does anyone know of a problem with Excel 2003 and the Athlon 64 3200+
processor?


Niether. This is a normal artifact of the way that (binary) computers
do arithmetic. Except for numbers that can be represented accurately
as the sum of powers of 2 (and 1/2), non-integral numbers are stored
only approximately. Specifically, they are stored as a sum of a finite
number of powers of 1/2 (multiplied by a power of 2). This results in
computation errors of the type that you described.

(By the way, that also applies to integral numbers that exceed the
maximum value that can be represented exactly in the computer.)

although small, creates an error and later "IF" statements fail a
test that should be true.


For that reason, you should not test the result of non-integral
computations for equality. Instead of =IF(A1 = A2,...,...), you
should write =IF(ABS(A1-A2) < 1E-6,...,...). I chose "1E-6"
arbitrarily. Use a fudge factor that is tailored to your tolerance
for computational error.