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