Maybe it is due to sorting when you have a mix with numbers
and text where numbers will sort ascending before any text entry?
Thanks for your thoughts. I appreciate it. We're just having an
academic discussion here, and I think you (alone) understand why I'm
so mystified.
We're dealing with an ambiguous situation.
When text is used in numeric expressions and functions, clearly the
text should be treated as numbers (if possible); and indeed, that is
what Excel does. For example, ="1234"-2000 returns -766, just as
=1234-2000 does.
When numbers are used in text expressions and functions, clearly the
numbers should be treated as text; and indeed, that is what Excel
does. For example, =len(2000) returns 4, just as =len("2000") does.
But when text and numbers are used together with comparitive
operators, Excel has a choice: (a) do numeric comparisons (if
possible); or (b) do text comparisons. It's an arbitrary choice;
there is no right or wrong approach, other than with regard to
compatibility with Visicalc and/or Lotus and/or Multiplan. (I don't
know if/how the latter products handled this.)
I think you are saying that Excel opts for #b. That's fine; I can
accept that.
Now, it is true that all alphabetic letters, upper and lower case,
have a higher ASCII code than any numeric character. So that could
explain why my second example, ="ABCD">=2000 is TRUE.
But in my original example, why does ="1234">=2000 return TRUE,
whereas ="1234">="2000" returns FALSE, as we expect? Why isn't the
numeric 2000 converted to the string "2000", just as it is in other
text expressions?
Remember: We are assuming that text-and-number comparisons are being
treated as text expressions.
Well, perhaps I misunderstood you.
But if "1234">2000 is TRUE because 2000 is __not__ converted to text,
what in the world is Excel comparing?!
It would not make sense to me that Excel is comparing the binary
representation byte-for-byte. But even it is, "1234">=2000 should be
FALSE. The binary code for "1" is 49, and the first octet of the
floating-point 2000.0 is 64. It is my understanding that Excel always
uses floating-point for numbers. But to cover all bases, even
"1234">=32767 and "1234">=2147483647 return TRUE, those numbers having
an upper octet value of 127 in their 16-it and 32-bit
representations.
Arguably, I am assuming that Excel compares the values themselves. If
instead Excel compares the internal representation of the objects
(gulp!), all bets are off. I have no idea what the internal
representation looks like. If is a typical TLV format (type, length,
value), it is conceivable that the T-value for text is greater than
the T-value for numbers.
In any case, I would argue that returning TRUE for "1234">=2000 is
arbitrary, an accident of implementation.
Peo, do you (or anyone else) have any thoughts about all this? Am I
overlooking the obvious?
It's been know to happen -- once
.
Thanks again for sharing your thoughts on this.
