one last question

[Dr. Harvey Waxman]

IF(A1=0,0,"")

The problem is that if A1 is empty the 0 appears as well.
I have formatted A1 both as text and number with no difference.

There must be a way to differentiate the zero from an empty cell but it isn't
so obvious to me.

Is there an answer?

 

[JE McGimpsey]

IF(A1=0,0,"")

The problem is that if A1 is empty the 0 appears as well.
I have formatted A1 both as text and number with no difference.

There must be a way to differentiate the zero from an empty cell but it isn't
so obvious to me.

Is there an answer?

You can't do it in one test, but here's one:

=IF(AND(A1<>"",A1=0),0,"")

another:

=IF(OR(ISBLANK(A1),A1<>0),"",0)

a third:

=IF(AND(ISNUMBER(A1),A1=0),0,"")

 

[Dr. Harvey Waxman]

You can't do it in one test, but here's one:

=IF(AND(A1<>"",A1=0),0,"")

another:

=IF(OR(ISBLANK(A1),A1<>0),"",0)

a third:

=IF(AND(ISNUMBER(A1),A1=0),0,"")

The issue I am working on is that I want a cell blank only when there is a
zero in any cell in a range of cells. If there is anything other than a zero (
a blank or a number) I want the cell to show a zero.

So far I have this for two cells:

=(IF(OR(AND(ISNUMBER(B2),B2=0),AND(ISNUMBER(C2),C2=0)),"",0))

It's a long formula for the nine cells i want but I guess it will work. any
more elegant ideas?

thanks in any case.

 

[Paul Berkowitz]

The issue I am working on is that I want a cell blank only when there is a
zero in any cell in a range of cells. If there is anything other than a zero
(
a blank or a number) I want the cell to show a zero.

If there's a zero, you want the result cell to be blank, but if there's a
blank you want the result cell to display zero? Really?

 

[Dr. Harvey Waxman]

If there's a zero, you want the result cell to be blank, but if there's a
blank you want the result cell to display zero? Really?

Yup. I got it to do what I want but it's a long and clumsy formula.

=(IF(OR(AND(ISNUMBER(C2),C2=0),AND(ISNUMBER(D2),D2=0),AND(ISNUMBER(E2),E2=0),AND
(ISNUMBER(F2),F2=0),AND(ISNUMBER(G2),G2=0),AND(ISNUMBER(H2),H2=0),AND(ISNUMBER(I
2),I2=0),AND(ISNUMBER(J2),J2=0),AND(ISNUMBER(K2),K2=0)),"",0))

There must be a more elegant way.

 

[JE McGimpsey]

The issue I am working on is that I want a cell blank only when
there is a zero in any cell in a range of cells. If there is
anything other than a zero ( a blank or a number) I want the cell to
show a zero.

In that case,

=IF(COUNTIF(rng,0),"",0)

 

[Harvey Waxman]

In that case,

=IF(COUNTIF(rng,0),"",0)

hmmm.

that or:
=(IF(OR(AND(ISNUMBER(C2),C2=0),AND(ISNUMBER(D2),D2=0),AND(ISNUMBER(E2),E2=0),AND
(ISNUMBER(F2),F2=0),AND(ISNUMBER(G2),G2=0),AND(ISNUMBER(H2),H2=0),AND(ISNUMBER(I
2),I2=0),AND(ISNUMBER(J2),J2=0),AND(ISNUMBER(K2),K2=0)),"",0))

tough choice. <vbg>

Who knew countif makes the distinction between zeros and blanks.?

Thanks yet again.

 

[Bob Greenblatt]

What I use is something like: =if(len(a1)>0,0,"")

 

[Harvey Waxman]

What I use is something like: =if(len(a1)>0,0,"")

Thanks everyone.