Relative And Absolute Cell Addresses

Exercise 3

Relative and Absolute Cell Addresses

The purpose of this exercise is to differentiate between the use relative cell addresses and absolute cell addresses. •

A3 is an example of a relative cell address o When the cell address is used in a formula and copied to a new location it will adjust to a different address based upon the new location

•

$A$3 is an example of an absolute cell address o When the cell address is used in a formula and copied to a new location it will NOT change based upon the new location o Absolute cell addresses should be used when a cell address is constant in the formula regardless of where copied

Formatting Instructions:

1. Start a new spreadsheet file. Key the values and labels in cells A1:E16 as shown. Increase column A column width.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

A TDSB Weekly Payroll Employee Name: Hourly Rate: Day Monday Tuesday Wednesday Thursday Friday

B

C

D

Gross Pay

Deductions 0.1

E

12 Hours Worked

Net Pay

8 7 8 6 4

Total Net Pay Average Net Pay

2. Center the spreadsheet title across columns A through E in row 1. Change the font style and increase the font size. 3. In cell B3 key your name. 4. Cell Formatting: • Highlight cells A6 through E6 - vertically and horizontally center and wrap the text of the column headings - adjust the column widths to show the wrap text feature for the Hours Worked column heading and ensure that complete words appear on one line • Change the value formatting of cell B4 to currency with two decimal places • Change the value formatting of cell D7 to percent with zero decimal places • Add borders to the spreadsheet

Spreadsheet Calculation Instructions:

5. To calculate Gross Pay for each day of the week the formula is Hours Worked multiplied by Hourly Rate. Since the hours worked value changes every day it will be set as a relative cell address in the formula. However, the hourly rate is constant so it will be set as an absolute cell address in the formula.

•

•

In cell C8 key =, use the mouse to click cell B8 (relative cell address), key *, use the mouse to click cell B4 (absolute cell address), press the function key F4 to set cell B4 as absolute and press enter. The formula =B8 * $B$4 will appear in the formula bar and the answer will appear in cell C8 Copy the formula down to cells C9 through C12. Notice on the formula bar that cell $B$4 did not change as it is an absolute cell address but cell B8 did as it is a relative cell address

6. To calculate Deductions for each day of the week the formula is Gross Pay (set as a relative cell address) multiplied by the deduction rate located in cell D7 (set as an absolute cell address). • In cell D8 create a formula using both a relative and absolute cell address • Copy the formula down to cells D9 through D12 •

Change the value format for cells D8 through D12 to currency with two decimals

7. To calculate Net Pay for each day of the week the formula is Gross Pay minus Deductions • In cell E8 create a formula using only relative cell addresses • Copy the formula down to cells E9 through E12

8. To calculate Total Net Pay for the week use the AutoSum function to add cells E8 through E12 • In cell E14 create the function •

The correct answer is $356.40

9. To calculate Average Net Pay for the week use the Average function to average the values in cells E8 through E12 • In cell E16 use the arrow down key beside the AutoSum button and select the Average command • The average function now reads =AVERAGE(E14:E15) using the mouse highlight cells E8 through E12 and press enter – the function should now read =AVERAGE(E8:E12) •

The correct answer is $71.28

10. Save the spreadsheet as ex03 on your home drive. 11. Add a footer