I'm writing an IF function and want my formula to return a 1 if the cell has
a number in it, but return a 0 if the cell is blank of has a zero in it.

ie Cell A1 would return 1 if A2 has 3 in it, but return 0 if A2 is blank or
has 0 in it.


Thnanks

=COUNT(A:A)-COUNTIF(A:A,0)


"jjlang" wrote:

I'm writing an IF function and want my formula to return a 1 if the cell has
a number in it, but return a 0 if the cell is blank of has a zero in it.

ie Cell A1 would return 1 if A2 has 3 in it, but return 0 if A2 is blank or
has 0 in it.


Thnanks

=if(or(A2=0,A2=""),0,1)

"jjlang" wrote:

I'm writing an IF function and want my formula to return a 1 if the cell has
a number in it, but return a 0 if the cell is blank of has a zero in it.

ie Cell A1 would return 1 if A2 has 3 in it, but return 0 if A2 is blank or
has 0 in it.


Thnanks

One way...

=--AND(COUNT(A1),A1<0)

--
Biff
Microsoft Excel MVP


"jjlang" wrote in message
...
I'm writing an IF function and want my formula to return a 1 if the cell
has
a number in it, but return a 0 if the cell is blank of has a zero in it.

ie Cell A1 would return 1 if A2 has 3 in it, but return 0 if A2 is blank
or
has 0 in it.


Thnanks

You can use this IF formula...

=IF(AND(ISNUMBER(A1),A1<0),1,0)

or you can use this shorter non-IF formula instead...

=--AND(ISNUMBER(A1),A1<0)

--
Rick (MVP - Excel)



"jjlang" wrote in message
...
I'm writing an IF function and want my formula to return a 1 if the cell
has
a number in it, but return a 0 if the cell is blank of has a zero in it.

ie Cell A1 would return 1 if A2 has 3 in it, but return 0 if A2 is blank
or
has 0 in it.


Thnanks

On Fri, 9 Apr 2010 06:57:01 -0700, jjlang
wrote:

I'm writing an IF function and want my formula to return a 1 if the cell has
a number in it, but return a 0 if the cell is blank of has a zero in it.

ie Cell A1 would return 1 if A2 has 3 in it, but return 0 if A2 is blank or
has 0 in it.


Thnanks

=if(a2=0,0,1)

or

=--(A2<0)

or

=(A2<0)*1
--ron

Hi jj

Try something as

=COUNT(C26:C28)-COUNTIF(C26:C28,0)

With kind regards,

JP

"jjlang" wrote in message
...
I'm writing an IF function and want my formula to return a 1 if the cell
has
a number in it, but return a 0 if the cell is blank of has a zero in it.

ie Cell A1 would return 1 if A2 has 3 in it, but return 0 if A2 is blank
or
has 0 in it.


Thnanks