# Use of combined comparison operators in Excel formula

How does Excel evaluate combined comparison operators to return either true or false as shown in the image below? For example, the formula `=IF(4<5>6,"true","false")` evaluates to true and `=IF(4<5<6,"true","false")` evaluates to false. • Use AND function: `IF(AND(4<5, 5<6), "true", "false")` May 18 at 6:18
• @Akina I'm very familiar with the AND function, just wanted to know the logic behind the evaluation to produce such results. Thanks. May 18 at 6:22
• Thought I had an answer - was thinking it evaluates the first part 4<5 = True and then True < 6 = True, but the formula results in False. Confused now. The numeric value of True should be 1. You could also get rid of the `IF` statement to use it: `=4<5<6` will return FALSE. May 18 at 6:28
• The numeric value of True should be 1. 1 is the representation of boolean TRUE but not a value. Any boolean is greater than any numeric, TRUE is greater than FALSE - you may check this. May 18 at 6:38
• `=True=1` returns False. Of course, I have pointed to the difference between the value and its representation and boolean to numeric comparing. Convert boolean to numeric then compare `=(True+0)=1` - and you'l obtain True. May 18 at 7:31

A formula `x<y<z` is evaluated step by step, just like `x+y+z`.

Confusingly, Excel has the strange logic that `true` or even `false` is greater than any numeric value (is there some philosophy here?) rather than being numerically equal to `1` or `0` as is more common. However, `=(4<5)+1` still equals `2`.

• `4<5<6` => `(4<5)<6` => `true<6` => `false`
• `4<5>6` => `(4<5)>6` => `true>6` => `true`
• `4>5>6` => `(4>5)>6` => `false>6` => `true`

and so on.

As has already been answered, you'd need `AND()` to evaluate multiple conditions at the same time.

Note that `=IF(condition,"true","false")` is actually redundant. `=condition` yields the same result (unless you need the string type).

• Thanks for this. I really understand it now. May 18 at 16:45
• I don’t know about this particular case, but usually the answer to “why does Excel do XYZ in a formula?” is almost always “because that’s how Lotus 1-2-3 did it” May 19 at 10:36

You can change the formulat to:

``````=IF(AND(4<5,4<6,5<6),"TRUE","FALSE")
=IF(AND(4<5,4<6,5>6),"TRUE","FALSE")
=IF(AND(4>5,4<6,5<6),"TRUE","FALSE")
=IF(AND(4>5,4>6,5>6),"TRUE","FALSE")
`````` True and False can be used in a meaningful way, by "multiplying" them.

On a general level (in programming) one can use e.g:

n=(n<6)*(n+1) ... will loop the values 0 to 6 as this is repeated, example below.

So to conclude; an expression with a conditional DOES evaluate to
to a "1" for true, when used in a multiplication, and "0" for false.

That is: a true 1 and 0 when using `(condition)*1` -> `1*True` => 1, `False*1` => 0

... so `(A1<5)*1` will give a `1` as result if A1 has a value which is less than 5.

```\$ python
Python 3.8.10 (default, Mar 15 2022, 12:22:08)
[GCC 9.4.0] on linux
>>> n=0
>>> for i in range(20):
...   print(i,n)
...   n=(n<6)*(n+1)
...
0 0
1 1
2 2
3 3
4 4
5 5
6 6
7 0
8 1
9 2
10 3
11 4
12 5
13 6
14 0
15 1
16 2
17 3
18 4
19 5
>>>
>>> quit()

\$
```
• Excepting that Excel doesn't wok the same as Python and most other programming languages, so this doesn't answer the OP's question. May 19 at 9:53
• The CONCEPT works the very same as in Python, please pick off your blinders. The Python snippet was to show what the effect is. May 19 at 15:46