Microsoft Office Tutorials and References
In Depth Information
Understanding logical functions
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 ; it
returns 200 if A1 contains any other entry. The match between the two text entries must be
exact except for case.
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 use 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 according to 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 Ye s . This performs all
the calculations you need in one cell and eliminates having to create a column of IF
formulas. For more information about SUMIF, see “The SUMIF, SUMIFS, and COUNTIF
functions” later in this chapter.
The AND, OR, and NOT functions
These three functions help you develop compound conditional tests. They 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-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 shown in Figure 14-1, all
students would pass.