Ross said:
Happy Cat said:
Go to Excel Options/ Advanced/ When Calculating This Workbook/
then tick Set Precision as Displayed.
[....] I've read somewhere about the potential problem of instability if
you do this. Do you know if any problems would be confined to
the specific workbook, or would it affect all Excel files if I changed the
setting?
First, the option is limited to the specific workbook.
Second, I would not say that "Precision as displayed" is unstable. There
are some risks. But as I learn more about PAD, I would say that those risks
have been overstated, by me and others. As with all of Excel's features,
knowledge and understanding usually ameliorates the problems people complain
about.
The biggest risk -- a real one -- has to do with constants in the workbook.
Note: constants only, not values resulting from formulas. Moreover, the
risk applies only to cells with an explicit numeric format, not General (the
default).
Risk #1: Setting PAD affects all worksheets in the workbook, not just the
selected worksheet. Consequently, the changes mentioned below can happen
without your noticing.
Risk #2: If you already have constants in cells with an explicit numeric
format, when you set PAD, the underlying value of the cells will be forever
changed.
Consider your example: you have 8.625 in a cell, but it displays as 8.63.
You probably set the cell to a numeric format with 2 decimal places.
Despite what you see, the underlying value is truly 8.625. When you PAD,
the underlying value will become 8.63. You say that's what you think you
want now; but I wonder. The point is: if you change your mind later, the
PAD change will be irreversible, except by manual editing, of course.
Or suppose you are focusing on 8.625 when you decide to set PAD, but after
setting PAD, you realize it changed another cell that you wish it hadn't.
Again, the change irreversible except by manual editing.
Word to the wise: Make a copy of the workbook as a back-up before you set
PAD.
Risk #3: After you set PAD, you enter constant numeric data into existing
cells already have an explicit numeric format, unbeknownst to you. You
enter 8.625; you see 8.63; so you decide to change the format to 3 decimal
places. Too late! The underlying value is truly 8.63 now.
The other "risks" in using PAD are really anomalies, and all are reversible
or you can work around them.
The key thing to know is: PAD affects only the final result of a numeric
formula. It does not affect the precision of values used or referenced in
formulas; and it does not affect intermediate calculations. PAD should be
called "Precision of the result as the cell is formatted".
For example, assume you have 8.625 in A1 and A2 with a numeric format with 3
decimal places. Assume you have =A1+A2 in A3 with a numeric format with 2
decimal places. With PAD set, you might think that each 8.625 is converted
8.63, the precision of A3, then added to give you 17.26. No. The result is
17.25 because the exact values in A1 and A2 (8.625) are used, then the
result is rounded to the PAD for A3.
The good news with PAD is: its effect on the results of formulas is
reversible. If you simply change the format of A1 and A2, you will get
17.26. Or you could change the format of A3 to numeric with 1 decimal place
and get 17.3 in either case. Or you can disable PAD, and formulas will
resume their normal behavior.
Finally, PAD does not completely insulate you from other anomalies due to
the internal format used by Excel (binary floating-point). Most decimal
fractions, like "63" in 8.63, cannot be represented exactly. So complex
formulas can have surprising results that will still require you to use
ROUND or some other means to work around them.
For example, suppose PAD is set, and you have 0.5 in A1 and 0.4 in A2. In
A3, you have =IF(A1-A2-0.1=0,TRUE). It might surprise you that the result
is FALSE. The explanation is complex; it has been addressed in other
threads. But the point here is: PAD did not protect you.
So PAD is not the cure-all for all arithmetic anomalies in Excel. But it
does avoid your having to explicitly ROUND every numeric formula to the
number of decimal places in the cell format, and to change each final ROUND
when you decide to change the cell format.
Nonetheless, I still avoid PAD because of the risks with constants. I am
too prone to error, too quick to do something without thinking of the
consequences elsewhere in the workbook, and too dependent on undoing my own
mistakes manually or with ctrl-Z. The effect of PAD on constants is
irreversible.
But if you are more confident in yourself (arguably a misguided feeling),
perhaps you are less worried than I am, and PAD is the right thing for you
to use.
HTH.
----- original message -----
