I entered '1234 (note the apostrophe) into A1 and the formula
=(A1=2000) into A2. I was surprised that A2 results in TRUE.

Why is that?

If I enter the formula =A1+A1 into A3, it results in the number 2468.

So it seems that the text in A1 is being interpreted as a number, at
least sometimes.

Even if that is not the case in A2 (why not?), how is A1 being
interpreted such that the conditional expression returns TRUE?

To rule out human error, if I replace A1 with 1234 (no apostrophe), A2
results in FALSE, as expected.

PS: I am not looking for work-arounds. I know several myself. I am
merely asking why A2 results in TRUE. It's a curiosity.

Text is always greater than a number in Excel so when you precede
a number with an apostrophe it becomes text and thus is greater.

--


Regards,


Peo Sjoblom

wrote in message
...
I entered '1234 (note the apostrophe) into A1 and the formula
=(A1=2000) into A2. I was surprised that A2 results in TRUE.

Why is that?

If I enter the formula =A1+A1 into A3, it results in the number 2468.

So it seems that the text in A1 is being interpreted as a number, at
least sometimes.

Even if that is not the case in A2 (why not?), how is A1 being
interpreted such that the conditional expression returns TRUE?

To rule out human error, if I replace A1 with 1234 (no apostrophe), A2
results in FALSE, as expected.

PS: I am not looking for work-arounds. I know several myself. I am
merely asking why A2 results in TRUE. It's a curiosity.

On Nov 5, 3:54*pm, "Peo Sjoblom" wrote:
Text is always greater than a number in Excel
so when you precede a number with an apostrophe
it becomes text and thus is greater.

Thanks. I can see that you are right. When I put ABCD into A1 and
=(A1=2000) into A2, A2 results in TRUE.

But why?

I would expect comparing text to a number to result in a #VALUE error,
just as =A1-2000 does. Conversely, since =A1-2000 results in a
numeric result when A1 contains text that looks like a number, I would
expect =(A1=2000) to do a numeric comparison.

Just a curiosity.

wrote:

On Nov 5, 3:54 pm, "Peo Sjoblom" wrote:

Text is always greater than a number in Excel
so when you precede a number with an apostrophe
it becomes text and thus is greater.


Thanks. I can see that you are right. When I put ABCD into A1 and
=(A1=2000) into A2, A2 results in TRUE.

But why?

I would expect comparing text to a number to result in a #VALUE error,
just as =A1-2000 does. Conversely, since =A1-2000 results in a
numeric result when A1 contains text that looks like a number, I would
expect =(A1=2000) to do a numeric comparison.

Just a curiosity.

You may compare two texts also, it is not a mathmatic operation.

Maybe it is due to sorting when you have a mix with numbers and text
where numbers will sort ascending before any text entry?

--


Regards,


Peo Sjoblom

wrote in message
...
On Nov 5, 3:54 pm, "Peo Sjoblom" wrote:
Text is always greater than a number in Excel
so when you precede a number with an apostrophe
it becomes text and thus is greater.

Thanks. I can see that you are right. When I put ABCD into A1 and
=(A1=2000) into A2, A2 results in TRUE.

But why?

I would expect comparing text to a number to result in a #VALUE error,
just as =A1-2000 does. Conversely, since =A1-2000 results in a
numeric result when A1 contains text that looks like a number, I would
expect =(A1=2000) to do a numeric comparison.

Just a curiosity.

On Nov 6, 7:59*am, "Peo Sjoblom" wrote:
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.

I think Excel will convert "1234" (i.e. a string made up purely of
digits) internally to numeric values where it can (and it makes sense
to do so, as in arithmetic operations), but it does not convert the
number 1234 to text "1234" except when a number is concatenated with
something else.

Maybe it has something to do with operator precedence - arithmetic
operators come before concatenation, with comparison operators last.

Does that make sense?

Pete

On Nov 7, 12:43*am, wrote:
On Nov 6, 7:59*am, "Peo Sjoblom" wrote:

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.

On Nov 6, 5:15 pm, Pete_UK wrote:
Does that make sense?

Sorry, but not to me.


[Excel] does not convert the number 1234 to text
"1234" except when a number is concatenated with
something else.

I already provided a counter-example: LEN(1234). According to the
Help page: "LEN returns the number of characters in a
__text_string__". So I presume it is treating 1234 to "1234" in order
to determine its length.


Maybe it has something to do with operator precedence
- arithmetic operators come before concatenation, with
comparison operators last.

That comment seems to be made out of context. There are no other
operators in the examples I provided: only one comparison operator.


In any case, none of your comments help explain why "1234"=2000 is
always TRUE. Do they?


----- original posting -----

On Nov 6, 5:15*pm, Pete_UK wrote:
I think Excel will convert "1234" (i.e. a string made up purely of
digits) internally to numeric values where it can (and it makes sense
to do so, as in arithmetic operations), but it does not convert the
number 1234 to text "1234" except when a number is concatenated with
something else.

Maybe it has something to do with operator precedence - arithmetic
operators come before concatenation, with comparison operators last.

Does that make sense?

Pete

On Nov 7, 12:43*am, wrote:



On Nov 6, 7:59*am, "Peo Sjoblom" wrote:

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.- Hide quoted text -

- Show quoted text -