Microsoft Office Tutorials and References

In Depth Information

**Absolute versus Relative Addressing**

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

and Operating Income for July

Cell

Row Title

Formula

Comment

B14

Cost of Goods Sold

=B13 * (1 – B4)

Revenue times (1 minus Margin %)

B15

Gross Margin

= B13 – B14

Revenue minus Cost of Goods Sold

B18

Bonus

=IF(B13 >= B7, B2, 0)

Bonus equals value in B2 or 0

B19

Commission

=B13 * B3

Revenue times Commission %

B20

Marketing

=B13 * B5

Revenue times Marketing %

B21

Research and Development

=B13 * B6

Revenue times Research and

Development %

B22

Support, General, and

Administrative

=B13 * B8

Revenue times Support, General,

and Administrative %

B23

Total Expenses

=SUM(B18:B22)

Sum of July Expenses

B25

Operating Income

=B15 – B23

Gross Margin minus Total Expenses

As the formulas are entered as shown in Table 3 – 7 in column B for July and then copied to columns C

through G (August through December) in the worksheet, Excel will adjust the cell references for each column

automatically. Thus, after the copy, the August Commission expense in cell C19 would be =C13 * C3. While the cell

reference C13 (February Revenue) is correct, the cell reference C3 references an empty cell. The formula for cell

C7 should read =C13 * B3, rather than =C13 * C3, because B3 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.

The following steps enter the cost of goods formula = B13*(1 – $B$4) in cell B14 using Point mode. To enter

an absolute cell reference, you can type the dollar sign ($) as part of the cell reference or enter it by pressing f4 with

the insertion point in or to the right of the cell reference to change it to absolute.

1

•

CTRL
+
HOME
to select cell B13

and then click cell B14 to show cell

B13 and to select the cell in which to

enter the i rst formula.

Press

Enter box

formula appears

in formula bar

•

=
(equal sign), select cell B13,

type
*(1–b4
to continue entering

the formula, and then press
F
4
to

change the most recently typed cell

reference, in this case cell b4, from a

relative cell reference to an absolute

cell reference. Type
)
to complete

the formula (Figure 3 – 25).

Is an absolute reference required in

this formula?

No, because a mixed cell reference

could have been used. The formula

in cell B14 will be copied across

columns, rather than down rows. So, the formula entered in cell B14 in Step 1 could have been

entered as =B13*(1–$B4), rather than =B13*(1–$B$4). That is, the formula could have included

the mixed cell reference $B4, rather than the absolute cell reference $B$4. When you copy a

formula across columns, the row does not change anyway. The key is to ensure that column B

remains constant as you copy the formula across columns. To change the absolute cell

reference to a mixed cell reference, continue to press the
F4
key until you achieve the desired

cell reference.

Type

cell $B$4 is absolute

cell B13 is relative

Figure 3 –25