Summation cell formatting

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

Guest

I have a four column worksheet - col A contains text for descriptions, cols b
thru d contain comma formatted numbers - 2 decimal places, the worksheet has
about 30 rows of data. When summing col b thru d the total for each column
should be zero, the row totals can be various amounts. The problem is col b
shows 0.00, but cols c and d show - (dashes) representing zero. This has
happened randomly to several worksheets over the years. I've tried
reformatting columns, formatting tool, but can't seem to get rid of the 0.00.
Any help would be appreciated.

Thanks,
 
Most likely it's because the cells with 0.00 is not really zero, if you copy
one of the 0.00
and paste special as values into an empty cell and then format that cell as
general you will see small decimal value. I do a lot of large bank account
reconciliations with complicated array formulas and I see this a lot. And if
you are not using these values in another chain of calculations you can use
ROUND like this

=ROUND(SUM(A2:D2),2)


You can also use precision as displayed under tools>options>calculations but
I would not recommend this since it can create strange results in other
places


Here are some info


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


http://www.cpearson.com/excel/rounding.htm
 
Thanks for the help, that did the trick, but I was suprised - the column b
data that is getting summed contains numbers with 2 decimal places or less
(dollar amounts that are keyed in manually), I did double check to see if
there was a third decimal place entered accidently, but none exists.

Again, thanks for the help!
 
Back
Top