Hello Roger,

I am "intrigued" and I will experiment in detail much later today.

Let me quickly tell you what I just did.  I started a blank sheet.  I did =COUNTBLANK(A1:A10) and I got 10.  Then I keyed in = " " to A1 and A2 respectively, and I got 8.  Then I keyed in 999 into A3 and I got 7.  Then I cleared A3 and I got 8.  I have always wondered if Excel can tell a blank cell (never touched) from a cell that has data previously but cleared.  This test tells me "no" but one expert says it can.  Is my test not set up properly or have I misunderstood the expert or ......

Can you or someone tell me if Excel knows the difference between a blank cell and a cleared cell?  I am pretty sure now that Excel can differentiate between a null cell and a blank cell.

Epinn (getting too detailed?)

Hi Epinn

i may be entirely wrong here, and I am quite willing to be shot down in
flames<bg>
I think there are 3 conditions
Zero in a  cell        0
an Empty cell        BLANK
Null in a cell           =""

Blank and Zero are counted as equivalent, but are both different from
Null.

Your test is looking at the whole of columns C through column G
Enter the following into cells A1:A7 respectively on a completely new
empty sheet
0, BLANK, ="", ="", BLANK, 0, BLANK
In B1 enter
=(COUNTIF(C:G,A1)>0)    and copy down through B2:B3
in B4 enter
=COUNTIF(C:G,A4)           and copy down through B5:B6
In B7 enter
=COUNTIF(C:G,A7)+COUNTIF(C:G,"")

The results you will see are
FALSE, FALSE, TRUE, 327680, 0, 0 , 327680

Now enter a 0 in say cell D1 and you will see the results change to
TRUE, TRUE, TRUE, 327679, 1, 1, 327680

So you can see that the count for a totally Blank cell is the same as
the count for a Zero cell, but is different from the count for a Null
cell ""

You would need to combine both cases if you want to return True for 0 or
Null as
=COUNTIF(C:G,A7)>0+COUNTIF(C:G,"")>0
(here the plus sign is acting equivalent to the "OR" condition)

If A1 is blank and there is a blank in C:G, I don't get TRUE with the
second formula.  That is why I say I have to use COUNTBLANK.

Have I confused everyone now?

Epinn

I know I can use the following formula to differentiate.

=IF(A1<>"",COUNTIF(C:G,A1)>0)

I was just surprised that blanks and zeros are treated the same.

Epinn

Should have included my formula instead of writing lines.  Although I
say column, I use each cell in the column as a criterion.  The formula
is copied down.

=COUNTIF(C:G,A1)>0

What I want to say is if A1 = 0 or blank, and 0 doesn't exist in C:G, I
get FALSE.  If 0 exists in C:G, I get TRUE.  If A1 is a blank, I don't
want to see TRUE.

Yes, when you include "" as part of the syntax.

For my experiment, H1 is a blank and =COUNTIF(A:A,H1) returns 0.

Epinn

You are not making sense (at least to me). What does another column have
to
do with it, COUNTIF works on a single range.

=COUNTIF(A:A,"") counts blanks for me.

I want to use COUNTIF > 0 to determine if a value in column A exists in
another column.  This works fine for non-blank and non-zero cells.
COUNTIF
appears to handle a blank and a "0" the same way.  If they exist in
column A
and a blank exists in another column, FALSE will be returned for both
"0"
and blank.  If a "0" exists in another column, TRUE will be returned for
both "0" and blank.  Wonder how I can differentiate between the two.  If
I
have a blank in column A and a "0" in another column, I don't want to
consider it a match.  Guess I have to use COUNTBLANK on the two columns
respectively.

Any comments?

Epinn

It is not difficult to differentiate between the pair (0 and blank) and null.  The problem is to differentiate between 0 and blank.  

As stated in my previous posts, I couldn't get TRUE when there was a match for a blank cell.  If a zero was found, I got TRUE when in fact the criterion was a blank cell.

More later ......

Epinn

Hi Epinn

i may be entirely wrong here, and I am quite willing to be shot down in
flames<bg>
I think there are 3 conditions
Zero in a  cell        0
an Empty cell        BLANK
Null in a cell           =""

Blank and Zero are counted as equivalent, but are both different from
Null.

Your test is looking at the whole of columns C through column G
Enter the following into cells A1:A7 respectively on a completely new
empty sheet
0, BLANK, ="", ="", BLANK, 0, BLANK
In B1 enter
=(COUNTIF(C:G,A1)>0)    and copy down through B2:B3
in B4 enter
=COUNTIF(C:G,A4)           and copy down through B5:B6
In B7 enter
=COUNTIF(C:G,A7)+COUNTIF(C:G,"")

The results you will see are
FALSE, FALSE, TRUE, 327680, 0, 0 , 327680

Now enter a 0 in say cell D1 and you will see the results change to
TRUE, TRUE, TRUE, 327679, 1, 1, 327680

So you can see that the count for a totally Blank cell is the same as
the count for a Zero cell, but is different from the count for a Null
cell ""

