Spreadsheets Relative & Absolute Addresses

1. SpreadsheetsRelative & Absolute Addresses CIL 102 ‘08 Fall

2. Relative & Absolute Addresses • The concept of relative and absolute addresses becomes relevant when a spreadsheet user builds spreadsheets and copies formulas • Copying formulas is frequently performed as a part of fundamental process

3. Simple Relative Address Example Compute perimeter and area of rectangles • Formulas • P = 2*(L+W) • A = L*W • Use labels to set up your table

4. Simple Relative Address Example Compute perimeter and area of rectangles • Place perimeter formula in the first row • Place area formula in the first row

5. Simple Relative Address Example Compute perimeter and area of rectangles • Highlight the two formulas and select Copy • Copy them to the remaining slots in the two columns by highlighting the area and select Paste

6. Simple Relative Address Example Compute perimeter and area of rectangles • As you move the cursor from formula to formula, not how the references to the length and width are adjusted, relative to the formula. • The formulas in row 4 reference the length and width in row 4, the formulas in rows 5, reference the values in row 5, and so forth.

7. Simple Relative Address Example • As long as the addresses associated to a formula change as the formula location changes, then relative addressing is fine • RULE OF THUMB Normally, formula references within a table are relative addressed as long as data and formulas are in rows or columns. • Use a color highlight for each table to guide you.

8. Simple Absolute Address Example • To illustrate absolute addressing, consider this variation of the perimeter/area table where the length is fixed. • Perimeter and area change with length.

9. Simple Absolute Address Example • Note that the length is outside of the table, it stays fixed. • The width varies from row to row.

10. Simple Absolute Address Example The F4 Key. • When you point to the length, press the F4 key. Note the $ symbols – this indicates an absolute address • The width is a relative address

11. Simple Absolute Address Example The F4 Key. • When the formula is duplicated down the column, the length reference says fixed (absolute), the width reference is relative

12. Simple Absolute Address Example The F4 Key. • Complete the area computation in the same manner

13. Simple Absolute Address Example The F4 Key. • Complete the Area computation with one absolute and one relative address. • Copy the formula • Note the absolute and relative references.

14. GPA Calculator

15. GPA Calculator • List your courses • List your grades • List the number of credits

16. GPA Calculator • Lookup the grade in the table and record the points • Compute the total points • Sum the points • Sum the credits • Compute the GPA

17. Look up the grade • Vertical look up table • The look up table is outside of the GPA table, hence ABS address. • The grade being looked up is in the GPA table, hence REL address

18. Vlookup • Lookup_value: The grade (relative address) • Table_Array: The table (Absolute address) • Col_index_num: Number in column 2

19. Vlookup • Col_index_num: Number in column 2 • Table_Array: The table (Absolute address) • Lookup_value: The grade (relative address)

20. VLookup

21. Multiply points * credits

22. Copy formulas down columns

23. Auto Sum • Credit column • Total points column

24. GPA • Divide total points by total credits