Microsoft Office Tutorials and References
In Depth Information
Using Mixed References
Figure 3-7
Results of formulas with absolute references
absolute reference
to cell D5
monthly income
from Janu ary to May
4. Click each cell in the range D19:G20 and verify that the formulas =\$D\$5 and
=\$D\$6 were copied into the appropriate cells.
Using Mixed References
A formula can also include cell references that are mixed. A mixed reference contains
both relative and absolute references. For example, a mixed reference for cell A2 can
be either \$A2 or A\$2. In the mixed reference \$A2, the column reference to column A is
absolute and the reference to row 2 is relative. In the mixed reference A\$2, the column
reference is relative and the row reference is absolute. As you can see, a mixed reference
“locks” one part of the cell reference while the other part can change. When you copy
and paste a formula with a mixed reference to a new location, the absolute portion of
the cell reference remains ﬁ xed and the relative portion shifts.
Figure 3-8 shows an example of using mixed references to complete a
multiplication table. The ﬁ rst cell in the table, cell B3, contains the formula =\$A3*B\$2, which
multiplies the ﬁ rst column entry (A3) by the ﬁ rst row entry (B2), returning the value 1.
When this formula is copied to another cell, the absolute portions of the cell references
remain unchanged and the relative portions of the references change. For example, if
the formula is copied to cell E6, the ﬁ rst mixed cell reference changes to \$A6 because
the column reference is absolute and the row reference is relative, and the second cell
reference changes to E\$2 because the row reference is absolute and the column
reference is relative. The result is that cell E6 contains the formula =\$A6*E\$2 and returns the
value 16. Other cells in the multiplication table are similarly modiﬁ ed so that each entry
returns the multiplication of the row and column headings.
Search JabSto ::

Custom Search