CPSC-130 Computing With Spreadsheets

1
CPSC-130Computing With Spreadsheets

• Gaining proficiency
• Week 2

2
Gaining Proficiency

• Finished Grade Book example
• Isolating assumptions
• Cell addressing
• Formatting
• Operator precedence
• Web Integration
• Date Arithmetic

3
Finished Grade Book Example

• Add a column
• Formula wrong
• Isolate assumption
• Relative vs. Absolute addressing

4
Isolating Assumptions

• Isolate means putting the value in a clearly
  marked cell
• Makes it explicit to spreadsheet reader
• Only have to change it in one place
• Isolate any value which isnt immediately obvious
  by looking at the sheet

5
Cell Addressing

• Relative so many cells over and down/up
• Absolute that particular cell
• Mixed so many columns over but that particular
  row, or so many rows over, but that particular
  column
• Examples

6
Copy and Move

• Copy updates references according to new location
• Move (cut and paste) updates references only if
  needed.
• Watch out for moves since they can affect so many
  cell formulas.
• Fill Handle

7
Formatting

• Column/row width
• Format Cells
• Shift vs. Control
• Format painter (paintbrush icon)

8
Operator Precedence

• What is the value of the following expressions?

123 (12)3 1(23)
123 (12)3 1(23)
-1 2 -(1 2)
123 (12)3 1(23)
9
Operator Precedence(memorize)
Highest , Colon, comma, and space
- Negation
Percent
Exponentiation
/ Multiplication and division
Addition and subtraction
String concatenation
Lowest lt gt lt gt ltgt Comparison operators
10
Web Integration

• Hyperlinks
• Save as Web Page
• Round-trip HTML
• Directory of Support Files
• Web Queries
• Import
• Refresh

11
Date Arithmetic

• Today()
• Number of days
• Years days / 365.24