Transcript Spreadsheets What we’ll cover for this lecture topic: – The paper worksheet ... – What are electronic spreadsheets? – Cells • cell references • cell contents –

Spreadsheets
What we’ll cover for this lecture topic:
– The paper worksheet ...
– What are electronic spreadsheets?
– Cells
• cell references
• cell contents
– Formulas and functions
– What are spreadsheets good for?
– Relative and absolute cell
referencing (critical!)
• Demos interspersed as time permits
COMP 4—Power Tools for the Mind
Power Tools 1
The electronic spreadsheet
The paper worksheet
• Worksheets were used in many businesses
for inventory management and accounting.
• Key feature: columns to separate different
types of information (‘fields’).
• Because columns sizes varied depending
on the use, many formats were available.
Accounting Ledgers (Items, debits, credits, tax info…)
Inventory (Items, #in stock, supplier, wholesale $, retail $...)
Payroll (Name, SSN, job title, mailing address, salary…)
BOTTOM LINE: Lots of paperwork and calculation!
Mid 70’s: Dan Bricklin & Bob Frankston--the story.2
The electronic spreadsheet
• “The most brilliant software invention of the
microcomputer era … “ Neill Graham
Basically, it’s just a 2-dimensional table:
Dept
Classical
Country
Jazz
TOTALS
Oct
$2,500
$3,855
$4,880
$11,235
Nov
Dec
$2,700 $2,900
$3,500 $3,200
$4,200 $4,800
$10,400 $10,900
• Each entry occupies a cell... It can hold:
– numeric values
– alphanumeric text (character strings).
• As labels; what about as data?
– an entire formula. The real power!
COMP 4—Power Tools for the Mind

Power Tools 3
• Remember Charles Babbage?
• Manual spreadsheets had lots of problems…
–
–
–
–
time-consuming to produce
difficult to edit and update
miscalculation errors
transcription errors (recording wrong number)
• With electronic spreadsheets…
– calculations are automated (fast, reliable, &
simple to modify!)
– formatting can also be easily changed
COMP 4—Power Tools for the Mind
Power Tools 4
Referencing a Cell
• In most electronic spreadsheets, columns are indexed
by letters, and rows are indexed by numbers.
A
B
C
D
E
1
2
3
4
• A cell is identified and referenced by writing its
column letter(s) followed by its row number. E.g., cell
D3 is highlighted in the above picture.
COMP 4—Power Tools for the Mind
Power Tools 5
Referencing a group of Cells
• To refer to a block of contiguous cells, specify a
starting cell and ending cell separated by a colon.
E.g., the marked region below is C2:E4.
A
B
C
D
E
1
2
3
4
• Non-contiguous blocks of cells can be referenced
using a list in which the references are separated by
commas, e.g., the reference C2:E4,A1:A3 is the
marked block above along with cells A1, A2 & A3.
COMP 4—Power Tools for the Mind
Power Tools 6
When a cell value CHANGES….
all cell functions that use that
cell’s value are automatically
recalculated.
Immensely USEFUL!
• So what’s it good for?
–
–
–
–
–
–
Personal/professional budgets and budget scenarios
Building space allocation
Grades
Income statements, balance sheets
Investment analyses
Keeping track of nearly anything ...
COMP 4—Power Tools for the Mind
Power Tools 7
• Great for “what-if” analyses:
– What if….
• we decrease costs of good sold by 5%, what will
our net earnings be?
• I earn an 80% on Exam 2, what will my average
be?
• If my pay gets raised 5%, how much extra takehome pay will I get? Can I afford another loan?
Demo 1 & 2….. Meet Microsoft Excel
COMP 4—Power Tools for the Mind
Power Tools 8
More about formulas
Excel uses a very simple programming language:
• Nouns?
cell references (names)
Digression…really interested in? cell contents
• Basic Verb Set (2-operand operations) includes:
Arithmetic operators:
+
Comparison operators: = <
– *
/
^
<= >
Boolean operators:
And Or
Special symbols:
$ , : ()
>= < >
Not
and:
Functions! 
COMP 4—Power Tools for the Mind
Power Tools 9
Functions
• Many more operations available as functions.
• Functions take a collection of cell references as
input, e.g.., SUM(A1:A5,B7,C8:C10)
calculates the sum of the values cells A1:A5,
B7, and C8:C10.
• A few particularly useful functions:
–
–
–
–
SUM: computes sum of all values
PRODUCT: computes product of all values
MAX: finds maximum value
MIN: finds minimum value
COMP 4—Power Tools for the Mind
Power Tools 10
– AVERAGE: finds average value
– COUNT: finds number of non-empty cells in the
group that contain numerical data.
– COUNTA: finds number of non-empty cells in
group.
– IF(condition,A,B): conditional formula. If
condition is true, use formula A. Otherwise, use
formula B.
• Fairly simple syntax for formulas.
• Mathematical precedence rules apply (use
parentheses)
COMP 4—Power Tools for the Mind
Power Tools 11
• Formulas can depend upon other cells (which
themselves might be the result of other
formulas...). Powerful!
= SUM(D4:D12)
= AVERAGE(A2:A200)
 Notice the colon:
Each is like a little
computer program that
lives in a cell.
COMP 4—Power Tools for the Mind
Power Tools 12
Relative cell references
• How does Excel look at a cell reference in a formula?
Relative to where formula resides!
Entry bar: B4 = (B1 + B2) * B3 [Enter]
A
1
2
3
C
15
30
10
20
Find value stored
3 rows up from formula cell,
2
Add that to value stored
2 rows up from formula cell,
3
= (B1 + B2) * B3
4
Excel interprets the
formula in cell B4 this way:
B
Multiply that to value stored
1 row up from formula cell,
Display answer.
Note: formulas displayed. 13
SO WHAT? Why should you care? Because:
--You will want to RE-USE formulas you create as OFTEN as
possible! (Copy and Fill)
--You must know HOW Excel will INTERPRET your request!
A
B
C
D
1
2
3
20
5
2
4
5
30
10
3
6
7
40
15
4
5
= A1 + B2
= C2 + D3
What if I
copy A4 to
C5?
What will
C5 look
like?
Row & cell
references
will
change!
14
What about here?
Very same idea
A
B
C
D
1
2
3
20
5
2
4
5
30
10
3
6
7
40
15
What if I
copy C4 to
A6?
What will
A6 look like?
= C1 +C2 +C3
4
5
6
=A 3 +A4 +A 5
15
What about here?
A
B
C
D
1
2
3
20
5
2
4
5
30
10
3
6
7
40
15
4
5
= C1 +C2 +C3
=C 2+C3 +C4
What if I
copy C4 to
C5?
What will
C5 look
like?
Did we
move
columns?
But we
DID move
ROWS! 16
Important Tricks of the Trade
• When you COPY a formula from one cell
to another, any RELATIVE references will
follow:
– That is: CELL REFERENCES will
CHANGE in the target cell, as just seen.
• Placing the special symbol $ in front of the
row or column identifier in a reference
prevents Excel from changing that part of
the reference when copying. ABSOLUTE
COMP 4—Power Tools for the Mind
Power Tools 17
1
A
B
C
D
10
3
20
5
1st: Excel
interprets the
formula this
way:
Find value
2
4
5
30
10
3
6
7
40
15
stored
in exactly
CELL A1,
Subtract from that:
value stored in cell:
4
5
6
= $A$1 - B2 + C3
2 rows up and
1 col to the right;
=$A$1 -C3 +D 4
What if I copy A4 to B5?
What will B5 look like?
Add that
to value in cell
1 row up and
2 cols to the
right.
Display answer.
18
Referencing cells in formulas
• Cell references are classified according to the
absence or presence of the $ symbol.
• All three types are used depending on the
problem (we’ll look at real examples later that
show WHEN/WHY to use different references!)
– Relative reference: Neither the row nor column
identifier is preceded by $. E.g., B4. (the default)
– Mixed reference: Either the row or column
identifier is preceded by $, but not both. E.g., $B4
and B$4.
– Absolute reference: Both the row and column
identifiers are preceded by $. E.g., $B$4.
COMP 4—Power Tools for the Mind
Power Tools 19
Important Tricks of the Trade, con’t
• When you move a formula, Excel
automatically updates the cell references
contained in formulas.
– Examples coming…
• Nifty Feature: When you edit a formula,
Excel will put color-coded boxes around
the different cell references. Works great
when trying to figure out what is wrong
with a formula.
DEMO 3
COMP 4—Power Tools for the Mind
Power Tools 20