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

 

[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

 

[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.

 

[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