"Joe User" wrote:
the Excel implementation is half-baked. It leads
to even more confusion
Although I do not like this Excel heuristic, I do not want to leave you with
the impression that Excel has a lock on confusing behaviors. The operative
word is "more" confusion.
For example, because of the nature of 64-bit floating bit arithmetic, the
order of operations can lead to different results. For example, 10.1 - 10 -
0.1 results in an infinitesimal number (about -3.6E-16), whereas 10.1 - 0.1 -
10 results in exactly zero.
All the more reason to rely on some form of explicit rounding, be it the
ROUND function (my preference) or the Precision As Displayed calculation
option.
The advantage of PAD is that it is pervasive; no need to for you to remember
to use ROUND.
The disadvantage of PAD is that is pervasive ;-). It might apply to
formulas where you prefer not to round. For example, typically I do not
round intermediate amortization schedule formulas; otherwise, the
quantization error results in apparent errors. I only round the actual
payment, since that is constrained by real-world requirements. (You can pay
fractional cents.)
And note that PAD can have deleterious and irreversible effects on
constants, e.g. periodic interest rates, that you might want to display with
limited decimal places, but you want the value to remain "exact".
(That is, as "exact" as 64-bit floating point permits.)
----- original message -----
"Joe User" wrote:
"Ashish_Vaidya" wrote:
when I subtracted two numbers [...], why would I
receive an answer that comes back as
x,xxx.xx4999999999900000000000000000?
FYI, it does not make sense to format more 15 "significant digits" -- that
is, 15 digits starting with the first non-zero digit on the left.
Has anyone else run into this issue?
How did you fix it?
Yes, this is very common. "Everyone" runs into this issue at some point.
The short answer is: always explicitly round expressions that involve
values that might have decimal fractions. Typically, use the ROUND function.
For example, IF(10.1-10=0.1,TRUE) returns FALSE(!). But
IF(ROUND(10.1-10),1)=0.1,TRUE) return TRUE, as expected.
(Alternatively, you might use the Precision As Displayed calculation. But
that can result in surprising results. If you choose to try PAD, be sure to
make a backup copy of your Excel file first. However, note that PAD will not
help in the example above.)
The longer answer can become quite involved. In short, Excel stores numbers
and performs arithmetic using a standard form called (IEEE 754) 64-bit
floating point. For overwhelming details, see
http://support.microsoft.com/kb/78113.
But the point to note is: most decimal fractions cannot be represented
exactly.
Instead, they are approximated by the sum of 53 consecutive powers of two
("bits"). Sometimes this results in different representations for the same
decimal fraction. For example, 10.1 is represented internally as exactly
10.0999999999999,996447286321199499070644378662109 375, whereas 0.1 is exactly
0.100000000000000,00555111512312578270211815834045 41015625.
The whole thing is complicatd by the fact that Excel has implemented some
heuristics to try to mitigate the effects of these differences.
Unfortunately, the Excel implementation is half-baked. It leads to even more
confusion when, for example, subtractiing some numbers results in exactly
zero, but subtracting other "similar" numbers does not. Moreover, the
half-baked implementation can have "impossible" results; for example
IF(A1=A2,TRUE) might return TRUE, but IF(A1-A2=0,TRUE) might not.
----- original message -----
"Ashish_Vaidya" wrote:
We have a spreadsheet that subtracts one number from another. We did not
understand why Excel is rounding the way it did so we decided to add decimal
places until we see the value change. For instance, one of our number was
going out to x,xxx.499999999999999999999999999999900000 while the other
number was x,xxx.5000000000000000000000000000000000. When we rounded the
numbers, one rounded up while the other rounded down. This is fine. Now
that we fixed the formula to TRUNC instead of ROUND, I thought the issue
would go away but now when I subtracted two numbers that were like
x,xxx.xx000000000000000000000000 and another number that is also
x,xxx.xx000000000000000000000000, why would I receive an answer that comes
back as x,xxx.xx4999999999900000000000000000? This doesn't make sense to me.
Has anyone else run into this issue? How did you fix it?
--
Ashish