What-If Analysis, Charting, And Working With Large Worksheets Page 26
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
87EX 162
Excel Chapter 3
What-If Analysis, Charting, and Working with Large Worksheets
Absolute versus Relative Addressing
Your Age in Days
How many days have you
been alive? Enter today’s
The next sections describe the formulas and functions needed to complete the calculations
date (e.g., 12/5/2012) in
in the worksheet.
cell A1. Next, enter your
birth date (e.g., 6/22/1996)
As you learned in Chapters 1 and 2, Excel modifi es cell references when copying
in cell A2. Select cell A3
formulas. While copying formulas, however, sometimes you do not want Excel to change
and enter the formula =
cell references. To keep a cell reference constant when copying a formula or function, Excel
A1 – A2. Format cell A3 to
uses a technique called absolute cell referencing. To specify an absolute cell reference in a
the General style using the
Format Cells dialog box,
formula, enter a dollar sign ($) before any column letters or row numbers you want to keep
and cell A3 will display
constant in formulas you plan to copy. For example, $B$4 is an absolute cell reference,
your age in days.
whereas B4 is a relative cell reference. Both reference the same cell. The difference
becomes apparent when they are copied to a destination area. A formula using the absolute
cell reference $B$4 instructs Excel to keep the cell reference B4 constant (absolute) in the
formula as it copies it to the destination area. A formula using the relative cell reference
B4 instructs Excel to adjust the cell reference as it copies it to the destination area. A cell
reference with only one dollar sign before either the column or the row is called a mixed
cell reference. When planning formulas, be aware of when you need to use absolute,
relative, and mixed cell references. Table 3–6 gives some additional examples of each of
these types of cell references.
Table 3–6 Examples of Absolute, Relative, and Mixed Cell References
Cell Reference
Type of Reference
Meaning
$B$4
Absolute cell reference
Both column and row references remain the same when you
copy this cell, because the cell references are absolute.
B$4
Mixed reference
This cell reference is mixed. The column reference changes when
you copy this cell to another column because it is relative. The
row reference does not change because it is absolute.
Absolute Referencing
Absolute referencing is
$B4
Mixed reference
This cell reference is mixed. The column reference does not
one of the more diffi cult
change because it is absolute. The row reference changes when
worksheet concepts to
you copy this cell reference to another row because it is relative.
understand. One point to
B4
Relative cell reference
Both column and row references are relative. When copied to
keep in mind is that the
another cell, both the column and row in the cell reference are
paste operation is the only
adjusted to refl ect the new location.
operation affected by an
absolute cell reference.
An absolute cell reference
instructs the paste
The next step is to enter the formulas that calculate the following values for July:
operation to keep the
cost of goods sold (cell B14), gross margin (cell B15), expenses (range B18:B22), total
same cell reference as it
expenses (cell B23), and the operating income (cell B25). The formulas are based on the
copies a formula from one
projected monthly revenue in cell B13 and the assumptions in the range B2:B8.
cell to another.
To Enter a Formula Containing Absolute Cell References
The formulas for each column (month) are the same, except for the reference to the projected monthly revenue
in row 13, which varies according to the month (B13 for July, C13 for August, and so on). Thus, the formulas for
July can be entered in column B and then copied to columns C through G. Table 3 –7 shows the formulas for deter-
mining the July cost of goods sold, gross margin, expenses, total expenses, and operating income in column B.
ADVERTISEMENT
0 votes
Related Articles
Related forms
Related Categories
Parent category: Education