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.
