Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Binary NOT? | Excel Worksheet Functions | |||
Hexadecimal to binary | Excel Discussion (Misc queries) | |||
Binary bit analysis | Excel Discussion (Misc queries) | |||
Solver returns non binary answer in binary constrained cells | Excel Worksheet Functions | |||
binary bit word | Excel Worksheet Functions |