0.95 has no exact binary representation (much as 1/3 has no exact
decimal representation). When you enter 179.95, computers (which do
binary math) see not 179.95, but the closest binary approximation that
can fit within the computer's precision.
179.94999999999998863131622783839702606201171875
is the decimal representation of the closest binary approximation in
IEEE double precision (used by Excel, and almost all other hardware and
software). What Excel gives when you subtract 179 is the exact answer
to this approximate problem.
The simplest way to think about these issues is to note that IEEE double
precision can approximate all 15 figure numbers, but only some 16 figure
numbers, hence Help's "specification" that Excel's accuracy is 15
figures. Thus you can think of your problem as
179.950000000000???
-179
-------------------
0.950000000000???
which is consistent with what you got.
0.949999999999989
Integers (<=15 figures) can be exactly represented, but most fractional
values cannot. You will have to either recast your calculations as
strictly integer calculations, or adjust your algorithm to accommodate
15 figure approximations to the numbers that you intend.
In this instance, you input nothing beyond 2 decimal places, and
subtraction cannot result in anything beyond that. Thus
=ROUND(179.95-179,2) will hide the evidence of binary approximations
without violence to the integrity of the calculation. I say "hide" the
evidence, because 0.95 will still be approximated, as revealed by
=0.95-(1-2^-4+2^-7).
Jerry
Excel MVP
Hi
If you type in say 179.95 then in the next cell 179 and subtract the
latter from the former you get 0.95. Actually, you've got
0.949999999999989. Maybe this is for statistical purposes (although
this should just apply to relevant functions), but this is a problem,
