USING SELECTED LOGICAL FUNCTIONS
Excel has a rich set of logical functions, including some that are included in the Analysis Tool-
Pak add-in. Most logical functions use conditional tests to determine whether a specified
condition is TRUE or FALSE.
-
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 30 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 above
75 and fewer than five unexcused absences. In Figure 14-1, we typed the formula
=IF(AND(G4<5,F4>75), "Pass", "Fail"). This fails the student in row 5 because of the five
absences. If you use OR instead of AND in the formula, all students
would pass.
The sample file used in this example is in the file And Or Not.xls.
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, it is usually used 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: 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 seven nested functions. For example, the following
formula =IF(A1=1, 3, IF(A1=2, 6, IF(A1=3, 5, IF(A1=4, 6, IF(A1=5, 8, IF(A1=6, 7,
IF(A1=7, 0, 1))))))) works just fine in Excel, but if you add one more IF function, you’ll get
an error.
Nested IF formulas are a common conditional test, but the seven-function limit might cause
some problems, especially if you’re converting data from a program that allows a greater
number of nested functions, such as Lotus 1-2-3. You’ll have to either break long formulas
into two cells or approach the task differently, perhaps by using lookup 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