0.820000000000001 instead of 0.82

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using Excel 2003 Small Business Edition

I think I have a problem with correct rounding of the calculated values.

I use columns A,B, and C as follows:

-- all cells of column A are filled with value 0.01
-- B1 is filled with value 0.01
-- all cells of column B beginning from the second cell are filled with
function =SUM(B1,A2), so that any cell of column B would be a sum of a cell
immediately above and a cell to the left from the current one
-- I copy column B and paste it into column C with the "values" option, so
that only the resulting values, not functions, are copied.


0.01 0.01
0.01 =SUM(B1,A2) 0.02
0.01 =SUM(B2,A3) 0.03 --> F(x) 0.03
…………
0.01 =SUM(B80,A81) 0.81 --> F(x) 0.81
0.01 =SUM(B81,A82) 0.82 --> F(x) 0.820000000000001

When working with the resulting column C I noticed that although all data on
the sheet itself look correct, beginning from line 82 down on, some values in
the function field F(x) of a focused cell of column C are displayed with
either extra 0.000000000000001 or as a n.nn9999999 approximation of a correct
value.

Is it a bug or a known "feature"?
 
This is extremely well known, and common to almost all computer software.

Almost all computer software does binary math. Most terminating decimal
fractions (including 0.01) are non-terminating binary fractions that can only
be approximated. What you are seeing is the accumulation of the
approximations.

The math is exactly correct given the approximations to the inputs.

If you want to see the actual values of the approximations being used, then
you might find the functions useful at
http://groups.google.com/group/microsoft.public.excel/msg/b106871cf92f8465

If you just want to avoid unexpected consequences of binary approximation,
then use =ROUND(SUM(B1,A2),2) since you know that anything beyond the second
decimal place in your calculation is residue of the binary approximations.

The topic of this discussion group is things that cause Excel to crash. Had
you posted your question in a more appropriate forum (such as the General
group) then you would likely have received an answer within minutes of
posting.

Jerry
 
Back
Top