A few remarks in response to Jerry's excellent post.
<it is common to almost all computer software and hardware>
Not entirely true. There are (software) implementations of the COBOL and
other languages that do not suffer from this problem, because they use
either scaled integer representations internally or true binary (not
floating point) representations of numbers.
Both systems have the "disadvantage" of smaller possible min/max values
(although this theoretically is not necessary). But in commercial
applications they may be perfectly suitable (and even desirable!).
These software solutions are sometimes supported by Binary Coded Decimal
(BCD) hardware instructions in the computer, but that is not necessary to
implement decimal arithmetic.
And, almost any pocket or desk calculator will do this kind of calculation
correctly, although with limited capacity. So did all punced card tabulators
for many decades.
<digits beyond the 15th may not be what you expect >
We should be careful with descriptions like "digits beyond the 15th". First,
it's *decimal* digits, but most of us would assume that anyway. But some
manuals quite rightly mention "15 significant digits". That is because in a
number like
0.000000000000000123456789012345
no precision is lost.
<then you correctly get
0.069999999999999840127884453977458178997039794921875 Which displays in 15
digits as 0.0699999999999998>
I don't think that is right. It's not just a matter of displaying less
precision, there really *is* less precision, as can easily be seen by
applying some more calculation.
Then I think we should be careful using the frase "*the* IEE
representation". There are several IEE floating point representations and
there is a new IEE 754-2008 standard.
The last remark I would like to make is that we (including myself) often
confuse IEEE FP and binary, like in "can't be represented exactly binary".
Some numbers can be represented exactly using true binary, but not in IEEE,
using the same number of bits. That is because in FP some bits are used for
the exponent.
It is my conviction that, since Excel is widely (and, I think, mainly) used
for commercial applications, true decimal arithmetic should be available, at
least as an option.
--
Kind regards,
Niek Otten
Microsoft MVP - Excel
I am also convinced that the performance implications will be relatively
small.
