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.

Search JabSto ::

Custom Search