Lecture 5 Spreadsheets and Presentations The Spreadsheet The

Lecture 5 Spreadsheets and Presentations

The Spreadsheet The spreadsheet is a matrix that consists of: – Worksheet (a spreadsheet document) – Columns (alphabetical horizontal divisions) – Rows (numbered vertical divisions) © 1999 Addison Wesley Longman 2

The Spreadsheet – Cells (the intersection of a row and column) – Addresses (column letter and row number, e. g. , C 12) © 1999 Addison Wesley Longman 3

The Worksheet The worksheet is a grid formed by columns and rows and can contain: – Values (or numbers such as 4, -76, $120. 00). – Labels (words that explain what the numbers mean such as Food). © 1999 Addison Wesley Longman A B 1 Expenses Amount 2 Rent $400 3 Food $250 4 Utilities $120 5 Total $760 4

Entering Text © 1999 Addison Wesley Longman 5

Entering Numbers © 1999 Addison Wesley Longman 6

The Worksheet – Formulas (a stepby-step procedure for calculating a number, e. g. =Sum(B 2: B 4). © 1999 Addison Wesley Longman 7

Specifying a range of cells © 1999 Addison Wesley Longman 8

Cell Types • Numerical Values (simply a number) – displayed right justified within the cell by default – may contain math operations eg. =6 -3+7 is displayed as 10 • Numeric Formula: – formulae may refer to values in other cells – for example, B 6 is =B 3+B 2 or +B 3+B 2 – values in B 2 and B 3 are added, result being displayed in B 6 © 1999 Addison Wesley Longman 9

Cell Types (cont. ) • Text (or Labels): – anything else, though usually a heading – treated as a sequence of individual characters – to enter numbers as text, enter one of the quotes as first character. – for example, '1234 entered as one two three four • Blank – mathematical value of zero. © 1999 Addison Wesley Longman 10

Some common functions • =SUM(range) = sum of all cells within the range • =AVERAGE(range) = average of non-blank cells within range • =MIN(range) = Returns minimum value in range • =MAX(range) • =TODAY() = just date • =NOW() = date and time • =IF(condition, true, false) © 1999 Addison Wesley Longman 11

Spreadsheet Features • Automatic replication of values, labels, and formulas (relative versus absolute references) • Automatic recalculation © 1999 Addison Wesley Longman 12

Relative References • Deafult type • Copy command automatically adjusts cell references in formula as it copies. • Relative references are adjusted – column references as copies across – row references as copies up/down. © 1999 Addison Wesley Longman 13

Absolute References • Absolute references are not changed – needed when the value is taken from one cell – e. g. interest rate • Add $ sign to make absolute reference – $H$4 always refer to fixed location – H$4 keeps row reference fixed on copying – $H 4 keeps column reference fixed when copying © 1999 Addison Wesley Longman 14

Spreadsheet Features • Linking (reflect changes in related worksheets). • Database capabilities. © 1999 Addison Wesley Longman 15

Spreadsheet Features • Predefined functions (e. g. , SUM, AVG, SQRT). • Macros (custom design your own feature) • Templates (ready-touse worksheets). © 1999 Addison Wesley Longman 16

“What If? ” Questions • Spreadsheets allow you to change numbers and instantly see the effects of those changes. – “What if I enter this value? ” • Equation solvers – Some spreadsheets generate data needed to fit a given equation and target value. © 1999 Addison Wesley Longman 17

“What If? ” Questions • Validators - the equivalent of spelling and grammar checkers for spreadsheets. © 1999 Addison Wesley Longman 18

Spreadsheet Graphics: From Digits to Drawings Charts allow you to turn numbers into visual data: – Pie charts (show relative proportions to the whole) – Line charts (show trends or relationships over time) © 1999 Addison Wesley Longman 19

Spreadsheet Graphics: From Digits to Drawings – Bar charts (use if data falls into a few categories) – Scatter charts (use to discover, rather than to display, a relationship between two variables) © 1999 Addison Wesley Longman 20