Conditional formula miscalculating blank cells
 

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

Conditional formula miscalculating blank cells
 

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,""))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Rob in Sydney" wrote:
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

Conditional formula miscalculating blank cells
 

Max

Thanks ... your answer did the trick.

Cheers

"Max" wrote:

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,""))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Rob in Sydney" wrote:
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

Conditional formula miscalculating blank cells
 

Welcome, Rob. Glad it helped
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Rob in Sydney" wrote in message
...
Max

Thanks ... your answer did the trick.

Cheers

Conditional formula miscalculating blank cells
 

=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
wrote:

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

Conditional formula miscalculating blank cells
 

On Sun, 30 Dec 2007 16:57:00 -0800, Rob in Sydney <Rob in
wrote:

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


Pretty sure this will work:

=MAX(0,SIGN(A10))


--ron

Conditional formula miscalculating blank cells
 

"Ron Rosenfeld" wrote in message
...
On Sun, 30 Dec 2007 16:57:00 -0800, Rob in Sydney <Rob in
wrote:

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


Pretty sure this will work:

=MAX(0,SIGN(A10))


--ron

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

=COUNT(A1)*(A1=0)

--
Biff
Microsoft Excel MVP

Conditional formula miscalculating blank cells
 

On Sun, 30 Dec 2007 22:57:36 -0500, "T. Valko" wrote:

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

=COUNT(A1)*(A1=0)

I knew that.

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


--ron