Why do I get calculation errors with Athlon and Excel 2003?

R

Rick1459

I am running a simple calc on a new computer that I bought with an Athlon
3200+ processor. 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?

Rick1459
 
J

joeu2004

Rick1459 said:
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?
Click to expand...

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.
Click to expand...

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.
 
J

Jerry W. Lewis

Specifically, the binary approximation to 1/10 based on the IEEE
standard for double precision (used by Excel on all processors) is
equivalent to
0.1000000000000000055511151231257827021181583404541015625

Since the OP failed to mention the starting value, it is hard to say
much about the specific calculations; but if the OP needs exact
increments, then he should multiply everything by 10 so that the
increments will be 1 (integers are exactly representable) instead of
0.1, which must be approximated.

Jerry
 
S

sportsguy

i had this problem, where 0.35 - 0.34 <> 0.1

so i rounded to the 13th decimal place on all computations, and
all is fine. . .

however, most times, you don't need that many decimals, so round to th

desired level of accuracy. . .

sportsgu
 
J

Jerry W. Lewis

Rounding is a good way to handle that.

The issue is finite precision calculations involving numbers that
require infinite precision for exact representation. For example,
you would see the same issue in decimal with the VBA Currency data type
(4 decimal places) where
(2/3)-(1/3) = 0.6667-0.3333 = 0.0001
instead of zero.

In your specific case, the binary approximations are
0.34999999999999997779553950749686919152736663818359375
-0.340000000000000024424906541753443889319896697998046875
---------------------------------------------------------
0.009999999999999953370632965743425302207469940185546875
compared to the binary representation for 0.1, which is
0.1000000000000000020816681711721685132943093776702880859375

I posted VBA code in
http://groups.google.com/group/microsoft.public.excel/msg/6efb95785d1eaff5
which would give you 28 figures of the decimal equivalent to the binary
approximation of these numbers, but neither that nor full accuracy is
required to roughly predict the degree of approximation in the final
answer. Help for Excel documents Excel's limit of 15 digit accuracy, so
the problem can be viewed as
0.350000000000000???
-0.340000000000000???
---------------------
0.010000000000000???
so it would have been sufficient to round to 15 decimal places, but you
are right that that is often overkill. For example, money calculations
usually have no more than 2 decimal digits of input, and (with the
possible exception of interest calculations) results can usually be
rounded to 2 decimal digits.

Jerry
 
Top