formulas and decimal places

[Dilys Duplock]

Using Excel 2003
I have a spreadsheet with general sum formulas in several columns. All
columns are formatted to show 2 decimal points only. When I add up the final
column (wth a formula or by blocking/choosing sum on the bottom bar) the
total has taken account of all the extra decimal places and added these in
thereby giving the wrong total of the column. In some cases the totals also
do not add up along the row either because of the same problem!

 

[DaveO]

When you're summing the final figures you're not summing what you can see but
the actual results of the formulas, so it's taking into account all of the
dp's.

Surely, you'd want an accurate number for your final sum, no matter what the
'rounded' sums show?

HTH.

 

[DonCam65]

Dilys
I strike this problem frequently. Any time one of the sum components
contains a calculated figure you are likely to get numbers with more than 2
decimal places. If it is money then actual real amount usually forms part of
a greater total and so discrepancies arise.
My solution
Use the following as your <General Sum Formula>
=ROUND(<General Sum Formula>,2) to get only the decimal places

Example
Values in B2, C2, D2, E2 Total required in F2
=ROUND(Sum(B2:E2),2)
When F2 is used as a component in a greater total there will not be a
problem with extra decimal places.

Incidentally don't ever compare two figures for equality unless you use
something similar to the above first. Where a calculation is involved the
system automatically goes out to 23 (I think) places.