Jeff in GA said:
This is: .29 - .28
Instead of returning the correct answer of 0.01, Microsoft Excel returns
0.00999999999999995
Excel does give you a tool for adjusting the result to your liking. Format
all cells with the number of desired decimal places, and set the calculation
option "Precision as displayed" (PAD). (In Excel 2003, click on Tools >
Options > Calculation.)
My preferred alternative is to use ROUND explicitly in most calculations.
This avoids some of the pitfalls of PAD.
Does Microsoft ever plan to repair its product
Ironically, it is the correctness of the calculation that is causing this
particular problem. I will explain the gory details below.
This floating point excuse for can not be blamed for poor product quality.
It is not "poor quality". It is one of several compromises that can be
made.
No matter what alternative you choose, you will always be faced with the
reality that there are no infinite resources in life. (At least, not until
we start building computer "bits" from individual atoms or "binary atoms".
Not truly infinite, but "uncountable".)
Some computers and some software add-ons provide "decimal arithmetic". That
helps to an extent, especially with simple arithmetic involving numbers with
a small number of non-repeating fractional digits. And I would agree: it
would be nice if Excel provide that as an option.
But even "decimal arithmetic" incurs problems with repeating decimal
fractions. For example, =1/3.
Some calculators, notably the MS Win calculator accessory, retain rational
numbers in their original form as a ratio of two integers, and they perform
some calculations by manipulating the ratios as we would on paper.
But even that strategy will fail as soon as we encounter a non-rational
number in the computation.
If you use a fixed-point or floating-point form with greater precision (more
bits), you are only deferring the problem. And there will still be a
potential problem with comparisons.
Excel does try to ameliorate such problems with an algorithm that attempts
to recognize and adjust "infinitesimal" differences. But the algorithm is
half-baked, leading to anomalies such as A1=A2 returns TRUE, but A1-A2=0
returns FALSE.
(Now for __that__ defect, I will join you in a chorus of complaints about
Microsoft's failure to correct flaws.)
Returning to your original problem....
This is: .29 - .28
Instead of returning the correct answer of 0.01, Microsoft Excel returns
0.00999999999999995
This is easy to duplicate in Excel 2003. I don't know what Peter's problem
is.
The cause of the problem is: 0.29 is represented internally exactly as
0.289999999999999,980015985556747182272374629974365234375, and 0.28 is
exactly 0.280000000000000,0266453525910037569701671600341796875 .
(The comma is my way of demarcating the first 15 significant digits, which
is all Excel will convert, rounding the 16th significant digit.)
Note that the representation of 0.29 is about 0.00000000000000002 (17
fractional digits) less than 0.29, and the representation of 0.28 about
0.00000000000000003 more than 0.28. So the difference is indeed
0.00000000000000005 less than 0.01.
The inexact representation of most decimal fractions is due to the fact that
binary floating point, the internal form, represents numbers by the sum of a
finite number of consecutive powers of 2. The operative word is "finite";
as noted above, there will always be some finite number digits.
Arguably, if 0.29 were represented by adding one more bit (2^-54), it would
be exactly 0.290000000000000,03552713678800500929355621337890625, about
0.000000000000000035 (18 fractional digits). In that case, 0.29 - 0.28
would be 0.0100000000000000,088817841970012523233890533447265625, which
Excel would display as 0.010...0 to 15 significant digits. But ironically,
that representation of 0.29 is less accurate.
What is strange about this problem (which as we all know is entirely
IEEE's
fault) is that it doesn't happen with other nearby number pairs, like
0.30 - 0.29.
Because 0.30 is represented internally exactly as
0.299999999999999,988897769753748434595763683319091796875. Note that is
less 0.30 by about the same amount that the representation of 0.29 is less
than 0.29. The difference is exactly
0.0100000000000000,088817841970012523233890533447265625.
If A1 is =0.30-029 and A2 is 0.01, =A1=A2 does result in TRUE. But A1 and
A2 are not truly equally, as evidenced by the fact that =A1-A2=0 results in
FALSE.
My point is: do not mislead yourself into thinking that any arithmetic with
decimal fractions is okay simply because you do not see the problem.
And just to reiterate, this is not "IEEE's fault". It is the fact that any
representation of decimal fractions in limited by finite resources, whether
that representation is binary or decimal. Arguably, decimal arithmetic
would mask some of the more flagrant examples.
I doubt that this will mollify your strong opinions. But I hope it gives
you a little insight and a modicum of understanding.
----- original message -----
)