Binary operator >> (?)

[Scossa]

Hello,

I' apologize for my bad english......

Everyone know logical operator "=", ">", "<", ">=", "<=", but i found
that this operator ">>" in a function like CountIf()

=CountIf( A1:A30, ">>")

returns the numbures of cells that contain text.

But unlike others operators, if you try somthing like =A1>>A3 yuo
can't close edit.

I haven't found nothing in on-line Excel documentation about this.

Ideas???

Bye!
Scossa

 

[Ron Rosenfeld]

Hello,

I' apologize for my bad english......

Everyone know logical operator "=", ">", "<", ">=", "<=", but i found
that this operator ">>" in a function like CountIf()

=CountIf( A1:A30, ">>")

returns the numbures of cells that contain text.

But unlike others operators, if you try somthing like =A1>>A3 yuo
can't close edit.

I haven't found nothing in on-line Excel documentation about this.

Ideas???

Bye!
Scossa

I do not believe that ">>" is an operator. Rather it means <greater than> <the greater than sign>

In other words, the first character is the operator; the second character is the operand.

If you enter a series of characters in column A, and then in column B enter

=A1 > ">"

and fill down, you will see that anything that evaluates to TRUE will be counted by your COUNTIF expression.

 

[joeu2004]

i found that this operator ">>" in a function like CountIf()
=CountIf( A1:A30, ">>")
returns the numbures of cells that contain text.

But unlike others operators, if you try somthing like
 =A1>>A3 yuo can't close edit.

You are misinterpreting the COUNTIF parameter, understandably.

The first ">" is the comparison operator. The second ">" is the
character ">".

So COUNTIF(A1:A30,">>") counts all cells will text whose strings
compare greater than ">".

You can replace the second ">" with other characters (e.g. ">:") and
get the same result.

You can see things as COUNTIF does by putting =A1>">" into B1 and copy
down through B30.

What I do find odd is that ="2">">" returns TRUE. The ASCII code for
"2" is 50, and the ASCII code for ">" is 62.

 

[Scossa]

I do not believe that ">>" is an operator.  Rather it means <greater than> <the greater than sign>

In other words, the first character is the operator; the second characteris the operand.

If you enter a series of characters in column A, and then in column B enter

        =A1 > ">"

and fill down, you will see that anything that evaluates to TRUE will be counted by your COUNTIF expression.- Nascondi testo citato

- Mostra testo citato -

OK, tnks, the correct explanation is often the simplest.

Bye!
Scossa

 

[Scossa]

You are misinterpreting the COUNTIF parameter, understandably.

The first ">" is the comparison operator.  The second ">" is the
character ">".

So COUNTIF(A1:A30,">>") counts all cells will text whose strings
compare greater than ">".

You can replace the second ">" with other characters (e.g. ">:") and
get the same result.

You can see things as COUNTIF does by putting =A1>">" into B1 and copy
down through B30.

What I do find odd is that ="2">">" returns TRUE.  The ASCII code for
"2" is 50, and the ASCII code for ">" is 62.

OK, tnks, the correct explanation is often the simplest.

Bye!
Scossa

 

[Ron Rosenfeld]

What I do find odd is that ="2">">" returns TRUE. The ASCII code for
"2" is 50, and the ASCII code for ">" is 62.

I'm sure you also noticed that the number 2 returns false when stored as a number.

The comparison on my system (Excel 2007) goes according to the default sort order for Excel. And the characters must be entered as characters, and not as the results of formulas, in order for the sort order to be congruent with the comparison.

 

[Dave Peterson]

You got the explanation, but this is one reason I like this format:
=countif(a1:a30,">"&">")

or even
=countif(a1:a30,">"&999)

I think it makes it easier to see what's happening.

 

[Scossa]

You got the explanation, but this is one reason I like this format:
=countif(a1:a30,">"&">")

or even
=countif(a1:a30,">"&999)

I think it makes it easier to see what's happening.

Ok, tnks.

 

[joeu2004]

I'm sure you also noticed that the number 2 returns false
when stored as a number.

I do not get your point, if you are trying to explain what I
observed. Perhaps you could elaborate, speaking directly to my
example.

=2>">" returns FALSE because numbers are always considered less than
text, based on the collating sequence described for VLOOKUP et al.

But I typed ="2">">". I am comparing text literal to text literal.
No references to cells with formulas. And Excel is treating "2"
different from 2 in that context, as evidenced by the difference in
comparison results.

I expect that comparision to follow the ASCII collating sequence,
except that lowercase and uppercase alpha are treated the same. That
is (written mathematically):

"!" < "/" < "0" < "9" < ":" < "@" < "A" < "Z"

I cannot say where __I__ would expect "[" and "{" to fit into the
collating sequence because I do not know whether uppercase alpha is
treated as lowercase alpha or vice versa.