You would need to combine both cases if you want to return True for 0 or
Null as
=COUNTIF(C:G,A7)>0+COUNTIF(C:G,"")>0
(here the plus sign is acting equivalent to the "OR" condition)

If A1 is blank and there is a blank in C:G, I don't get TRUE with the
second formula.  That is why I say I have to use COUNTBLANK.

Have I confused everyone now?

Epinn

I know I can use the following formula to differentiate.

=IF(A1<>"",COUNTIF(C:G,A1)>0)

I was just surprised that blanks and zeros are treated the same.

Epinn

Should have included my formula instead of writing lines.  Although I
say column, I use each cell in the column as a criterion.  The formula
is copied down.

=COUNTIF(C:G,A1)>0

What I want to say is if A1 = 0 or blank, and 0 doesn't exist in C:G, I
get FALSE.  If 0 exists in C:G, I get TRUE.  If A1 is a blank, I don't
want to see TRUE.

Yes, when you include "" as part of the syntax.

For my experiment, H1 is a blank and =COUNTIF(A:A,H1) returns 0.

Epinn

You are not making sense (at least to me). What does another column have
to
do with it, COUNTIF works on a single range.

=COUNTIF(A:A,"") counts blanks for me.

I want to use COUNTIF > 0 to determine if a value in column A exists in
another column.  This works fine for non-blank and non-zero cells.
COUNTIF
appears to handle a blank and a "0" the same way.  If they exist in
column A
and a blank exists in another column, FALSE will be returned for both
"0"
and blank.  If a "0" exists in another column, TRUE will be returned for
both "0" and blank.  Wonder how I can differentiate between the two.  If
I
have a blank in column A and a "0" in another column, I don't want to
consider it a match.  Guess I have to use COUNTBLANK on the two columns
respectively.

Any comments?

Epinn

Hi Roger,

Thank you for taking the time to get to the bottom.  I did your test and it confirmed my prepost test on blank and zero.  The fact that you brought up null string (i.e. empty text) and Lori mentioned true blank (i.e. ignoring null string) help me see the big picture.

No, you are absolutely correct based on our tests that there are three conditions:-

0, blank and null

and may be there is a fourth condition - true blank??

I find it interesting that blanks have such affinity.

Blanks team up with 0 and are apart from null strings - see Roger's test.
Blanks team up with null strings - COUNTBLANK.
Blanks are true blanks and apart from null strings - COUNTIF with "=" as criterion.

Have I left out anything?

Epinn

Hi Epinn

i may be entirely wrong here, and I am quite willing to be shot down in
flames<bg>
I think there are 3 conditions
Zero in a  cell        0
an Empty cell        BLANK
Null in a cell           =""

Blank and Zero are counted as equivalent, but are both different from
Null.

Your test is looking at the whole of columns C through column G
Enter the following into cells A1:A7 respectively on a completely new
empty sheet
0, BLANK, ="", ="", BLANK, 0, BLANK
In B1 enter
=(COUNTIF(C:G,A1)>0)    and copy down through B2:B3
in B4 enter
=COUNTIF(C:G,A4)           and copy down through B5:B6
In B7 enter
=COUNTIF(C:G,A7)+COUNTIF(C:G,"")

The results you will see are
FALSE, FALSE, TRUE, 327680, 0, 0 , 327680

Now enter a 0 in say cell D1 and you will see the results change to
TRUE, TRUE, TRUE, 327679, 1, 1, 327680

So you can see that the count for a totally Blank cell is the same as
the count for a Zero cell, but is different from the count for a Null
cell ""

You would need to combine both cases if you want to return True for 0 or
Null as
=COUNTIF(C:G,A7)>0+COUNTIF(C:G,"")>0
(here the plus sign is acting equivalent to the "OR" condition)

If A1 is blank and there is a blank in C:G, I don't get TRUE with the
second formula.  That is why I say I have to use COUNTBLANK.

Have I confused everyone now?

Epinn

I know I can use the following formula to differentiate.

=IF(A1<>"",COUNTIF(C:G,A1)>0)

I was just surprised that blanks and zeros are treated the same.

Epinn

Should have included my formula instead of writing lines.  Although I
say column, I use each cell in the column as a criterion.  The formula
is copied down.

=COUNTIF(C:G,A1)>0

What I want to say is if A1 = 0 or blank, and 0 doesn't exist in C:G, I
get FALSE.  If 0 exists in C:G, I get TRUE.  If A1 is a blank, I don't
want to see TRUE.

Yes, when you include "" as part of the syntax.

For my experiment, H1 is a blank and =COUNTIF(A:A,H1) returns 0.

Epinn

You are not making sense (at least to me). What does another column have
to
do with it, COUNTIF works on a single range.

=COUNTIF(A:A,"") counts blanks for me.

