Microsoft Office Tutorials and References
In Depth Information
Making Decisions — The IF Function
EX 189
Making Decisions — The IF Function
According to the Request for New Workbook in Figure 3–2 on page EX 164, if the projected
January sales in cell B4 is greater than or equal to the revenue for bonus in cell B24
(4,750,000.00), then the January bonus value in cell B9 is equal to the bonus value in
cell B19 (100,000.00); otherwise, cell B9 is equal to 0. One way to assign the January bonus
value in cell B9 is to check to see if the sales in cell B4 equal or exceed the revenue for
bonus amount in cell B24 and, if so, then to enter 100,000.00 in cell B9. You can use this
manual process for all six months by checking the values for the corresponding month.
Because the data in the worksheet changes each time a report is prepared or the
ﬁ gures are adjusted, however, it is preferable to have Excel assign the monthly bonus to
the entries in the appropriate cells automatically. To do so, cell B9 must include a formula
or function that displays 100,000.00 or 0.00 (zero), depending on whether the projected
January sales in cell B4 is greater than, equal to, or less than the revenue for bonus value
in cell B24.
The IF function is useful when you want to assign a value to a cell based on a
logical test. For example, using the IF function, cell B9 can be assigned the following IF
function:
=IF(B4>=\$B\$24, \$B\$19, 0)
logical_test value_if_true value_if_false
The IF function instructs Excel that, if the projected January sales in cell B4 is
greater than or equal to the revenue for bonus value in cell B24, then Excel should display
the value 100000 in cell B19, in cell B9. If the projected January sales in cell B4 is less than
the revenue for bonus value in cell B24, then Excel displays a 0 (zero) in cell B9.
The general form of the IF function is:
Logical Operators in
IF Functions
IF functions can use logical
operators, such as AND,
OR, and NOT. For example,
the three IF functions
=IF(AND(B3>C3, D3<C5),
“OK”, “Not OK”) and
=IF(OR(C3>G5, D2<X3),
“OK”, “Not OK”) and
=IF(NOT(A6<H7), “OK”,
“Not OK”) use logical
operators. In the ﬁ rst
example, both logical
tests must be true for
the value_if_true OK to
be assigned to the cell.
In the second example,
one or the other logical
tests must be true for the
value_if_true OK to be
assigned to the cell. In the
third example, the logical
test A6<H7 must be false
for the value_if_true OK
to be assigned to the cell.
=IF(logical_test, value_if_true, value_if_false)
The argument, logical_test, is made up of two expressions and a comparison operator.
Each expression can be a cell reference, a number, text, a function, or a formula. Valid
comparison operators, their meaning, and examples of their use in IF functions are shown
in Table 3–8. The argument, value_if_true, is the value you want Excel to display in the
cell when the logical test is true. The argument, value_if_false, is the value you want Excel
to display in the cell when the logical test is false.
Table 3–8 Comparison Operators
Comparison
Operator
Meaning
Example
=
Equal to
=IF(H7 = 0, J6 ^ H4, L9 + D3)
<
Less than
=IF(C34 * W3 < K7, \$K\$6, L33 - 5)
>
Greater than
=IF(MIN(K8:K12) > 75, 1, 0)
>=
Greater than or equal to
=IF(P8 >= \$H\$6, J7 / V4, 7.5)
<=
Less than or equal to
=IF(G7 - G2 <= 23, L\$9, 35 / Q2)
<>
Not equal to
=IF(B1 <> 0, ‘’No’’,’’Yes’’)
Search JabSto ::

Custom Search