Excel 2007 When subtracting 2-place (dollar)
amounts in a row, the difference is shown as an
unrounded 11-place decimal. Trying to format the
difference cell in that row to a 2-place presentation
has no effect. What am I missing ?

Thanks,

Rodan.

on 5/23/2011, Rodan supposed :
> Excel 2007 When subtracting 2-place (dollar)
> amounts in a row, the difference is shown as an
> unrounded 11-place decimal. Trying to format the
> difference cell in that row to a 2-place presentation
> has no effect. What am I missing ?
>
> Thanks,
>
> Rodan.

Try using the ROUND() function, and specify the desired decimal place
precision.

Example:

=ROUND(A1-B1, 2)

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

On May 23, 11:24*am, "Rodan" <Ro...@Verizon.NOT> wrote:
> When subtracting 2-place (dollar) amounts in a row,
> the difference is shown as an unrounded 11-place decimal.
> Trying to format the difference cell in that row to a
> 2-place presentation has no effect. *What am I missing ?

Nothing. This is a very common problem. For example, note that
=IF(10.1 - 10 = 0.1,TRUE) returns FALSE(!).

In general, one remedy is to explicitly round expressions that you
expect to be accurate to a specified precision. In my example,
=IF(ROUND(10.1-10,1)=0.1,TRUE) returns TRUE.

Changing the format alone usually only alters the __appearance__ of
values, not the __actual__ value itself. For example, if you enter
1.25 into A1 and format to display 1 decimal place, it will usually
__appear__ to be 1.3, but only =IF(A1=1.25,TRUE) returns TRUE.

I say "usually" because there is an alternative to using ROUND
explicitly pervasively: setting the "Precision as displayed"
calculation option (PAD).

However, I do not recommend PAD. First, it can change constants
irreparably if you are not careful to set cell formats before setting
PAD and before entering constants. Second, PAD is not a cure-all
because it performs its function only on the final result of a
formula. For example, setting PAD does not avoid the need for ROUND
if the 10.1-10 example above.

The root cause of the problem is: Excel, like most applications, uses
binary floating-point to represent numbers. Consequently, most non-
integers (as well as integers larger than 2^53) cannot be represented
exactly. Arithmetic operations exacerbate the problem.