Unacceptable floating point errors

Errata....

I wrote:
Arguably, if 0.29 were represented by adding one more bit (2^-54), it
would be exactly 0.290000000000000,03552713678800500929355621337890 625,
about 0.000000000000000035 (18 fractional digits). In that case, 0.29 -
0.28 would be 0.0100000000000000,0888178419700125232338905334472 65625,
which Excel would display as 0.010...0 to 15 significant digits.

I had decided not to correct the several typos in my posting, most of which
are not material. But I do want to correct a potential misimpression, even
though no one has (yet) made a point of it, just for the record.

First, what I meant to write is: if I increment the least significant bit
by adding 2^-54, it would have the result above, which is about
0.000000000000000035 (18 fractional digits) more than 0.29.

But I do not mean to suggest that Excel is flawed for not doing so in its
conversion.

The issue, again, has to do with binary floating point arithmetic.

The string "0.29" is converted to a number by computing 2/10 + 9/100. That
happens to result in exactly
0.289999999999999,98001598555674718227237462997436 5234375, which is less
than 0.29.

I had noted previously that the string "0.28" happens to convert, by
computing 2/10 + 8/100, to the exact number
0.280000000000000,02664535259100375697016716003417 96875, which is more than
0.28.

The different directions in conversion -- one less, the other more -- is
partly responsible for the failure of the simplistic case of 0.29 - 0.28 not
appearing to be 0.01 when formatted to 16 decimal places (15 significant
digits).

That might raise the speculation that Excel should always convert to a
binary value greater than (if not equal to) the original numeric string.

But for this example, it is not a panacea.

Even though the result of 0.29 - 0.28 (in A3, say) might appear to be 0.01
when formatted to 16 decimal places, the formula =A3=0.01 returns FALSE (!).

The reason is: the exact result of (0.29 + 2^-54) - 0.28 is
0.0100000000000000,0888178419700125232338905334472 65625. That is different
enough from the internal representation of the constant 0.01 for Excel to
recognize the difference, despite its heuristics to adjust infinitesimal
differences. The exact internal representation of the constant 0.01 is
0.0100000000000000,0020816681711721685132943093776 702880859375.

Moreover, one might reasonably question the wisdom of trying to make this
specific example work better.

The reason is: there are 18 valid representations of each of 0.29 and 0.28,
any of which might be the result of an arithmetic expression. The 324
combinations of subtracting 0.28 from 0.29 result in any of 35 values from
about 0.00999999999999907 to about 0.01000000000000095. Only 18
combinations result in what appears to be 0.01 when formatted to 16 decimal
places. But =A3=0.01 returns FALSE for all of those combinations, since all
18 are exactly 0.0100000000000000,0888178419700125232338905334472 65625.


----- original message -----

"JoeU2004" wrote in message
...
"Jeff in GA" wrote:
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,98001598555674718227237462997436 5234375, and 0.28 is
exactly 0.280000000000000,02664535259100375697016716003417 96875 .

(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,03552713678800500929355621337890 625,
about 0.000000000000000035 (18 fractional digits). In that case, 0.29 -
0.28 would be 0.0100000000000000,0888178419700125232338905334472 65625,
which Excel would display as 0.010...0 to 15 significant digits. But
ironically, that representation of 0.29 is less accurate.


Elsewhere in the thread, you wrote:
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,98889776975374843459576368331909 1796875. 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,0888178419700125232338905334472 65625.

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

"Jeff in GA" wrote in message
...
This is NOT some calculation to the nth place or an atomic weight.

This is: .29 - .28

Instead of returning the correct answer of 0.01, Microsoft Excel returns
0.00999999999999995

This floating point excuse for can not be blamed for poor product
quality.

Does Microsoft ever plan to repair its product, or should it only be used
for calculating federal budget numbers?

Yes, you are quite correct, Jeff. If you are starting off with numbers with
a resolution of 0.01 (such as in many currency claculations), and if you
want precision, it would certainly make sense to multiply up and do the
calculation in cents (integers) rather than in dollars (floating point).
--
David Biddulph

"Jeff in GA" wrote in message
...
For prople looking for precise answers (e.g, where the orders of magnitude
are small) I wonder if it would make sense to de-decimalize the numbers,
compute, and then re-decimalize them.

Jeff
You can't have a greater degree of accuracy in a result than was present
in the arguments
..30 - .29 does = .01 to 2 significant digits, beyond that the result is
undefined. .01000000000 is just one of many valid ones

..01 is the 2sd representation of any number from .005 to less than .015.

If you have a need for such high precision then you probably need to use
specialist tools rather than a general purpose product like Excel.

cheers
Simon

Jeff in GA wrote:
David, I appreciate your commitment to the notion that there could not
possibly be a solution to the problem, and that we should all accept
incorrect mathematical results.

Meanwhile, even my fourth grade child knows that .30 - .29 = .0100000000000

But yes, David, let's adapt to the machines, and not make the machines adapt
to us.

Also: the customer is always wrong.

Happy now?

...Jeff

"David Biddulph" wrote:

Perhaps you could tell us what the exact binary representation of 0.29 would
be?
--
David Biddulph

"Jeff in GA" wrote in message
...
This is NOT some calculation to the nth place or an atomic weight.

This is: .29 - .28

Instead of returning the correct answer of 0.01, Microsoft Excel returns
0.00999999999999995

This floating point excuse for can not be blamed for poor product quality.

Does Microsoft ever plan to repair its product, or should it only be used
for calculating federal budget numbers?