160 likes | 277 Views
Relative vs. Absolute Addressing in a Spreadsheet. Spreadsheet Address Relative Address Absolute Address Examples. Relative Address. you can copy formulas as well as text or numeric data - what happens to the cell reference (address)
E N D
Relative vs. Absolute Addressing in a Spreadsheet • Spreadsheet Address • Relative Address • Absolute Address • Examples Chapter 1, Unit c
Relative Address • you can copy formulas as well as text or numeric data - what happens to the cell reference (address) • when the formula is copied, the cell references adjust to their new locations because a formula automatically uses a relative cell reference Chapter 1, Unit c
Example of Copying a Formula with Relative Addressing • suppose B7 contains the formula • =B5+B6 • this formula (cell) is copied into C7 • the copied formula in C7 is • =C5 +C6 • in relative terms the formula says use the cells one row up and two rows up Chapter 1, Unit c
Example of Copying a Formula with Relative Addressing • suppose B7 contains the formula • =C6 • this formula (cell) is copied into C7 • the copied formula in C7 is • =D6 • in relative terms the address is one column to the right and one row up Chapter 1, Unit c
Relative address (reference) • the cell address in the formula changes to reflect the relative position • relative is default • be careful when deleting, you could affect formulas referencing the deleted cells Chapter 1, Unit c
Example 1 The formula in A4 is =A2 + A3 Fill A4 to B4 B4: _________________ Copy A4 to C5 C5: _________________ Chapter 1, Unit c
Answer to Example 1 • B4: B2 + B3 • C5: C3 + C4 Chapter 1, Unit c
Absolute Addressing • no adjustment takes place • the position is fixed • since relative is default, Excel must allow you to indicate that an address is absolute. This is done by placing a $ in front of the coordinate you want fixed. Chapter 1, Unit c
Example of Copying Formula with Absolute Addressing • suppose B7 contains the formula • =$A$1 • this formula (cell) is copied into C7 • the copied formula in C7 is • =$A$1 • the formula says the position of the column is absolute and position of the row is absolute Chapter 1, Unit c
Example of Copying Formula with Absolute Addressing • suppose B7 contains the formula • =$A1 • this formula (cell) is copied into C8 • the copied formula in C8 is • =$A2 • the formula says the position of the column is absolute and position of the row is six rows up Chapter 1, Unit c
Example 2 The formula in A4 is = A2 + $D$3 Fill A4 to B4 B4 : _____________________ Copy A4 to C5 C5 : _____________________ Chapter 1, Unit c
Answer to Example 2 • B4: B2 + $D$3 • C5: C3 + $D$3 Chapter 1, Unit c
Additional Examples • The formula in A3 is A2/$A4 • Copy A3 to C3 C3:_____________ • Copy A3 to D2 D2:_____________ • The formula in A3 is A2/A$4 • Copy A3 to C3 C3:_____________ • Copy A3 to D4 D4:_____________ Chapter 1, Unit c
Answers to Additional Examples • The formula in A3 is A2/$A4 • Copy A3 to C3 C3: =C2/$A4 • Copy A3 to D2 D2: =D1/$A3 • The formula in A3 is A2/A$4 • Copy A3 to C3 C3: =C2/C$4 • Copy A3 to D4 D4: =D3/D$4 Chapter 1, Unit c
Questions Chapter 1, Unit c