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’’)