|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
April 9th, 2010, 02:57 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNT BLANK AND ZERO AS THE SAME THING
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 
|
|
#2
April 9th, 2010, 03:19 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNT BLANK AND ZERO AS THE SAME THING
=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
|
|
#3
April 9th, 2010, 03:21 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNT BLANK AND ZERO AS THE SAME THING
=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
|
|
#4
April 9th, 2010, 03:21 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNT BLANK AND ZERO AS THE SAME THING
One way...
=--AND(COUNT(A1),A10) -- 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
|
|
#5
April 9th, 2010, 03:23 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNT BLANK AND ZERO AS THE SAME THING
You can use this IF formula...
=IF(AND(ISNUMBER(A1),A10),1,0) or you can use this shorter non-IF formula instead... =--AND(ISNUMBER(A1),A10) -- 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
|
|
#6
April 9th, 2010, 03:23 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNT BLANK AND ZERO AS THE SAME THING
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 =--(A20) or =(A20)*1 --ron
|
|
#7
April 9th, 2010, 03:23 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNT BLANK AND ZERO AS THE SAME THING
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
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
