a1=38641972,52
a2=-38416033,72
a3=(=a1+a2)=225938,800000004
a4=225938,8
a5=(=a3-a4)=4,48198989033699E-09
Not a defect, but a very common artifact of the way that Excel stores
numbers and performs calculation called 64-bit binary floating-point.
Not limited to Excel 2007; common in all versions of Excel. (I use
Excel 2003.) Also common in most applications, although some
applications try to avoid it by using some form of decimal arithmetic.
For example, IF(10.1-10=0.1,TRUE) returns FALSE!
Generally, the necessary work-around is to explicitly round the result
of all formulas that are intended to be "exact" to some precision.
For example, round to 2 decimal places when an "exact" dollars-and-
cents result is expected. So A3 and A5 should be:
=ROUND(A1+A2,2)
=ROUND(A3-A4,2)
Similarly, IF(ROUND(10.1-10,2)=0.1,TRUE) returns TRUE.
In short, most numbers with decimal fractions cannot be represented
exactly internally. Instead, they are approximated by the sum of 53
consecutive powers of 2. See
http://support.microsoft.com/kb/78113
for details.
Consequently, the binary representation of the result of some
calculation is not always the same binary representation of the
equivalent constant. In your example:
38,641,972.52 is exactly 38641972.5200000,03278255462646484375
-38,416,033.72 is exactly -38416033.7199999,988079071044921875
A1+A2 is exactly 225938.800000004,470348358154296875
But 225938.80 is exactly 225938.799999999,9883584678173065185546875.
The infinitesimal differences cause an infinitesimal difference in the
result. Note that usually, formatting alone cannot correct the
problem because formatting affects only the appearance of a value, not
the actual value. (But see the PAD note below.)
Note: Some people suggest setting the Precision As Displayed (PAD)
calculation option as an alternative to explicit rounding. I
deprecate its use for several reasons. Chief among them is the fact
that PAD is error-prone: if you are not careful, it can change
constants undersirably and irreversibly. If you choose to experiment
with PAD, it would be prudent to make a back-up copy of the Excel file
first.
