Calculation Problem

[djm123]

All though the formula is correct ( a simple math addition between 2 cells)
..There are a few of these cells in my 2003 XL Spread Sheet that consistently
makes a 1 cent error in the addition - that is, it's adds the cell less 1
cent (formatted in currency)
How do I fix this?

Thanks

 

[Niek Otten]

http://www.mcgimpsey.com/excel/pennyoff.html

 

[Bernard Liengme]

I expect that the two values being added result from formula and you are
getting round off errors.
You are seeing one thing (the cell is formatted to display 2 decimal places)
but the stored values are slightly different

A1 might display 1.54 but the value stored could be 1.544
B1 might display 2.72 but the value stored could be 2.723
You will expect the formula =A1+B1 to return 4.26 (1.54+2.72)
But Excel computes 1.544+2.723 to get 4.267 which it displays as 4.27 (a
penny out)

You could use =ROUND(A1,2)+ROUND(B1,2)
You could change your formula from =a_formula to =ROUND(a_formula,2)
You could specify your worksheet uses "precision as displayed" - many Excel
authors warn against.

Have a look at
http://mcgimpsey.com/excel/pennyoff.html

best wishes