Absolute and Relative Cell addressing Explained

Document Sample

```					Absolute and Relative Cell addressing Explained.

All formula in Excel should use cell addresses that reference the values you wish to
perform calculations upon so that when changes are made to values in those cells,
formula referencing the cells will automatically recalculate correct results.

Relative and absolute cell addressing facilitate the copying of formula and functions that
use cell addresses. When using relative cell addresses (the default) in Excel formula, the
cell addresses in the formula will adjust accordingly as the formula is copied through the
worksheet. For each row that the formula is copied down the worksheet, the row number
of each relative cell address in the formula will increase by one, relative to the original
location of the formula. For each column the formula is copied to the right across the
worksheet, the column letter of each relative cell address in the formula will increase by
one, relative to the original location of the formula.

For example:
A              B                C               D              E
1 NAME              HOURS            HOURLY          GROSS
RATE            PAY
2   FRED              40             8.00            =B2*C2         Formula copied
from D2 through
D5
3   JOE               40             9.00            =B3*C3
4   SUE               42             9.50            =B4*C4
5   MARY              38             8.25            =B5*C5

In the gross pay column examine our formula to calculate Grosspay. All cell addresses
used in the formula are relative cell addresses, therefore they will adjust accordingly as
the formula is copied from cell D2 through cell D5. Notice that the row numbers
increase by one in each cell address as the formula is copied down the rows of the sheet.
This occurs because each person has their own number of hours worked and their own
hourly rate and this must be reflected in the formula to produce the correct results. Since
entering the formula for each employee would be too time consuming if you had 20 or
more employees, excel allows you to enter the formula once at the top of the column and
copy the formula down the column or across the row as needed while the relative cell
addresses change in the formula, relative to the original positio n of the formula in the
spreadsheet. To see other examples of Relative and Absolute cell addresses, open this
link If Function Pass Fail and Two Tax Rates Examples and examine the formula
can manipulate, right click the link and select save target as. Save the file to your
computer system and select open, when prompted. Simply clicking on the link may
only allow you to view the contents of the sheet.
A              B                  C               D              E               F
1                  TAX RATE           37%
2 NAME             HOURS              HOURLY          GROSS          TAXES
RATE            PAY
3    FRED             40              8.00            =B3*C3         =D3*\$C\$1        Formula copied
from E3 through E6
4    JOE              40              9.00            =B4*C4         =D4*\$C\$1
5    SUE              42              9.50            =B5*C5         =D5*\$C\$1
6    MARY             38              8.25            =B6*C6         =D6*\$C\$1

In this example we demonstrate both relative and absolute cell addresses being used in
Excel formula at the same time. You can identify an absolute cell address in a formula
by the \$ that surround the cell address in the formula. Absolute cell addresses are used in
a formula when you want a cell address to remain constant while the formula is copied
through a spreadsheet. In this example the tax rate is the same for each employee, so
instead of entering the tax rate multiple times in the sheet we enter it once and refer to the
cell that contains the tax rate as absolute in all formula that reference the tax rate. As
shown in column E above. Notice each employee has a gross pay cell. When calculating
taxes we want the individuals gross pay taxed at the tax rate, therefore the grosspay cell
address is relative in the taxes formula so it can change relative to the position of the
formula in the sheet. The tax rate is always in cell address C1, so in order to keep that
cell address constant while the formula is copied from E3 to E6 we make C1 absolute in
the formula BEFORE we copy the formula. The absolute key is F4. It can be used to
add \$ to a cell address in a formula while entering or editing the formula. To see other
examples of Absolute and Relative cell addresses, open this link If Function Pass Fail and
Two Tax Rates Examples and examine the formula contained within the spreadsheets.
Note: to open links as excel spreadsheets that you can manipulate, right click the
link and select save target as. Save the file to your computer system and select open,
when prompted. Simply clicking on the link may only allow you to vie w the contents
of the sheet.

```
DOCUMENT INFO
Shared By:
Categories:
Stats:
 views: 235 posted: 9/2/2010 language: English pages: 2
Jun Wang Dr
About Some of Those documents come from internet for research purpose,if you have the copyrights of one of them,tell me by mail vixychina@gmail.com.Thank you!