Quote:
Originally Posted by joeu2004
For all of these examples, the remedy is the same: explicitly round to the precision that you expect calculations to be accurate to. That means: use the ROUND function.
|
PS.... Arguably, that is not always easy to do. For example, if you use pivot tables, calculations are not rounded by default. I'm not a PT person. But I believe the only work-around is to create "calculated fields" (?) instead of the normal PT set-up.
-----
Another work-around that I
do not usually recommend is: set the "Precision As Displayed" Advanced Option.
If you want to experiment with that, be sure to
make a backup copy of the Excel file first.
PAD is
very dangerous; setting it can cause immediate and
irreversible changes to constants throughout the Excel file.
Moreover, setting PAD does not fix all rounding problems. It does not fix my earlier example, for instance: IF(10.01 - 10 = 0.01, TRUE).
But just trying it, then
throwing away the Excel file with PAD set, might give you confidence in the need to
explicitly round formulas and expressions
selectively.