Microsoft Office Tutorials and References
In Depth Information
Making Decisions — The IF Function
formula assigned
to cell B14 appears
in formula bar
2
Click the Enter box in the formula
bar to display the result, 1323285.168,
instead of the formula in B14
(Figure 3 – 26).
projected July cost
of goods sold
Figure 3 –26
formula appears
in formula bar
3
Click cell B15 to select the cell in
which to enter the next formula,
type = (equal sign), click cell B13,
type (minus sign), and then click
cell B14 to add a reference to the
cell to the formula.
Click the Enter box in the formula
bar to display the result in the
selected cell, in this case gross
margin for July, 1790326.992, in
cell B15 (Figure 3 – 27).
active cell shows
result of formula
Figure 3 –27
Making Decisions — The IF Function
According to the Request for New Workbook in Figure 3 – 2 on page EX 140, if the
projected July revenue in cell B13 is greater than or equal to the sales revenue for bonus
in cell B7 (3,500,000.00), then the July bonus value in cell B18 is equal to the bonus
value in cell B2 (200,000.00); otherwise, cell B18 is equal to 0. One way to assign the
July bonus value in cell B18 is to check to see if the revenue in cell B13 equals or exceeds
the sales revenue for the bonus amount in cell B7 and, if so, then to enter 200,000.00 in
cell B18. You can use this manual process for all six months by checking the values for the
corresponding months.
Because the data in the worksheet changes each time a report is prepared or the
i 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 B18 must include a
formula or function that displays 200,000.00 or 0.00 (zero), depending on whether the
projected July revenue in cell B13 is greater than, equal to, or less than the sales revenue
for bonus value in cell B7.
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 B18 can be assigned the following
IF function:
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(A1>C1, B1<C2),
“OK”, “Not OK”) and
=IF(OR(K5>J5, C3<K6),
“OK”, “Not OK”) and
=IF(NOT(B10<C10), “OK”,
“Not OK”) use logical
operators. In the i 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 B10<C10 must be false
for the value_if_true OK to
be assigned to the cell.
=IF(B13>=\$B\$7, \$B\$2, 0)
logical_test value_if_true value_if_false

Search JabSto ::

Custom Search