BabyMc said:
How does one easily insert the round function around the lookup - when I
have many forumlas to do and the lookup is using many different filenames
(which, I think, prevents me from using edit replace)?
As JLatham suggested, at this point, your "easiest" solution might be to set
the calculation option "Precision as displayed". In Excel 2003, click on
Tools > Options > Calculation.
But do be careful. That option affects all cells in the workbook that have
a non-General numeric format, and the effect is non-reversible for such
cells with constants. For example, if you have the constant 0.125 in a cell
formatted as Number with 2 decimal places, setting PAD will change the
constant to 0.13, which will affect calculations in other dependent cells.
Moreover, note that PAD does not affect calculations in cells formatted as
General, the default. So PAD might seem to have no or less benefit
sometimes.
The alternative, using ROUND prolifically, is better, in my opinion. But in
order to do add this after the fact, the "easiest" solution might require
writing a macro. The macro itself may be non-trivial to design, depending
on how robust it needs to be. And if you are unfamiliar with macros, that
approach might be infeasible.
I don't understand why one would need to put the round function around a
lookup where the results are not to more decimal places than required.
Perhaps the following examples and explanation will help.
Suppose A1 contains 10.7, A2 contains =A1-10, and A3 contains 0.7. If you
do a comparison of A2 and A3, or look up A3 in a table containing A2, they
will not match exactly. In this case, A2 is slightly less than A3, so a
typical VLOOKUP might still work. But in other examples, A2 might be
slightly larger than A3.
In contrast, suppose A1 contains 1.7, and A2 contains =A1-1. In this case,
a comparison of A2 and A3 is an exact match.
Why the difference?
Most decimal fractions cannot be represented exactly in the internal form
that Excel uses to represent numbers -- a standard binary floating-pointing
form. We are encountering 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 we subtract 10, we
get .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.
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.
In contrast, the internal representation of 1.7 has the same approximation
of 0.7 as 0.7 itself because fewer bits are needed to represent 1. That is,
1.7 is represented internally as
1.69999999999999,99555910790149937383830547332763671875. So when we
subtract 1, we get the same respresentation of 0.7.
All of this seems mysterious and difficult to predict, for a number of
complicated reasons. Besides the anomaly demonstrated above, Excel employs
some heuristics (algorithms) to try to hide such differences by "correcting"
the result of some calculations. But the heuristics are implemented
inconsistently, sometimes resulting in even more mysterious behavior. For
example, using different numbers, the expression IF(A2>A1,A2-A1,0) might
result in a negative result very close to zero (e.g. about -2E-16).
I hope this helps. In my opinion, the best solution is to be aware of this
ever-present problem with arithmetic involving numbers with decimal
fractions and, with that foresight, to use ROUND prolifically from the
start.
----- original message -----
