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