I want to use COUNTIF > 0 to determine if a value in column A exists in
another column.  This works fine for non-blank and non-zero cells.
COUNTIF
appears to handle a blank and a "0" the same way.  If they exist in
column A
and a blank exists in another column, FALSE will be returned for both
"0"
and blank.  If a "0" exists in another column, TRUE will be returned for
both "0" and blank.  Wonder how I can differentiate between the two.  If
I
have a blank in column A and a "0" in another column, I don't want to
consider it a match.  Guess I have to use COUNTBLANK on the two columns
respectively.

Any comments?

Epinn

Roger,

I should stop playing with blank, null, zero etc. real soon or I may go "insane."

Let me tell you what I have found.  Not sure if it is a bug.

Key in 1,2,3,4,5,6,7,8,9,0 to A1:A10 respectively i.e. A1 = 1, A2 = 2 etc. etc.

Then key in the following somewhere.

=SUMPRODUCT(COUNTIF(A1:A10,A1:A10))>10

You get a FALSE because there is no duplicate.  Now, change the "2" in A2 to "1".

You get a TRUE because there is duplicate i.e. A1=A2=1.  So far so good.  Please do "evaluate formula" at this time.  You will see {2,2,1,1,.....} and this makes sense.  I just want to make note of the lst and 2nd "2".

Now clear A1 and A2 by pressing del key and you have two blank cells.  You get a FALSE.  I can accept that the formula ignore two blanks.  Now the following confuses me.

You still have A1 and A2 as two empty blank cells, right?  Now, key in ="" to A1.  Do you get TRUE?  So, what is the duplicate here?  Let's do "evaluate formula" and you will see just ONE "2".  I can't explain this.  Remember earlier when we have A1 and A2 being "1" evaluate formula shows us {2,2,1,1,......}.  It doesn't make sense to me that there is only ONE "2" now.  If there is a duplicate, there should be another "2" somewhere between the { }.

Is this a bug or am I going "crazy?"  By the way, I use version 2003.  Would you mind telling me if you have got the same.  

Now, I am going to explore the following which is better to search for duplicates.

=IF(COUNTA(A1:A10),MAX(FREQUENCY(A1:A10,A1:A10))>1,"")

I appreciate your help.

Epinn  

Hi Epinn

i may be entirely wrong here, and I am quite willing to be shot down in
flames<bg>
I think there are 3 conditions
Zero in a  cell        0
an Empty cell        BLANK
Null in a cell           =""

Blank and Zero are counted as equivalent, but are both different from
Null.

Your test is looking at the whole of columns C through column G
Enter the following into cells A1:A7 respectively on a completely new
empty sheet
0, BLANK, ="", ="", BLANK, 0, BLANK
In B1 enter
=(COUNTIF(C:G,A1)>0)    and copy down through B2:B3
in B4 enter
=COUNTIF(C:G,A4)           and copy down through B5:B6
In B7 enter
=COUNTIF(C:G,A7)+COUNTIF(C:G,"")

The results you will see are
FALSE, FALSE, TRUE, 327680, 0, 0 , 327680

Now enter a 0 in say cell D1 and you will see the results change to
TRUE, TRUE, TRUE, 327679, 1, 1, 327680

So you can see that the count for a totally Blank cell is the same as
the count for a Zero cell, but is different from the count for a Null
cell ""

You would need to combine both cases if you want to return True for 0 or
Null as
=COUNTIF(C:G,A7)>0+COUNTIF(C:G,"")>0
(here the plus sign is acting equivalent to the "OR" condition)

If A1 is blank and there is a blank in C:G, I don't get TRUE with the
second formula.  That is why I say I have to use COUNTBLANK.

Have I confused everyone now?

Epinn

I know I can use the following formula to differentiate.

=IF(A1<>"",COUNTIF(C:G,A1)>0)

I was just surprised that blanks and zeros are treated the same.

Epinn

Should have included my formula instead of writing lines.  Although I
say column, I use each cell in the column as a criterion.  The formula
is copied down.

=COUNTIF(C:G,A1)>0

What I want to say is if A1 = 0 or blank, and 0 doesn't exist in C:G, I
get FALSE.  If 0 exists in C:G, I get TRUE.  If A1 is a blank, I don't
want to see TRUE.

Yes, when you include "" as part of the syntax.

For my experiment, H1 is a blank and =COUNTIF(A:A,H1) returns 0.

Epinn

You are not making sense (at least to me). What does another column have
to
do with it, COUNTIF works on a single range.

=COUNTIF(A:A,"") counts blanks for me.

I want to use COUNTIF > 0 to determine if a value in column A exists in
another column.  This works fine for non-blank and non-zero cells.
COUNTIF
appears to handle a blank and a "0" the same way.  If they exist in
column A
and a blank exists in another column, FALSE will be returned for both
"0"
and blank.  If a "0" exists in another column, TRUE will be returned for
both "0" and blank.  Wonder how I can differentiate between the two.  If
I
have a blank in column A and a "0" in another column, I don't want to
consider it a match.  Guess I have to use COUNTBLANK on the two columns
respectively.

Any comments?

Epinn