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 |
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 |
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 |
COUNT BLANK AND ZERO AS THE SAME THING
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 |
COUNT BLANK AND ZERO AS THE SAME THING
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 |
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 =--(A2<0) or =(A2<0)*1 --ron |
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 |
All times are GMT +1. The time now is 09:41 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com