View Single Post
 
Old 06-28-2017, 01:16 PM
joeu2004 joeu2004 is offline Windows 7 32bit Office 2007
Advanced Beginner
 
Join Date: Aug 2016
Posts: 32
joeu2004 is on a distinguished road
Default

Quote:
Originally Posted by joeu2004 View Post
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.
Reply With Quote