All versions of Excel give -0.157000005245209 as the answer for
=209951201.868-209951202.025
(I have assumed the obvious typo in your original post) For you to not get
the trailing ...5245209 in Excel 2000 indicates that rather than entering
these values, you calculated at least one of them in a formula, and that
formula value is not the same as the corresponding 2003 value beyond the 15th
significant figure.
As documented, Excel will display no more than 15 significant figures. When
you request more, you get a displayed value padded with trailing zeros that
have no relationship to the actual value stored. You can use the VBA
functions at
http://groups.google.com/group/microsoft.public.excel/msg/6efb95785d1eaff5
to see more figures.
The arithmetic leading to the Excel 2003 answer is actually quite correct,
though potentially surprising. Almost all computer software and hardware do
binary math with finite precision. Most terminating decimal fractions
(including .828 and .025) are non-terminating binary fractions that can only
be approximated in binary (just as 1/3 can only be approximated as a decimal
fraction). The result is that you got the exact answer to an approximation
to your intended problem.
In general, you should realize that floating point numbers often have values
other than what you intended beyond the 15th significant figure. You cant
see these approximations directly (15 digit display limit) but they may be
revealed as a result of subtraction of numbers that agree at the first few
figures. You are the only person who knows what calculations you will do,
and therefore you are the only person who can determine what adjustments will
protect you from these unavoidable consequences of finite precision.
You subtracted numbers that agreed to 8 figures, so the result may have no
more than 7 (15-8) figures that agree with your intended problem, as opposed
to the approximate problem that finite precision forced Excel to use. For
simple addition and subtraction, rounding the result to the appropriate
figures will move finite precision approximations back out beyond the 15
digit display limit, without violence to the calculation.
Jerry
