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

[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

[[email protected]]

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

[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

[email protected] wrote:

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

[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 the
desired level of accuracy. . .

sportsguy

[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/micro...fb95785d1eaff5
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

sportsguy wrote:

> 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 the
>
> desired level of accuracy. . .
>
> sportsguy