1. Spreadsheets, cont. 22February 2011

2. What if I Want the SAME Place • Absolute positioning • Can lock the cell, column or row • Cell: $A$1 • Column: $A1 • Row: A$1 • To change a reference to absolute • Insert $ • Use F4

3. Exercise • Start with A1 hourly rate B2:B8 date C2:C8 hours worked • You are to add D2:D8 day’s pay • Only want to type the formula ONCE

4. Using Multiple Worksheets • Why? • Separate input data • Presentation • Summarization • Versions • How to reference between • Sheet!Cell • To go between workbooks • ‘[workbook]worksheet’!cell

5. Exercise • From prior exercise Move hourly rate to another sheet

6. Why Multiple Sheets? • General structure • Data on one page • Computations on another • Easy to change the data

7. Referencing Multiple Cells • Continuous cells (RANGE) • Colon (:) • Drag cursor • Combining (UNION) • Comma (,)

8. Naming sections • Under Formulas tab, • Name Manager: Define Name • Some default options • If the row or column has a label, will use it • Can collect non-adjacent • Absolute addresses

9. What else can you Name? • Constants • Single Cells • Formulas

10. Why name? • Human readability • Convenience if the section size changes

11. Operator Precedence

12. Complex Computations • Area of • Area of white – area of blue (all changeable) 25 55 10 10 45

13. Complex Computations • Option 1 • Separate entries and hide fields • Hide columns or use separate spreadsheets • Option 2 • Build them up in pieces • Use parentheses if you can’t remember precedence • Option1, followed by option 2