I have a worksheet where to formula gives a result in a monetary format,
however the calculations cause the answer to be rounded by. For example the
result maybe $2.456 which rounds up to $2.46. I want the amount to remain as
$2.45, in other words I don't want rounding to occur. Is there anyway I can
turn rounding off in certain cells?
--
Steven.

In God we trust, all others we virus scan.

elusiverunner wrote:

I have a worksheet where to formula gives a result in a monetary format,
however the calculations cause the answer to be rounded by. For example the
result maybe $2.456 which rounds up to $2.46. I want the amount to remain as
$2.45, in other words I don't want rounding to occur. Is there anyway I can
turn rounding off in certain cells?
--
Steven.

In God we trust, all others we virus scan.

Hi elusiverunner,

=TRUNC(2.456 , 2)

returns 2.45

Ken Johnson

Thanks for your reply Ken, but the cells that require the lack of rounding
already has a formula in it. Is there a way I can merge this with other
formula?
--
Steven.

In God we trust, all others we virus scan.


"Ken Johnson" wrote:


elusiverunner wrote:

I have a worksheet where to formula gives a result in a monetary format,
however the calculations cause the answer to be rounded by. For example the
result maybe $2.456 which rounds up to $2.46. I want the amount to remain as
$2.45, in other words I don't want rounding to occur. Is there anyway I can
turn rounding off in certain cells?
--
Steven.

In God we trust, all others we virus scan.

Hi elusiverunner,

=TRUNC(2.456 , 2)

returns 2.45

Ken Johnson

elusiverunner wrote:

Thanks for your reply Ken, but the cells that require the lack of rounding
already has a formula in it. Is there a way I can merge this with other
formula?

Hi Steven,

What is that formula?

Or try...

=TRUNC(original formula without the =, 2)

Ken Johnson

Sorry that it has taken so long for me to get back to you, Ken. I have tried
your suggestion and it works a treat. However, cells that do not have values
in have now got this - "#VALUE!" without the " were they were blank before
getting values inserted.

I have tried to manipulate the formula to try and get blank cells when there
is no data, but to no luck. The formula before the "TRUNC" statement looks
like this =IF(OR(D5=""),"",(D5/10))
--
Steven.

In God we trust, all others we virus scan.


"Ken Johnson" wrote:


elusiverunner wrote:

Thanks for your reply Ken, but the cells that require the lack of rounding
already has a formula in it. Is there a way I can merge this with other
formula?

Hi Steven,

What is that formula?

Or try...

=TRUNC(original formula without the =, 2)

Ken Johnson

elusiverunner wrote:
"Ken Johnson" wrote:
try...
=TRUNC(original formula without the =, 2)
[....]
I have tried to manipulate the formula to try and get blank cells when there
is no data, but to no luck. The formula before the "TRUNC" statement looks
like this =IF(OR(D5=""),"",(D5/10))

=if(D5="", "", trunc(d5/10, 2))