Logical Functions

We can organize the six Logical functions, listed under Function name in Figure 4.5, into three pairs of related functions: TRUE/ FALSE, IF/ NOT, and AND/ OR.

Formula Types:

Simple values Basic operators

Naming and referencing Functions

Figure 4.5 The six Logical functions: TRUE, FALSE, AND, OR, NOT, and IF.

The TRUE and FALSE functions simply display the words TRUE and FALSE. There are no parameters to enter for this function; we just type the function name:

=TRUE =FALSE

We may want to use these functions in a manual evaluation of our data. For example, we want to determine which numbers are greater than 100 in the list featured in Figure 4.6. We have placed the function =TRUE next to each cell that contains a number larger than 100. When reviewing our list of numbers, we can immediately see which numbers are greater than 100. Alternately, we could place the FALSE function next to the cells with numbers less than 100.

Figure 4.6 The TRUE and FALSE functions display the words True and False,

respectively.

The NOT function contrasts a cell with any other cell, and can be used as long as the cell that we are reviewing has a value of either TRUE or FALSE. Use the following formula to specify the opposing cell:

=NOT(cell_name)

So, if we place the NOT function next to every cell where we previously placed a TRUE function, we should see the result FALSE in each cell. Figure 4.7 illustrates that F8, with a value of FALSE, opposes E8, which is TRUE.

Figure 4.7 The NOT function displays the opposite value of a cell. Since E8 is TRUE,

NOT(E8) is FALSE.

The IF function allows us to evaluate data by using a specified condition that determines whether data is true or false. An IF function requires us to specify three parameters:

condition to be met, return value if true, and return value if false. For example:

=IF(logical_test, value_if_true, value_if_false)

Returning to the data in Figure 4.6, we can easily use the IF function to determine which values are greater than 100. Therefore, in Figure 4.8(a), we have added the IF function to the last column on the right. The formula for the IF function, =IF(C5>100, TRUE,

FALSE), directs the computer to return the value TRUE if C5 is greater than 100. In all

other circumstances, it will return the value FALSE. We can copy this function to the entire column in order to evaluate all of the numbers in the table. Note that we can put any text or numerical value in the return parameters. For example, we could return the words “Big Number” if our number is greater than 100 and “Small Number” if it is less than 100. Likewise, we could display a “1” for all numbers greater than 100 and a “0” for all numbers less than 100. We can even display nothing by placing empty quotation marks “” in the return parameters, as displayed in Figure 4.8(b).

(a)

(b)

Figure 4.8 The IF function takes three parameters: condition to be met, return value

if true, and return value if false. (a) In this example, TRUE is displayed if the number is greater than 100, and FALSE is displayed if it is less than 100. (b) In the second example, nothing is displayed if the condition is not met.

The AND and OR functions return the values TRUE and FALSE depending on certain conditions. For the AND function, all of the conditions listed must be true in order for

TRUE to be displayed. If any of the conditions are violated, FALSE is returned. The

format for the AND function is:

=AND(condition1, condition2, …)

We can have up to 30 conditions. For the OR function, only one of the conditions needs to be true for TRUE to be the result, while all of the conditions would have to be violated for FALSE to be displayed. The OR function uses a similar format:

=OR(condition1, condition2, …)

In Figure 4.9(a), we have added the AND function to evaluate whether or not all of the numbers in our table are greater than 100. Our AND function requires every cell to be greater than 100:

This function results in FALSE because some of the numbers in the data set do not match the conditions of this function. In Figure 4.9(b), by contrast, we have paired the same conditions with an OR function. Because only one of the conditions needs to be met, this function results in TRUE since only one of the conditions needs to be met.

(a)

(b)

Figure 4.9 The AND and OR functions evaluate several conditions. The AND

function requires all conditions to be met in order to return TRUE, while OR requires only one condition to be met to be TRUE. (a) The AND function returns FALSE because not every number in the table is greater than 100. (b) The OR function returns TRUE because at least one number in the table is greater than 100.

The IF, AND, and OR functions are very useful in advanced applications. For some problems, there may be several complex conditions that need to be checked. These functions can be used to evaluate data to ensure that those conditions are met and to signal which data violates those conditions.