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