Excel anomaly?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mark - 22 Nov 2005 06:16 GMT

Working with some data I came across some weird results:

Starting with a value of 33.665, I subtracted 33.95, which gave me a value
of -0.285. To normalize this I added 0.285 which resulted in a value of
-3.7E-15????? I began checking the values and found that after I subtracted
the 33.95, excel placed a 4 in the 15th decimal place. It is not in any of
the preceeding values. Why is it doing this and how can I make it stop?

Signature

Mark

Reply to this Message

Jerry W. Lewis - 22 Nov 2005 11:24 GMT

Excel (and almost all other general purpose software) does binary math.
Most terminating decimal fractions (including .665, .95, and .285) are
nonterminating binary fractions which can only be approximated in finite
precision (just as 1/3 can only be approximated as a finite precision
decimal fraction). When you do math on approximate inputs, it should
not be surprising when the outputs are only approximate.

Your options are:

- do integer math (all integers with <=15 digits can be exactly
represented, so =33665-33950+285 returns zero as you expect

- test whether results are nearly zero instead of exactly zero

- round results to an appropriate number of decimal places to hide
binary residue beyond Excel's documented limit of 15 decimal digits.

More Detail:

The precision of Excel's binary approximation is defined by the IEEE
standard for double precison
http://www.cpearson.com/excel/rounding.htm
so its results are comparable to almost all other general purpose
software. The IEEE standard approximations for your inputs (converted
back to decimal) are
33.66499999999999914734871708787977695465087890625
-33.9500000000000028421709430404007434844970703125
+ 0.284999999999999975575093458246556110680103302001953125
----------------------------------------------------------
-0.000000000000003719247132494274410419166088104248046875
which Excel correctly reports to its documented 15 digit limit as
-0.00000000000000371924713249427

In this instance, it is obvious that you can round to 3 digits without
reducing the precision of the results. In more complicated problems,
you can use that documented 15 digit limit as a rough guide for where to
round. Think of your problem as
33.6650000000000????
-33.9500000000000????
+ 0.284999999999999??
---------------------
0.0000000000000????

It is not clear how this is making Excel crash (the topic of this
newsgroup).

Jerry

> Working with some data I came across some weird results:
>
[quoted text clipped - 3 lines]
> the 33.95, excel placed a 4 in the 15th decimal place. It is not in any of
> the preceeding values. Why is it doing this and how can I make it stop?

Reply to this Message

Greg Lovern - 22 Nov 2005 23:21 GMT

> Working with some data I came across some weird results:
>
[quoted text clipped - 5 lines]
> of
> the preceeding values. Why is it doing this and how can I make it stop?

Hi Mark,

That's a binary conversion error. Excel converts your base 10 numbers to
binary (base 2) before sending them to the processor to do the arithmetic,
then converts the processor's result from binary to base 10. Both
conversions may be approximate.

You can avoid binary conversion errors with my Excel add-in, xlPrecison 2.0.
The free edition can be downloaded from here:

http://PrecisionCalc.com

Good Luck,

Greg

Reply to this Message