#1
|
|||
|
|||
Zero (0) in cell
Hi.
If you have a cell with a formula, and the input cells are empty, this cell will show a zero (0). In the cell formating box you can define that this zero should not be shown, but I can't remember! Please, can anyone help?? |
#2
|
||||
|
||||
You can use a custom format like this:
Code:
#,##0_);(#,##0);"" Cheers, |
#3
|
|||
|
|||
You should be able to do it with Conditional Formatting.
Basically, select the entire worksheet, then click Format > Conditional Formatting. For the condition, it will be When Cell Value Equals 0 Then for the formatting when the condition is true, just make the text white. The zero will still be there, but it wont be viewable against the white background. -SW |
#4
|
||||
|
||||
Quote:
An alternative is to modify the formula. For example: =IF(A1+B1=0,"",A1+B1) or: IF(OR(A1=0,B1=0),"",A1+B1) The first hides the result only if it evaluates to 0, whereas the second hides the result if either of the inputs is 0.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
||||
|
||||
Quote:
Also, Your formula would work better as: Code:
IF(AND(A1=0,B1=0),"",A1+B1) |
#6
|
||||
|
||||
Quote:
Your advice: Quote:
As for: I was quite deliberate in using the OR statement, for the reasons I gave. With your variant, if -A1=B1, you'll still get a 0 display.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#7
|
||||
|
||||
The way I interpret the question (and I could be wrong!) is if all of the precedent cells are empty then to 'not display a result', whilst if any of the cells contain a number then a result should be displayed (including 0 if that is a result).
To do that I'd use a formula (or variation thereof) like this: Code:
=IF(COUNT(C3:D3),SUM(C3:D3),"") My 2c... |
#8
|
||||
|
||||
macropod,
Apparently lost with you. Quote:
As I said, sometimes the result is supposed to be "0", which is why I included the warning about the custom format. However with the OR statement in your example, if A1=0 and B1=4, your result would be "" instead of "4". Now that is "plain wrong". |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How can I fill cell color starting from Cell D5 using Conditional formatting instead | Learner7 | Excel | 0 | 07-08-2010 05:50 AM |
How can I make a cell color RED under the Today's date cell? | Learner7 | Excel | 2 | 07-08-2010 12:52 AM |
Help reg Protection of cell. | aligahk06 | Excel | 1 | 09-02-2009 05:45 PM |
Cell formatting | Niko | Excel | 5 | 04-23-2009 12:05 AM |
Auto-populate an MS Word table cell with text from a diff cell? | dreamrthts | Word Tables | 0 | 03-20-2009 01:49 PM |