Excel question...is this possible?

Question

allright...let's say we use cell A1...and let's say we have a zero in that field, i would like to have a formula say basically:

if(A1=0,"keep the value zero but hide the number so it returns a blank",A1)

in other words...keep whatever number, text, or whatever is in that cell, so when you use lets say the sum function it'll count the 0 but just hide it...dont show it when its a 0 but count it....hehe i just lost myself....i hope im making any sense on this question...thank you guys...oh and u programmers and excel wiz people are my idols and heroes :)

Answer 1

Format -> Conditional Formatting

Condition 1

Cell Value IS - Equal To - 0

Format Button - Font tab - Color Select "White". (or whatever your cell background is.)

If you have multiple background colors, you will have to write a VB macro:

Should be similar to:

If (x1CellValue = 0) Then

  Selection.Font.ColorIndex = Selection.Interior.ColorIndex

End If

Answer 2

If you have a formula in cell A1, there is no room for a value there.  You can put the formula in another cell, A2, for instance, but then A1 would just show the value.  A2 COULD have something like =if(a1=0,0," "), but that's rather redundant to show the zero in A2, or leave it blank.

Sorry, there is just no way to do what you are asking about.

Answer 3

You can use:

if(A1=0,"",A1)

will return a null value if zero.

Answer 4

kind of confusing :-)  but, do you just not want to display the 0 if it's zero?  If that's the case, and you want to do it for the whole worksheet, you can turn off the display of 0's completely.

In excel 2003, go to Tools > Options. Under the View tab, uncheck the Zero Values box.  Anything that evaluates to a 0, will not display on the screen.  In Excel 2007, I'm not sure where you turn it off but it's there somewhere (maybe just search the help screens for Zero Value)

As far changing the font color based on the contents of A1, that's easy.  There's a feature called Conditional Formatting that will do exactly that (ie: you put in your condition and if the condition is met, you can set the font style, color, size, etc of the cell).