Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 26
Default Binary operator (?)

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 =A1A3 yuo
can't close edit.

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

Ideas???

Bye!
Scossa
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,045
Default Binary operator (?)

On Fri, 18 Mar 2011 00:37:46 -0700 (PDT), Scossa wrote:

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 =A1A3 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.
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,059
Default Binary operator (?)

On Mar 18, 12:37*am, Scossa wrote:
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
*=A1A3 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.
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 26
Default Binary operator (?)

On 18 Mar, 09:46, Ron Rosenfeld wrote:
On Fri, 18 Mar 2011 00:37:46 -0700 (PDT), Scossa wrote:
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 *=A1A3 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.- Nascondi testo citato

- Mostra testo citato -



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

Bye!
Scossa
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 26
Default Binary operator (?)

On 18 Mar, 09:47, joeu2004 wrote:
On Mar 18, 12:37*am, Scossa wrote:

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
*=A1A3 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.


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

Bye!
Scossa


  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,045
Default Binary operator (?)

On Fri, 18 Mar 2011 01:47:28 -0700 (PDT), joeu2004 wrote:

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.

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 420
Default Binary operator (?)

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.

On 03/18/2011 02:37, Scossa wrote:
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 =A1A3 yuo
can't close edit.

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

Ideas???

Bye!
Scossa


--
Dave Peterson
  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 26
Default Binary operator (?)

On 18 Mar, 13:31, Dave Peterson wrote:
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.

On 03/18/2011 02:37, Scossa wrote:





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 *=A1A3 yuo
can't close edit.


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


Ideas???


Bye!
Scossa


--
Dave Peterson- Nascondi testo citato

- Mostra testo citato -


Ok, tnks.
  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,059
Default Binary operator (?)

On Mar 18, 4:42*am, Ron Rosenfeld wrote:
On Fri, 18 Mar 2011 01:47:28 -0700 (PDT), joeu2004 wrote:
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.


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.
  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,045
Default Binary operator (?)

On Fri, 18 Mar 2011 10:14:06 -0700 (PDT), joeu2004 wrote:

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.


  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,059
Default Binary operator (?)

On Mar 18, 3:42*pm, Ron Rosenfeld wrote:
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.
  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,059
Default Binary operator (?)

On Mar 18, 3:52*pm, joeu2004 wrote:
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
  #13   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,045
Default Binary operator (?)

On Fri, 18 Mar 2011 15:52:30 -0700 (PDT), joeu2004 wrote:

On Mar 18, 3:42*pm, Ron Rosenfeld wrote:
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.

  #14   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,045
Default Binary operator (?)

On Fri, 18 Mar 2011 17:23:22 -0700 (PDT), joeu2004 wrote:

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.
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Binary NOT? Mark Scott[_3_] Excel Worksheet Functions 4 January 24th 10 10:21 PM
Hexadecimal to binary [email protected] Excel Discussion (Misc queries) 5 March 4th 06 06:45 PM
Binary bit analysis MadManInABox Excel Discussion (Misc queries) 5 September 9th 05 09:08 PM
Solver returns non binary answer in binary constrained cells Navy Student Excel Worksheet Functions 6 September 1st 05 03:11 PM
binary bit word Don Excel Worksheet Functions 2 August 20th 05 02:54 PM


All times are GMT +1. The time now is 10:42 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"