Microsoft Office Tutorials and References

In Depth Information

**Session 2.2**

Amanda appreciates the type of information the worksheet provides, but she is con-

cerned about getting lost in all of the numbers. She would like the worksheet to display a

simple text message: “Yes” if the loan is affordable given the conditions she has set for the

budget and “No” if otherwise. To add such a feature to the worksheet, you’ll need to use a

Logical function.

Working with Logical Functions

A
Logical function
is a function that tests, or evaluates, whether a condition in the workbook

is true or false. The condition is usually entered as an expression. For example, the expression

A1=10 would be true if cell A1 contains the value 10; otherwise, the expression is false.

Using the IF Function

The most commonly used Logical function is the
IF function
, which has the following syntax:

=IF(
logical_test
,
value_if_true,
[
value_if_false
])

where
logical_test
is an expression that is either true or false,
value_if_true
is the value dis-

played in the cell if the logical test is true, and
value_if_false
is the value displayed if the

logical test is false. Note that the
value_if_false
argument is optional, though in most cases

you will use it so that the function covers both possibilities.

For example, the formula
=IF(A1=10, 20, 30)
tests whether the value in cell A1 is equal to

10. If the expression A1=10 is true, the function displays the value 20 in the cell containing

the function; otherwise, the cell displays the value 30. You can also construct logical tests that

involve text values. The formula
=IF(A1=”Retail”, B1, B2)
tests whether cell A1 contains the

text “Retail”; if it does, the function returns the value of cell B1; otherwise, it returns the value

of cell B2.

Expressions in the logical test always include a comparison operator. A
comparison

operator
indicates the relationship between two values. Figure 2-31 describes the com-

parison operators supported by Excel.

Figure 2-31

Comparison operators

Operator

Example

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

You’ll use the IF function to display a text message in the worksheet indicating whether

a $175,000 loan is affordable. In this case, the logical expression will test whether the

value in cell E14 (the required savings) is less than the value in cell E15 (the calculated

savings). The expression is E14 < E15. If this expression is true, then the loan is affordable

for Amanda’s family; otherwise, it is not. You will now enter the formula that includes the

IF function
=IF(E14 < E15, “Yes”, “No”)
.