MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Format problem


Posted by Sandy on January 03, 2002 11:15 AM

I have a formula sum=(d24-d20) which produces the result 0.00. I want a simple - (dash) to appear. The same formula is used for the range of cells in column C and the dash does appear. I used format cells - accounting no dollar sign and no decimal. I tried clearing the format and copying and pasting from cell C to D but keeping coming up with 0.00 - how can I get the - to appear only?


Posted by Scott on January 03, 2002 11:19 AM

It's possible that your calc is not producing absolute zero (something like 0.0000001). You can see this by increasing the decimal spaces. Other than this, Accounting format should give you the results that you desire.

Posted by Chris D on January 03, 2002 1:53 PM

Have you tried :

view
toolbars
customise
commands
catagories = format
commands = , comma style

click on ", comma style" and drag it into an appropriate place in your toolbar

close

you should now have a "," button in your toolbar

highlight your range of data and click on the new "," button : this will comma format your numbers but will also show any zeros as a mere "-"

As Scott rightly points out though, sometimes a 0.00 is really a 0.00000000000037864 etc etc so it will not recognise this as zero.

HTH
Chris

Posted by Brian on January 03, 2002 2:37 PM

Sandy
Try pasting the following in the custom category

_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)


Choose the cell,
Choose Format
Choose Number
Choose Custom
On the right where it says “Type” paste the above in the open box below.
See if this does the trick and let me know..

Brian