Error with decimals

[Guest]

Has anyone notice a problem with up decimal, somehow excel adds up more
decimals.
eg.

cell (A1) 5045.3
cell (A2) 5045.03
cell (A3) =A1-A2

Note that the diference will be .27
But excel brings 0.270000000000437

 

[Bernard Liengme]

Welcome to the world of computer math!
Computers (no just Excel) generally store numbers in binary format (base 2)
rather than decimal (base 10) - we have 10 fingers while a computer knows
only ON and OFF.
The conversion is done following the IEEE protocol which specifies 15 digits
(this is a bit of a simplification). Some decimal numbers (real or floating
point numbers with fractional parts but not integers) cannot be exactly
represented in this binary form. So we get "round-off" errors due to the
conversion to and from binary/decimal
Read more at http://support.microsoft.com/kb/78113/en-us
One way to solve this is to use =ROUND(formula, some number less than 15)
as in =ROUND(A1-A2,10)
best wishes

 

[Guest]

If you want to expand on Bernard's answer, you might find the functions at
http://groups.google.com/group/microsoft.public.excel/msg/b106871cf92f8465
to be useful. Using them, you will see that the IEEE standard
approximations to 5045.3 and 5045.03 are
5045.3000000000001818989403545856475830078125
5045.0299999999997453414835035800933837890625
whose difference is
0.27000000000043655745685100555419921875
which Excel correctly reports to its documented limit of 15 digits.

Bottom line: the math is right, but most decimal fractions must be
approximated, leading to approximate results.

Jerry