What-If Analysis, Charting, And Working With Large Worksheets Page 29
ADVERTISEMENT
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87What-If Analysis, Charting, and Working with Large Worksheets
Excel Chapter 3
EX 165
The IF function instructs Excel that, if the projected July revenue in cell B13 is greater
than or equal to the sales revenue for bonus value in cell B7, then Excel should display the
value 200000 in cell B2, in cell B18. If the projected July revenue in cell B13 is less than the
sales revenue for bonus value in cell B7, then Excel displays a 0 (zero) in cell B18.
The general form of the IF function is:
=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
Meaning
Example
Operator
=
Equal to
=IF(B12 = 200, F3 * H4, E10 + F3)
<
Less than
=IF(G56 * Q56 < D12, $M$10, B9 ^ 5)
>
Greater than
=IF(MIN(A12:A52) > 75, 0, 1)
>=
Greater than or equal to
=IF($T$9 >= B7, P3 - H12, 1000)
<=
Less than or equal to
=IF(C9 * G2 <= 99, $T35, 350 * C9)
<>
Not equal to
=IF(G15 <> 1, “No”,“Yes”)
To Enter an IF Function
The following steps assign the IF function =IF(B13>=$B$7,$B$2,0) to cell B18. This IF function determines
whether or not the worksheet assigns a bonus for July.
1
•
Click cell B18 to select
the cell for the next
formula.
Insert Function
dialog box
•
Click the Insert
Function box in the
formula bar to display
the Insert Function
‘Or select a
dialog box.
category’
box arrow
•
Click the ‘Or select a
category’ box arrow
IF function
selected
(Insert Function dialog
box) and then select
description
Logical in the list to
of selected
cell B18 selected
function
populate the ‘Select
a function’ list with
OK button
logic functions.
•
Click IF in the ‘Select a
function list’ to select
the required function
(Figure 3 – 28).
Figure 3 –28
ADVERTISEMENT
0 votes
Related Articles
Related forms
Related Categories
Parent category: Education