3. Logical functions

---

About logical values

A logical value is the result of a test or comparison. There are two logical values TRUE or FALSE.

How to write a logical value in Excel?

To write the value TRUE in Excel :

• write TRUE in upper or lower case
• write a number other than 0

To write the value FALSE in Excel :

• write FALSE in upper or lower case
• write the number 0

How to get a logical value from an expression?

The most common way to get a logical value is to use a comparison operation. The comparison operators are :

Comparison operator	Meaning	Example	Result
=	Equal to	5=3	FALSE
>	Greater than	5>3	TRUE
<	Less than	5<3	FALSE
>=	Greater than or equal to	5>=3	TRUE
<=	Less than or equal to	5<=3	FALSE
<>	Different from	5<>3	TRUE

A logical value can also be obtained by combining two logical values. This is the case, for example, when we want to test whether two conditions are TRUE or whether one or the other is TRUE.

But in Excel there are no logical operators. Instead, we use the logical functions AND and OR.

IF Function

The IF function evaluates the first argument which must be a logical value. If the value of this first argument is true, then IF returns the second argument. Otherwise, IF returns the third argument.

Syntax

IF(logical_test,value_if_true,value_if_false)

Arguments

logical_test: a logical value that the IF function must evaluate
value_if_true : the value returned by the function if the logical_test argument is true
value_if_false : the value returned by the function if the logical_test argument is false

Result

The IF function returns the value value_if_true if logical_test is true, otherwise the IF function returns the value value_if_false

Example

Let's say we have a spreadsheet where we record the total price of orders. We want to calculate the discount for each order according to the following rule: If the total price is more than 5,000, then the discount is 10% of the total price. If the total price is less than 5,000, then no discount is given.

The formula in C2 is : "=IF(B2>5000,B2*0.1,0)"

AND and OR functions

The AND and OR functions take two or more arguments

• AND returns TRUE if all arguments are TRUE
• OR returns TRUE if at least one argument is TRUE

Syntax

AND(logical1,logical2,...)
OR(logical1,logical2,...)

Arguments

logical : logical value to be evaluated

Result

The AND function returns TRUE if all arguments are TRUE, and returns FALSE if at least one argument is FALSE
The OR function returns TRUE if at least one argument is TRUE, and returns FALSE if all arguments are FALSE

Example

The following example shows the so-called Truth tables of AND and OR

Exercises

Exercise 1 - Logical functions

Exercise 2 - Logical functions

---