5

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.

enter image description here

Improve this question
6
  • Use AND function: IF(AND(4<5, 5<6), "true", "false")
    – Akina
    May 18 at 6:18
  • 1
    @Akina I'm very familiar with the AND function, just wanted to know the logic behind the evaluation to produce such results. Thanks.
    – Jovanny
    May 18 at 6:22
  • 2
    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.
    – Darren Bartrup-Cook
    May 18 at 6:28
  • 1
    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.
    – Akina
    May 18 at 6:38
  • 3
    =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.
    – Akina
    May 18 at 7:31

3 Answers 3

Reset to default
15

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).

Improve this answer
2
  • Thanks for this. I really understand it now.
    – Jovanny
    May 18 at 16:45
  • 2
    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”
    – Euro Micelli
    May 19 at 10:36
2

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")

enter image description here

Improve this answer
1

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
Type "help", "copyright", "credits" or "license" for more information.
>>> 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()

$
Improve this answer
2
  • Excepting that Excel doesn't wok the same as Python and most other programming languages, so this doesn't answer the OP's question.
    – Reversed Engineer
    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.
    – Hannu
    May 19 at 15:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.