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.