Stop rounding values
 

I have several values that have been set to show only 2 decimal places,
but the actual value has more. When I sum the two cells I get the wrong
result.

For example

A1 = 23.34656 displayed as 23.35
A2 = 1.1072 displayed as 1.11

When the two displayed values are added the answer is 24.46 but if you
enter the formula A1+A2 the result is 24.45

How do I stop Excel doing this?

Hi Keith

maybe
=ROUND(A1,2)+ROUND(A2,2)

or you could tick tools / options / calculations tab - precision as
displayed
from help:
"Precision as displayed Permanently changes stored values in cells from full
precision (15 digits) to whatever format, including decimal places, is
displayed."

Cheers
JulieD




"Keith" wrote in message
...
I have several values that have been set to show only 2 decimal places, but
the actual value has more. When I sum the two cells I get the wrong
result.

For example

A1 = 23.34656 displayed as 23.35
A2 = 1.1072 displayed as 1.11

When the two displayed values are added the answer is 24.46 but if you
enter the formula A1+A2 the result is 24.45

How do I stop Excel doing this?

Hi
use ROUND at your intermediate results. e.g.
=ROUND(A1,2)

--
Regards
Frank Kabel
Frankfurt, Germany

"Keith" schrieb im Newsbeitrag
...
I have several values that have been set to show only 2 decimal
places,
but the actual value has more. When I sum the two cells I get the
wrong
result.

For example

A1 = 23.34656 displayed as 23.35
A2 = 1.1072 displayed as 1.11

When the two displayed values are added the answer is 24.46 but if
you
enter the formula A1+A2 the result is 24.45

How do I stop Excel doing this?

Keith

You either

1) Need to change the setting in ToolsOptionsCalculationPrecision as
displayed (May not be desirable)
2) Wrap your formulae in the ROUND function

=ROUND(A1,2)+ROUND(A2,2)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"Keith" wrote in message
...
I have several values that have been set to show only 2 decimal places, but
the actual value has more. When I sum the two cells I get the wrong
result.

For example

A1 = 23.34656 displayed as 23.35
A2 = 1.1072 displayed as 1.11

When the two displayed values are added the answer is 24.46 but if you
enter the formula A1+A2 the result is 24.45

How do I stop Excel doing this?