MrExcel Publishing
Your One Stop for Excel Tips & Solutions

desperate ---format cells!!


Posted by Susan on May 11, 2001 11:40 AM

Help - should be simple, but I've tried everything....I have a spreadsheet that has the value zero showing up as "0.00", "(0.00)", and "-". I am desperate for a way to make all zeros appear as "-" (or anything else that is consistent). Right now, it looks very messy and I can't say if "if col. A = C = E, then X" because program does not read "-" and "0.00" as same value. Any help appreciated.


Posted by Mark W. on May 11, 2001 11:51 AM

Format as:

0;-0;"-"

...but, keep in mind that the format of a cell
won't influence the results of an IF() function.

Posted by Susan on May 14, 2001 6:47 AM

Thanks. I'll try it. I didn't think that the format should affect IF functions either, but it seems to be happening. Obviously something else is wrong, but haven't figured out what yet.

Posted by Susan on May 16, 2001 8:35 AM

Re: rounding issue

Okay, I finally found someone who could tell me why the zero in some cells showed as "(0.00)". Apparently there was a number about 12 places out in the decimals. The problem is that we're dealing with dollars and cents and doing only addition and subtraction. Therefore, I see no way we could get any number other than the first 2 decimal places. Does anyone have any idea why this is happening? I can't just use ROUND for various reasons.
Susan