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" <(E-Mail Removed)> wrote in message
news:96E80B98-9DA2-4083-A730-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
news:96E80B98-9DA2-4083-A730-(E-Mail Removed)...
> 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 <(E-Mail Removed)>
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" <(E-Mail Removed)> wrote in message
news:96E80B98-9DA2-4083-A730-(E-Mail Removed)...
> 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