Microsoft Office Tutorials and References

In Depth Information

**Working with Logical Functions**

where
logical_test
is a statement that is either true or false,
value_if_true
is the value

returned by the IF function if the statement is true, and
value_if_false
is the value

returned by the function if the statement is false. For example, the following formula tests

whether the value in cell A1 is equal to the value in cell B1. If it is, the formula returns a

value of 100; otherwise it returns a value of 50.

=IF(A1=B1, 100, 50)

In many cases, however, you will not use values directly in the IF function. The

following formula uses cell references, returning the value of cell C1 if A1 equals B1;

otherwise, it returns the value of cell C2:

=IF(A1=B1,ƒC1,ƒC2)

The = symbol in these formulas is a comparison operator. A
comparison operator
is

a symbol that indicates the relationship between two values. Figure 3-31 describes the

comparison operators that can be used within a logical function.

Figure 3-31

Comparison operators

Operator

Statement

Description

=

A1 = B1

Tests whether the value in cell A1
is equal to
the value in cell B1

>

A1 > B1

Tests whether the value in cell A1
is greater than
the value in cell B1

<

A1 < B1

Tests whether the value in cell A1
is less than
the value in cell B1

>=

A1 >= B1

Tests whether the value in cell A1
is greater than or equal to
the

value in cell B1

<=

A1 <= B1

Tests whether the value in cell A1
is less than or equal to
the value in

cell B1

<>

A1 <> B1

Tests whether the value in cell A1
is not equal to
the value in cell B1

The IF function also works with text. For example, the following formula tests whether

the value of cell A1 is equal to YES. If the value of cell A1 is equal to YES, the formula

returns the text DONE; otherwise, it returns the text RESTART.

=IF(A1=”YES”, “DONE”, “RESTART”)

In addition, you can nest other functions inside an IF statement. The following formula

ﬁ rst tests whether cell A5 is equal to the maximum of values within the range A1:A100. If

it is, the formula returns the text “Maximum”; otherwise, it returns no text.

=IF(A5=MAX(A1:A100), “Maximum”,””)

In Diane and Glenn’s budget, you will use an IF function for each month to test

whether the net cash ﬂ ow for that month is $1,000 or greater. If it is, Diane wants to

transfer some of it into the home savings account. On the other hand, if the net cash ﬂ ow

is less than $1,000, she does not want to transfer any of it into the home account. You’ll

start creating this function by entering the minimum net cash ﬂ ow into the worksheet.

To specify the minimum net cash flow:

◗

1.
In cell H11, enter the label
Minimum required net cash flow
.

◗

2.
In cell L11, enter
1000
. This is the minimum cash flow amount.

◗

3.
Use the Format Painter to copy the formatting from the range
H10:L10
to the

range H11:L11.