Understanding Logical Functions

You use logical functions to test for specific conditions. These functions are often called logical operators in discussions of Boolean logic, which is named after George Boole, the British mathematician. You might have run across logical operators in set theory, used when teaching logical concepts in high school. You use logical operators to arrive at one of two conclusions: TRUE or FALSE. We’ll discuss the most useful logical functions in the following sections. You can access the logical functions by clicking the Logical button on the Formulas tab on the Ribbon.

Using Selected Logical Functions

Excel has a rich set of logical functions. Most logical functions use conditional tests to determine whether a specified condition is TRUE or FALSE.

For more information about conditional tests, see “Creating Conditional Tests” on page 444.

Inside Out-Streamline Formulas Using the SUMIF Function 

If you find yourself frequently using the IF function to perform conditional tests on individual rows or columns and then using the SUM function to total the results, the SUMIF function might make your work a little easier. With SUMIF, you can add specific values in a range, based on a criterion you supply. For example, you can type the formula =SUMIF(C12:C27, "Yes", A12:A27) to find the total of all numbers in A12:A27 in which the cell in the same row in column C contains the word Yes. This performs all the calculations you need in one cell and eliminates having to create a column of IF formulas.

The IF Function

The IF function returns values based on supplied conditional tests. It takes the arguments (logical_test, value_if_true, value_if_false). For example, the formula =IF(A6<22, 5, 10) returns 5 if the value in cell A6 is less than 22; otherwise, it returns 10. You can nest other functions within an IF function. For example, the formula =IF(SUM(A1:A10)>0, SUM(A1:A10), 0) returns the sum of A1 through A10 if the sum is greater than 0; otherwise, it returns 0.

You can also use text arguments to return nothing instead of zero if the result is false. For example, the formula =IF(SUM(A1:A10)>0, SUM(A1:A10)," ") returns a null string (" ") if the conditional test is false. The logical_test argument can also consist of text. For example, the formula =IF(A1="Test", 100, 200) returns the value 100 if cell A1 contains the string Test or returns 200 if it contains any other entry. The match between the two text entries must be exact except for case.

The AND, OR, and NOT Functions

Three additional functions help you develop compound conditional tests: AND, OR, and NOT. These functions work with the logical operators =, >, <, >=, <=, and <>. The AND and OR functions can each have as many as 255 logical arguments. The NOT function takes only one argument. Arguments can be conditional tests, arrays, or references to cells that contain logical values.

Suppose you want Excel to return the text Pass only if a student has an average score greater than 75 and fewer than five unexcused absences. In Figure 14–8, we typed the formula =IF(OR(G4<5,F4>75), "Pass", "Fail"). This fails the student in row 5 because of the five absences. If you use AND instead of OR in the formula shown in Figure 14–8, all students would pass.

Figure 14–8: You can create complex conditional tests using the OR function.

You’ll find the And Or Not.xlsx file in the Sample Files section of the companion CD.

The OR function returns the logical value TRUE if any one of the conditional tests is true; the AND function returns the logical value TRUE only if all the conditional tests are true.

Because the NOT function negates a condition, you usually use it with other functions. NOT instructs Excel to return the logical value TRUE if the argument is false or the logical value FALSE if the argument is true. For example, the formula =IF(NOT(A1=2), "Go", " ") tells Excel to return the text Go if the value of cell A1 is anything but 2.

Nested IF Functions

Sometimes you can’t resolve a logical problem using only logical operators and the AND, OR, and NOT functions. In these cases, you can nest IF functions to create a hierarchy of tests. For example, the formula =IF(A1=100, "Always", IF(AND(A1>=80, A1<100), "Usually", IF(AND(A1>=60, A1<80), "Sometimes", "Who cares?"))) states, in plain language, the following: If the value is 100, return Always; if the value is from 80 through 99, return Usually; if the value is from 60 through 79, return Sometimes; or finally, if none of these conditions is true, return Who cares?. You can create formulas containing up to 64 levels of nested functions.

Other Uses for Conditional Functions

You can use all the conditional functions described in this section as stand-alone formulas. Although you usually use functions, such as AND, OR, NOT, ISERROR, ISNA, and ISREF, within an IF function, you can use formulas, such as =AND(A1>A2, A2<A3), also to perform simple conditional tests. This formula returns the logical value TRUE if the value in A1 is greater than the value in A2 and the value in A2 is less than the value in A3. You might use this type of formula to assign TRUE and FALSE values to a range of numeric database cells and then use the TRUE and FALSE conditions as selection criteria for printing a specialized report.