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

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

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.100000000000000005551115123125782702118158340454 1015625

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

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.

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


--
sportsguy
------------------------------------------------------------------------
sportsguy's Profile: http://www.excelforum.com/member.php...o&userid=24771
View this thread: http://www.excelforum.com/showthread...hreadid=480386

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.349999999999999977795539507496869191527366638183 59375
-0.340000000000000024424906541753443889319896697998 046875
---------------------------------------------------------
0.009999999999999953370632965743425302207469940185 546875
compared to the binary representation for 0.1, which is
0.100000000000000002081668171172168513294309377670 2880859375

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