Raj said:
d10,d11,d12 have the value 10.7 in them
Cell e10 has the value .7 directly input
Cell e11 has the value .7 derived by the formula =d11-int(d11)
Cell e12 has the value .7 dervied by the formula = e11-trunc(e11)
When values in cells e10,e11,e12 are tested for >=.7, cell f10 shows
true, f11 and f12 show false. Why?
Most decimal fractions cannot be represented exactly in the internal form
that Excel uses to represent numbers -- a standard binary floating-pointing
form. You are encountering two different approximations for 0.7.
10.7 is represented internally as
10.6999999999999,99289457264239899814128875732421875. (The comma is my way
of demarcating 15 significant digits to the left.) When you subtract 10,
you get 0.699999999999999,289457264239899814128875732421875.
But 0.7 is represented internally as
0.699999999999999,9555910790149937383830547332763671875.
You can see that they differ starting in the 16th significant digit, and the
first representation is indeed less than the second representation.
I will explain why we get two different approximations of 0.7 below.
But the important lesson here is: use ROUND prolifically when you are
dealing with numbers with decimal fractions. For example, compute
ROUND(D11-INT(D11),1), or compare ROUND(E11,1)>=0.7. I prefer the first
solution whenever feasible.
Note: Although formatting might change the appearance of numbers, it does
not change the underlying value. Formatting E11 as Number with 1 decimal
place is not the same computing ROUND(D11-INT(D11),1), even though they
might look the same.
Another alternative is to set the calculation option "Precision as
displayed" under Tools > Options > Calculation. But since that affects all
cells that do not use the General format, that option can lead to untoward
surprises. I do not use it.
So, why do we get two different approximations of 0.7?
In a nutshell, because numbers are represented internally by 53 consecutive
powers of 2 ("bits"). With 0.7, we can use the full 53 bits to represent
0.7. But with 10.7, some of the bits are used to represent 10; so there are
fewer bits to represent 0.7. In this case, that results in a less accurate
representation of 0.7. When we subtract 10, we are left with the less
accurate representation of 0.7.
All of this seems mysterious and difficult to predict, for a number of
complicated reasons. For example, you would encounter no problem with your
original formulation if D11 contained 1.7. By coincidence, the internal
representation of 1.7 has the same approximation of 0.7 as 0.7 itself.
But I reiterate: that is coincidence.
Again, use ROUND prolifically to avoid most surprises. (But unfortunately
not all!)
