error in totals of cells

[lazyatbest]

I have a problem in excell trying to total several diffferent cells into
one. This is a payroll spread sheet and when i try to get the total of the
all taxes and regular deductions, the result from the formula i am using
consitantly adds 1-2 cents to the total. I can see that it's wrong just by
adding the figures in my head. The formula i am using is
=SUM(F7:F9)+SUM(D79). Can anyone offer any thoughts?

 

[Nick Hodge]

excel will always sum to all 15 digits of it's precision even if your sheet
formats it to show only two.

I suspect you have a calculation further up the sheet which is resulting in
a lot of decimals

e.g 1.454999999 will display as 1.45

add two of these together and excel adds 1.45999999+1.454999999= 2.91999998

format this as two decimals and you get 2.92 where you will have expected
2.90

You can get around this is in two ways

1) Tools>Options...>Calculation Tab>Precision as displayed (This would not
be my preferred route)

2) Use the ROUND function in all you calculations. e.g.

=ROUND(A1/A2,2)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)