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”) .

Search JabSto ::

Custom Search