Excel and most other software stores numbers in binary format, not decimal
format. Excel follows the IEEE standard for double precision
http://en.wikipedia.org/wiki/Double_precision_floating-point_format
which provide 53 bit accuracy. As a result, integers up to
9.007199254740991E15 = 2^53-1 can be stored exactly, but most decimal
fractions must be approximated. When you do math with approximate inputs, it
should be no surprise when the result is only approximately what you intended.
You do not give an example calculation, so I will supply my own. Consider
=(0.3-0.2-0.1)
which returns -2.77556E-17 instead of 0. The intent is a calculation that
would be equivalent to
=(3-2-1)/10
which does return 0, since the integers can be represented exactly in
binary. Howver, if you conver the first version to IEEE DP binary floadting
point, you will find that in input numbers must be approximated as
0.2999999999999999888977697537484345957636833190917968750
-0.2000000000000000111022302462515654042363166809082031250
-0.1000000000000000055511151231257827021181583404541015625
----------------------------------------------------------
-0.1000000000000000277555756156289135105907917022705078125
Excel will display no more than 15 significant figures of each of these
numbers, but this is what is happening under the hood, as you can verify from
the VBA functions available at
http://groups.google.com/group/microsoft.public.excel/msg/b106871cf92f8465
If you do the math, you will find that the arithmetic is exactly correct,
given the initial approximations to the input numbers. Since the issue is
unavoidable approximation of numbers and not math errors, you can safely
round the results to a level appropriate to the kind of calculations that you
are doing.
Alternately you can restructure the calculations to only use integers.
Jerry
