Strange calculation

  • Thread starter Thread starter Nate
  • Start date Start date
N

Nate

I don't know if anyone has experienced this before but I just
subtracted two numbers (35.7 from 36.4) and the anwser, when pasted
into another cell as values, returned .69999999999...
I even tried on another computer with the same results. Has anyone
seen this before?

I also found similar results when adding 12.26,-31.82,3.02,17.47. The
anwser is .929999999...
 
Nate wrote...
I don't know if anyone has experienced this before but I just
subtracted two numbers (35.7 from 36.4) and the anwser, when pasted
into another cell as values, returned .69999999999...
I even tried on another computer with the same results. Has anyone
seen this before?
Click to expand...
....

Floating point rounding error. It's been around as long as digital
computers have been used for calculations. This is no different in
Excel than it is in any other piece of software that used IEEE double
precision floating point math.

If you want numbers rounded to one or two decimal places, safest to
round them explcictly, e.g.,

=ROUND(36.4-35.7,1)

or

=ROUND(36.4-35.7,2)
 
To clarify (only slightly) Harlan's explanation, most terminating decimal
fractions (including .7 and .4) are non-terminating binary fractions that can
only be approximated. The subtraction is exactly correct, given the
unavoidable approximations to your inputs.

The D2D function at
http://groups.google.com/group/microsoft.public.excel/msg/b106871cf92f8465
can be used to see the exact value of the approximation that Excel (and
almost all other computer software) is using.

Jerry
 
Back
Top