Unlike
relative references, absolute references do not change columns or rows when
we copy function from one cell to another or using Auto fill. You can use an absolute
reference to fix (constant) a row and/or column . we
use dollar sign ($)we use dollar sign ($) to fix(constant) the values of row or
column or both of the as we illustrated in Lesson 21
The
dollar sign in an excel cell allow you to fix either row or column or both of
them on any cell reference by preceding the column or row with dollar sign , we
can use it in this form :
$A2 this fix
(constant) column A if you copy the cell or function to another one or if you drag the function like we
studied before in
A$2 this fix
(constant) row 2 if you copy the cell or function to another one or if you drag
the function like we studied before in
$A$2 this
fix (constant) row 2 and column A
For Example :
the table in figure illustrate 4 companies (A,B,C and D)
sold units in Jan ,feb and March
and the price of the units sold for each company
we want to calculate the total revenue (quantity *price per unit ) for
companies A and C
then , we need to make the price constant for companies A
and C in jan , feb and march and let the
quantity changes when month change
so in cell B7 we will write the function
=G2 *B3
Which is equal (the price * the quantity ) , this
function calculate the right result , But , if we auto fill the
function to the right side to calculate the total revenue in Feb. and March we
will get wrong results because we have to make the price (G2) constant in
three months
We will precede both column and row with dollar sign ($) to constant the price of the unit in
company A
And then auto fill Feb and March
In the previous example we use both relative and absolute reference
in cell G2 function (=$G$2 *B3)
B3 : relative cell reference changes with auto fill
process
$G$2 : Absolute cell reference doesn’t change with the
auto fill process and fix (constant) the value of column and row
Comments
Post a Comment