Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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? | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum Count of Single Criteria in Multiple Non-Adjacent columns | Excel Worksheet Functions | |||
sumproduct bombs out | Excel Worksheet Functions | |||
the cell currently being evaluated contains a constant | Excel Worksheet Functions | |||
Can Excel represent formula in textural format with values substi. | Excel Worksheet Functions |