COUNT BLANK AND ZERO AS THE SAME THING
in [Functions]
|
Prev: Save As Macro
Next: Help to resove this problem.
From: jjlang on 9 Apr 2010 09:57 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
From: Teethless mama on 9 Apr 2010 10:19 =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
From: T. Valko on 9 Apr 2010 10:21 One way... =--AND(COUNT(A1),A1<>0) -- Biff Microsoft Excel MVP "jjlang" <jjlang(a)discussions.microsoft.com> wrote in message news:96E80B98-9DA2-4083-A730-80CFB68E0D16(a)microsoft.com... > 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
From: adam6b on 9 Apr 2010 10:21 =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
From: Rick Rothstein on 9 Apr 2010 10:23
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" <jjlang(a)discussions.microsoft.com> wrote in message news:96E80B98-9DA2-4083-A730-80CFB68E0D16(a)microsoft.com... > 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 |