Chris said:
I had to take it out to 16 places for the difference to show. Strange.
Actually not. This is a common problem. Just for fun, try: =IF(10.1 - 10
= 0.1, TRUE). It will return FALSE (!).
See
http://support.microsoft.com/kb/78113 for a long explanation. In a
nutshell: most numbers with decimal fractions cannot be represented exactly
as displayed because of the internal format used by Excel and most
applications. This causes "numerical aberrations" to arise in most
arithmetic operations. (I try to avoid the phrase "numerical error" because
this is not a defect.)
You might see these aberrations when you do things like =(A1-0.3020). The
parentheses are unneeded in this example. I use them habitually to avoid
Excel's half-baked attempt to correct these aberrations, which masks their
existence and effect in other contexts.
In your example, assuming you did not see the aberration when formatting to
14 decimal places, the difference was between -4.996E-16 and -5.44E-15.
If it makes sense in your application, you might consider using ROUND() at
the source of the aberration instead of simply in the ROUNDDOWN() formula.
The aberrant value displayed as 0.3020 might have a pervasive effect on
other dependent calculations. For example, if A1 is =A2*A3, change it to
=ROUND(A2*A3,4).
But the operative phrase is "makes sense". Sometimes, it is preferable to
retain the exact calculation in A1 and round only selective references.
That is one reason why I deprecate the use of an oft-mentioned alternative
to using ROUND() explicitly, namely setting the "Precision as displayed"
calculation option (Tools > Options > Calculation in Excel 2003). The PAD
option has a pervasive effect on all cells that are not formatted as
General.
Caveat: If you choose to experiment with PAD, be sure to copy your workbook
first. PAD has an irreversible effect on constants in cells that are not
formatted as General.
----- original message -----