"Ross" wrote:
"Happy Cat" wrote:
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 -----

"Ross" wrote in message
...
Hello

Thanks for responding. 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?

Thanks
--
smither fan


"Happy Cat" wrote:

Go to Excel Options/ Advanced/ When Calculating This Workbook/ then tick
Set
Precision as Displayed.
--
Happy Cat


"Ross" wrote:

Good Afternoon, Everyone

I have a rounding problem.

My s.s. figures quarter payments with monthly pre-pays.

Excel is rounding the 2 monthly pre-pays from, for example, 8.625 to
8.63
ea., which is fine by me. That's the way we would obviously go when we
make
a payment, so I want it to show that. The problem arises when the 2
monthly
pre-pays are added together to get the pre-pay figure to use in the
quarterly
calculation on the sheet. Excel gives the accurate calculation of
17.25, but
I need it to show the 17.26 which we paid. Other than writing some
kind of
code, is there anyway to get around this, so that I'm not continually
taking
formulas out and doing hard inputs?

The pre-pay portion of my ss has these Totals formulas on row 17:

Col. E Col. F Col. G

=e15+e16 =f15+f16 =e17+f17

Thanks for your help,
--
smither fan