Microsoft Office Tutorials and References

In Depth Information

EX 186

Excel Chapter 3
What-If Analysis, Charting, and Working with Large Worksheets

Absolute versus Relative Addressing

The next sections describe the formulas and functions needed to complete the calculations

in the worksheet.

Plan

Ahead

Determine necessary formulas and functions needed.

The next step is to enter the formulas that calculate the following values for January: cost of

goods sold (cell B5), gross margin (cell B6), expenses (range B9:B13), total expenses (cell B14),

and the operating income (cell B16). The formulas are based on the projected monthly sales

in cell B4 and the assumptions in the range B19:B25.

The formulas for each column (month) are the same, except for the reference to the

projected monthly sales in row 4, which varies according to the month (B4 for January, C4 for

February, and so on). Thus, the formulas for January can be entered in column B and then

copied to columns C through G. Table 3–6 shows the formulas for determining the January

costs of goods, gross margin, expenses, total expenses, and operating income in column B.

If the formulas are entered as shown in Table 3–6 in column B for January and then copied

to columns C through G (February through June) in the worksheet, Excel will adjust the cell

references for each column automatically. Thus, after the copy, the February Commission

expense in cell C10 would be =C4 * C20. While the cell reference C4 (February Sales) is

correct, the cell reference C20 references an empty cell. The formula for cell C7 should

read =C4 * B20, rather than =C4 * C20, because B20 references the Commission % value in the

What-If Assumptions table. In this instance, a way is needed to keep a cell reference in a

formula the same, or constant, when it is copied.

Table 3–6 Formulas for Determining Cost of Goods, Margin, Expenses, Total Expenses,

and Operating Income for January

Cell

Row Title

Formula

Comment

B5

Cost of Goods Sold

=B4 * (1
B21)

Sales times (1 minus Margin %)

B6

Gross Margin

= B4
B5

Sales minus Cost of Goods

B9

Bonus

=IF(B4 >= B24, B19, 0)

Bonus equals value in B19 or 0

B10

Commission

=B4 * B20

Sales times Commission %

B11

Marketing

=B4 * B22

Sales times Marketing %

B12

Research and

Development

=B4 * B23

Sales times Research and

Development %

B13

Support, General, and

Administrative

=B4 * B25

Sales times Support, General, and

Administrative %

B14

Total Expenses

=SUM(B9:B13)

Sum of January Expenses

B16

Operating Income

=B6
B14

Gross Margin minus Total Expense

Absolute Referencing

Absolute referencing is

one of the more difﬁ cult

worksheet concepts to

understand. One point to

keep in mind is that the

paste operation is the only

operation affected by an

absolute cell reference.

An absolute cell reference

instructs the paste

operation to keep the

same cell reference as it

copies a formula from one

cell to another.

To keep a cell reference constant when copying a formula or function, Excel uses a

technique called absolute cell referencing. To specify an absolute cell reference in a formula,

enter a dollar sign ($) before any column letters or row numbers you want to keep constant

in formulas you plan to copy. For example, $B$20 is an absolute cell reference, while B20 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$20

instructs Excel to keep the cell reference B20 constant (absolute) in the formula as it copies

it to the destination area. A formula using the
relative cell reference
B20 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
. Table 3–7

gives some additional examples of absolute, relative, and mixed cell references.