What I actually have is a calculation: 1.0*0.10=0.10[.] Perhaps it's the multiplication that "adds" decimals?
It is important to provide examples that actually demonstrate the problem.
1*0.1 does
not cause any problems. But 1.
5*0.1 does.
In general,
any arithmetic (not just multiplication) with decimal fractions or that results in decimal fractions can present problems.
Excel
per se does not worry about the "faraway decimals". But we should.
That is why I wrote:
whenever you expect
a calculation to be accurate to some number of decimal places,
explicitly round to that number of decimal places.
I do
not recommend setting the Precision As Displayed option, for many reasons.
But if you want to experiment with it, be sure to
save a copy of the Excel file before setting the option. One of the dangers of setting PAD in an existing Excel file is: PAD might change some constants
permanently, and any adverse effect is
irreversible.
-----
Briefly, problems arise because most decimal fractions cannot be represented exactly in 64-bit binary floating-point, which is what Excel uses internally to represent numbers.
For example, 0.1 is actually 0.100000000000000,0055511151231257827021181583404541015625 .
(I use period for the decimal point and comma to demarcate the first 15 significant digits.)
Moreover, the approximation of a particular decimal fraction often depends on the magnitude of the number [1].
For example, 10.1 is actually 10.0999999999999,996447286321199499070644378662109375 . Consequently, IF(10.1 - 10 = 0.1, TRUE) returns FALSE(!) because 10.1 - 10 is actually 0.0999999999999996,447286321199499070644378662109375.
Confusion due to these "standard" side-effects is compounded by other behaviors that are
specific to Excel.
For example, Excel
displays (not "stores") only up to the first 15 significant digits (rounded). So, sometimes we cannot see the "faraway decimals".
For example, if A1 has the formula =1.5*0.1, that appears to be 0.150000000000000 (15 fractional digits). But it is actually 0.150000000000000,02220446049250313080847263336181640625 .
(The IEEE 754 standard requires displaying up to
17 significant digits in order to convert between binary and decimal representations with no loss of precision.)
And Excel has implemented some work-arounds in an attempt to hide some of the "standard" side-effects. But the work-arounds are
applied inconsistently.
For example, IF(A1 = 0.15, TRUE) returns TRUE because the "=" operator rounds both operands to 15 significant digits. But MATCH(0.15,A1,0) returns #N/A, indicating no match, because MATCH compares the exact binary values.
Setting PAD only affects the value stored in cells.
For example, if A1 is formatted as Number with 1 decimal place, =1.5*0.1 becomes the exact binary representation of 0.15. Thus, MATCH(0.15,A,0) would return 1, indicating a match.
But MATCH(1.5*10,A1,0) would return #N/A, indicating no match. And ironically, MATCH(1.5*10,A1,0) returns 1 when PAD is not set.
-----
[1] The approximation of a decimal fraction depends on the magnitude of the number because 64-bit binary floating-point represents numbers by the sum of 53 consecutive powers of two, the largest of which depends on the magnitude.
For example:
Rich (BB code):
0.1 = 1*2^(-4) + 1*2^(-5) + 0*2^(-6) +...+ 0*2^(-47) + 1*2^(-48) + 1*2^(-49) + 0*2^(-50) +...+ 0*2^(-54) + 1*2^(-55) + 0*2^(-56)
10.1 = 1*2^3 + 0*2^2 + 1*2^1 + 0*2^0 + 0*2^(-1) + 0*2^(-2) + 0*2^(-3) + 1*2^(-4) + 1*2^(-5) +...+ 0*2^(-47) + 1*2^(-48) + 1*2^(-49)
As you can see, 10.1 includes part of the approximation of 0.1 from 1*2^(-4) through 1*2^(-49). But we lose the part of the approximation that includes 1*2^(-52) through 1*2^(-55). That is, we lose precision.
[/IMG]
