Summation cell formatting

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jim Doerfler - 06 Sep 2007 21:14 GMT

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,

Signature

James W. Doerfler, CPA

Reply to this Message

Peo Sjoblom - 06 Sep 2007 21:25 GMT

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

Signature

Regards,

Peo Sjoblom

>I have a four column worksheet - col A contains text for descriptions, cols
>b
[quoted text clipped - 11 lines]
>
> Thanks,

Reply to this Message

Jim Doerfler - 06 Sep 2007 21:50 GMT

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!

Signature

James W. Doerfler, CPA

> Most likely it's because the cells with 0.00 is not really zero, if you copy
> one of the 0.00
[quoted text clipped - 31 lines]
> >
> > Thanks,

Reply to this Message