Rounding / Decimal Places

[=?ISO-8859-1?Q?Tom=E1s?=]

Hello, I have a problem which I can't solve:

I have a number in a cell with 6 decimal places. For some calculation
issues I can't round that number. But I want to show the number with
only 2 decimal places, so I set it in the cell format.
The problem is that I need to use the number in a formula "as it is
showed", that is, if it is showed with 2 deimal places then I need the
number rounded to 2 decimal places. If I change the format to more
decimal places, I want the number to change in the formula to the
corresponding decimal places.

Is there a way to "read the number as it is showed"?

I hope the explanation wasn't too confusing.

Any help will be appreciated,
Thanks,

Tomás

 

[Ken Wright]

So in the formula that you need it rounded, simply round it using the ROUND function, eg:-

=ROUND(A1,2)

This will round it just in that formula, leaving the underlying data alone for other formulas that
access that cell and need all the dps

 

[=?ISO-8859-1?Q?Tom=E1s?=]

Thanks for your reply, Ken.

Yes, that's the first thig I thought. Unfortunately, the circumstance
doesn't allow me to hard-code the number of decimal places, as in:
=ROUND(A1,2)

My problem is that I can't have the numbers printed on paper with less
significant figures than used to calculate results.

Let me give an example:
I have the following numbers:
0,567
0,678
0,789
0,118
------
Total: 2,152

But I format the cells to show them as:
0,57
0,68
0,79
0,12
------
Total: 2,15
However, if you add these last numbers by hand, the result should be
2,16.
What I need to be shown as the result is 2,16.

Regards,
Tomás

 

[Peo Sjoblom]

Try tools>options>calculation and select precision as displayed

--

Regards,

Peo Sjoblom

Thanks for your reply, Ken.

Yes, that's the first thig I thought. Unfortunately, the circumstance
doesn't allow me to hard-code the number of decimal places, as in:
=ROUND(A1,2)

My problem is that I can't have the numbers printed on paper with less
significant figures than used to calculate results.

Let me give an example:
I have the following numbers:
0,567
0,678
0,789
0,118
------
Total: 2,152

But I format the cells to show them as:
0,57
0,68
0,79
0,12
------
Total: 2,15
However, if you add these last numbers by hand, the result should be
2,16.
What I need to be shown as the result is 2,16.

Regards,
Tomás


"Ken Wright" <[email protected]> wrote in message

--

 

[Greg Lovern]

Hi Tomás,

If I'm reading your question correctly, some dependent cells need to
use the unrounded number, while other dependent cells need to use the
number rounded to its display value.

If that's correct, I think you'd have to write a custom function that
read the operand's cell formatting, and rounded the operand's value
accordingly.

(Turning on the 'Precision As Displayed' setting would only work for
the dependent cells that need to use the number rounded to it's
display value; it wouldn't work for the dependent cells that need to
use the unrounded value.)


Hope this helps,

Greg Lovern
http://PrecisionCalc.com
Get Your Numbers Right

 

[=?ISO-8859-1?Q?Tom=E1s?=]

Yes!! Thank you all!!
The 'precision as displayed' option was what i was looking for.
It works because what i wanted to change was derived data, not raw data.

Thanks Ken, Peo and Greg for your help,
Tomás.