How is "A1<=A2<=A3" evaluated?
 

How does Excel evaluate "A1<=A2<=A3"?

I assume Excel evaluates it as (A1<=A2)<=A3,
where "(A1<=A2)" results in the boolean value
TRUE or FALSE.

When I put =(A1<=A2) into C1 and =C1*2 into
C2, C2 is 2 when A1<=A2 and 0 when A1A2. So
I infer that TRUE=1 and FALSE=0 in a numerical
expression.

But =(A1<=A2)<=A3 always evaluates to FALSE,
and =A1<=(A2<=A3) always evaluates to TRUE,
no matter what numerical value I put into A3
(esp. 0) and what the relationship is between
A1 and A2.

The formula =(A1<=A2)<=A3 does behave as
expected if A3 is FALSE. That is, the formula
result is FALSE when A1A2, and it is TRUE
when A1<=A2.

But since =(A1<=A2)*2 has a numerical result
-- ergo, "(A1<=A2)" is treated as a number
sometimes -- shouldn't =(A1<=A2)<=A3 treat
"(A1<=A2)" as a number when A3 is a number?

How is "A1<=A2<=A3" evaluated?
 

True and False are treated as 1 and 0 for math computations but for logical
comparisons True and 1 are not equal (e.g., entering =1=True in a cell
returns False), nor are False amd 0.

I believe this is related to how Excel has always coerced entries to numbers
for math operations where it could. For example if you enter the string '5
(apostrophe-5) in A1 and the number 5 in A2, =A1=A2 is False yet =A1-A2 is
0.

If you want Excel not to coerce text numbers, so the =A1=A2 returns -5, turn
on Transition Formula Evaluation under Tools, Options, Transition. This is
a setting for compatibility with 1-2-3 or at least the older versions. With
it on 1 and True (and False and 0) are treated as the same thing for all
purposes I believe.

--
Jim
wrote in message
oups.com...
| How does Excel evaluate "A1<=A2<=A3"?
|
| I assume Excel evaluates it as (A1<=A2)<=A3,
| where "(A1<=A2)" results in the boolean value
| TRUE or FALSE.
|
| When I put =(A1<=A2) into C1 and =C1*2 into
| C2, C2 is 2 when A1<=A2 and 0 when A1A2. So
| I infer that TRUE=1 and FALSE=0 in a numerical
| expression.
|
| But =(A1<=A2)<=A3 always evaluates to FALSE,
| and =A1<=(A2<=A3) always evaluates to TRUE,
| no matter what numerical value I put into A3
| (esp. 0) and what the relationship is between
| A1 and A2.
|
| The formula =(A1<=A2)<=A3 does behave as
| expected if A3 is FALSE. That is, the formula
| result is FALSE when A1A2, and it is TRUE
| when A1<=A2.
|
| But since =(A1<=A2)*2 has a numerical result
| -- ergo, "(A1<=A2)" is treated as a number
| sometimes -- shouldn't =(A1<=A2)<=A3 treat
| "(A1<=A2)" as a number when A3 is a number?
|