If you can provide a pointer to Microsoft documentation about the
collating sequence of individual text characters in comparisons, that
would be helpful. I have tried a few Help searches, to no avail.

 

[Ron Rosenfeld]

I do not get your point, if you are trying to explain what I
observed. Perhaps you could elaborate, speaking directly to my
example.

In the message to which I was replying, you had written:

'What I do find odd is that ="2">">" returns TRUE. The ASCII code for
"2" is 50, and the ASCII code for ">" is 62.'

You did not mention the behavior of the number 2, only the text string "2"

So I then mentioned that I was certain you had noticed the behavior of the number 2, having seen that you are reasonably thorough, and that the explanation was that the comparison was made according to the default sort order that MS uses, at least in my version of Excel.

If you can provide a pointer to Microsoft documentation about the
collating sequence of individual text characters in comparisons, that
would be helpful. I have tried a few Help searches, to no avail.

In Excel 2007, the relevant HELP topic is "Default sort orders". I found it by searching for "sort order" on HELP.

It is not a comprehensive list, but it covers the commonly used symbols in addition to other values.

 

[joeu2004]

In Excel 2007, the relevant HELP topic is "Default sort
orders". I found it by searching for "sort order" on HELP.

Aha! Yes, I find the same Help entry in XL2003. Thanks for that.

But according to the XL2003 "sort order" help page:

----- begin quote
Text and text that includes numbers are sorted in the
following order:
0 1 2 3 4 5 6 7 8 9 (space) ! " # $ % & ( ) * , . / :
; ? @ [ \ ] ^ _ ` { | } ~ + < = > A B C D E F G H I J
K L M N O P Q R S T U V W X Y Z
----- end quote

Clearly, ="A">"Z" should and does return FALSE. So the table is in
ascending order.

So I still find it odd that ="2">">" returns TRUE, based on the above
table.

 

[joeu2004]

But according to the XL2003 "sort order" help page:
----- begin quote
Text and text that includes numbers are sorted in the
following order:
0 1 2 3 4 5 6 7 8 9 (space) ! " # $ % & ( ) * , . / :
; ? @ [ \ ] ^ _ ` { | } ~ + < = > A B C D E F G H I J
K L M N O P Q R S T U V W X Y Z
----- end quote

Clearly, ="A">"Z" should and does return FALSE.  So the
table is in ascending order.
So I still find it odd that ="2">">" returns TRUE, based
on the above table.

Well, the answer was "obvious": the documented sort order is
incorrect.

This is apparent if we put the following into consecutive cells: 0A,
9A, !, >, A, Z. Then sort ascending.

The sorted result is: !, >, 0A, 9A, A, Z.

Obviously, the __characters__ "0" through "9" fit between ">" and "A"
in the sort order.

I suspect that whoever wrote the help page was thinking of the
__numbers__ 0 through 9. But that is covered by another section in
the "sort order" help page, to wit:

----- begin quote
In an ascending sort, Microsoft Excel uses the following order.
[....]
Numbers Numbers are sorted from the smallest negative number to the
largest positive number.

Alphanumeric sort When you sort alphanumeric text, Excel sorts left
to right, character by character.
----- end quote

 

[Ron Rosenfeld]

In Excel 2007, the relevant HELP topic is "Default sort
orders". I found it by searching for "sort order" on HELP.

Aha! Yes, I find the same Help entry in XL2003. Thanks for that.

But according to the XL2003 "sort order" help page:

----- begin quote
Text and text that includes numbers are sorted in the
following order:
0 1 2 3 4 5 6 7 8 9 (space) ! " # $ % & ( ) * , . / :
; ? @ [ \ ] ^ _ ` { | } ~ + < = > A B C D E F G H I J
K L M N O P Q R S T U V W X Y Z
----- end quote

Clearly, ="A">"Z" should and does return FALSE. So the table is in
ascending order.

So I still find it odd that ="2">">" returns TRUE, based on the above
table.

I, too, find the sort order is not consistent with the table, but is consistent with the results of "2">">"

Empirically, it seems the sort order, for text, is:

(space) ! " # $ % & ( ) * , . / : ; ? @ [ \ ] ^ _ ` { | } ~ + < = >0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z

And one can generate a list for characters greater than CHAR(127). The results are interesting.

 

[Ron Rosenfeld]

I suspect that whoever wrote the help page was thinking of the
__numbers__ 0 through 9.

Looks like our messages crossed.

It is somewhat surprising that this incorrectly published sort order has not been corrected. It has been noted in the past. http://www.mvps.org/dmcritchie/excel/sorting.htm

Also, there are interesting articles found doing a web search on collating algorithms. New stuff to me; I always just thought "sort order" and not about all the other thinking that goes into developing a sort order.