Precision as Displayed

[Jamie A Miller]

I am having problems with the calculations in Excel. My formulas are based
on cells with formulas. When I sum up my final results, the values do not
add up to the amount if added on a calculator. I know why this is doing
this, and I know there is a Precision as Displayed option. My question is,
if I change the formatting on the cells after a formula has been updated,
will the formula re-update because of the formatting change? I do not want
to change all my workbooks with out first knowing. Also, from what I
understand, this change is an Excel change, not just a workbook change; i.e.
all my workbooks will be changed if I want to change one workbook to this
option. Is this correct?

 

[Jim Rech]

Precision as Displayed is a workbook level setting.

Lets say you have Precision set on and you have a formula that evaluates to
2.4 because you've formatted the cell to display 1 decimal place. Then you
change the format to zero decimal places. The value of that cell/formula
will change to 2 with the next sheet calculate. If you have automatic calc
mode set that will be immediately. If manual then with the next F9.

--
Jim Rech
Excel MVP
|I am having problems with the calculations in Excel. My formulas are based
| on cells with formulas. When I sum up my final results, the values do not
| add up to the amount if added on a calculator. I know why this is doing
| this, and I know there is a Precision as Displayed option. My question
is,
| if I change the formatting on the cells after a formula has been updated,
| will the formula re-update because of the formatting change? I do not
want
| to change all my workbooks with out first knowing. Also, from what I
| understand, this change is an Excel change, not just a workbook change;
i.e.
| all my workbooks will be changed if I want to change one workbook to this
| option. Is this correct?

 

[Angel160]

Jamie,

We have used the Precision as Displayed option and we had to click on
popup before it was implemented so there's no danger of using it withou
knowing.

Also, it has only changed on a workbook by workbook basis and no
throughout the whole of Excel.

Hope this helps a little

 

[swatsp0p]

Note, however, that any calculations done on this cell will use the entered
value, not the dispayed value...e.g. 2.4*3=7.2, therefore, even if you
display [2] in cell A1, a formula in B1 such as A1*3 will return 7.2 (or [7]
if formatted to no decimals), not the expected [6] (2*3).

To get around this, use 'round' in your formula, e.g.
=(ROUND(A1,0))*3

try this and change 2.4 to 2.9 to see what happens. Depending on your
desired outcome, you may want to use 'rounddown' to effectively strip the
decimal entirely, e.g. 2.9 becomes 2.

HTH

Bruce

 

[Jim Rech]

Note, however, that any calculations done on this cell will use the
Huh? That's exactly backward. After a Calc the value of the cell will be 2
and formulas referring to it will pick up 2 not 2.4.

No. The purpose of using Precision is precisely<g> to avoid having to use a
bevy of ROUNDs.
--
Jim Rech
Excel MVP
| Note, however, that any calculations done on this cell will use the
entered
| value, not the dispayed value...e.g. 2.4*3=7.2, therefore, even if you
| display [2] in cell A1, a formula in B1 such as A1*3 will return 7.2 (or
[7]
| if formatted to no decimals), not the expected [6] (2*3).
|
| To get around this, use 'round' in your formula, e.g.
| =(ROUND(A1,0))*3
|
| try this and change 2.4 to 2.9 to see what happens. Depending on your
| desired outcome, you may want to use 'rounddown' to effectively strip the
| decimal entirely, e.g. 2.9 becomes 2.
|
| HTH
|
| Bruce
|
| "Jim Rech" wrote:
|
| > Precision as Displayed is a workbook level setting.
| >
| > Lets say you have Precision set on and you have a formula that evaluates
to
| > 2.4 because you've formatted the cell to display 1 decimal place. Then
you
| > change the format to zero decimal places. The value of that
cell/formula
| > will change to 2 with the next sheet calculate. If you have automatic
calc
| > mode set that will be immediately. If manual then with the next F9.
| >
| > --
| > Jim Rech
| > Excel MVP
| > | > |I am having problems with the calculations in Excel. My formulas are
based
| > | on cells with formulas. When I sum up my final results, the values do
not
| > | add up to the amount if added on a calculator. I know why this is
doing
| > | this, and I know there is a Precision as Displayed option. My
question
| > is,
| > | if I change the formatting on the cells after a formula has been
updated,
| > | will the formula re-update because of the formatting change? I do not
| > want
| > | to change all my workbooks with out first knowing. Also, from what I
| > | understand, this change is an Excel change, not just a workbook
change;
| > i.e.
| > | all my workbooks will be changed if I want to change one workbook to
this
| > | option. Is this correct?
| >
| >
| >