Conditional formula miscalculating blank cells

  • Thread starter Thread starter Rob in Sydney
  • Start date Start date
R

Rob in Sydney

I am trying to create a conditional formula that will tag cells with data
points >=0 as "1", if not >=0, then a tag of "0". Excel is identifying blank
cells as though they contained a zero and therefore tags these blank cells as
a "1" when I need them to be reflected as a "0".

Can anyone help / advise how to correct this.

Thanks + happy new year
 
One way, which would also trap* any wrong results caused by text strings but
will treat text numbers as numbers
*it'll return a blank: "" for text

With source data running in A1 down
In B1, copied down:
=IF(A1="",0,IF(AND(ISNUMBER(A1+0),A1>=0),1,""))
 
=IF(OR(A1<0,A1=""),0,1)


Gord Dibben MS Excel MVP

On Sun, 30 Dec 2007 16:57:00 -0800, Rob in Sydney <Rob in
 
On Sun, 30 Dec 2007 16:57:00 -0800, Rob in Sydney <Rob in
I am trying to create a conditional formula that will tag cells with data
points >=0 as "1", if not >=0, then a tag of "0". Excel is identifying blank
cells as though they contained a zero and therefore tags these blank cells as
a "1" when I need them to be reflected as a "0".

Can anyone help / advise how to correct this.

Thanks + happy new year
Click to expand...


Pretty sure this will work:

=MAX(0,SIGN(A10))


--ron
 
Ron Rosenfeld said:
On Sun, 30 Dec 2007 16:57:00 -0800, Rob in Sydney <Rob in



Pretty sure this will work:

=MAX(0,SIGN(A10))


--ron
Click to expand...

If the cell contains numeric 0, SIGN(0) = 0.

=COUNT(A1)*(A1>=0)
 
If the cell contains numeric 0, SIGN(0) = 0.

=COUNT(A1)*(A1>=0)
Click to expand...

I knew that.

Rereading the OP's request, I see I misread. I read >0 where he wrote >=0.
:-((


--ron
 
Back
Top