Transcript Spreadsheets

Spreadsheets
The Basic Principals
Why use spreadsheets?
• Spreadsheets allow the user to do calculations.
• Spreadsheets allow the user to enter complex
formulae to allow calculations to be performed.
• Spreadsheets allow the user to create graphs
from numerical data.
• IF ONE OF THE NUMBERS CHANGES THEN
THE COMPUTER WILL RECALCULATE THE
ANSWER.
Types of Formulae - Adding
If you want to add up a list of numbers there are 2
ways of doing this.
A
1
2
3
4
5
B
23
10
7
88
=B1+B2+B3+B4
A
1
2
3
4
5
B
23
10
7
88
=SUM(B1:B4)
This tells the computer to add
everything between B1 and B4
Types of Formula - Multiplying
Sometimes you will need to use a formulae to
calculate the total cost of something. This could
be 400 apples at 20p each.
1
A
B
C
Apples
400
20p
D
=B1*C1
E
Formulae – Special Types
AVERAGE
MAX/MIN
This calculates the average of a list
This calculates the
biggest and smallest in a
list.
1
2
3
4
5
6
A
3
5
27
14
99
=AVERAGE(A1:A5)
1
2
3
4
5
A
B
34
37
31
MAX
MIN
=max(B1:B3)
=min(B1:B3)
CELL ATTRIBUTES
•
The cell in a spreadsheet can contain text or number. You can define what
you want the spreadsheet to display.
A
B
C
1
Tuck
Shop
26 Oct
2
Juice
20
20p
400p
3
Crisps
10
25p
250p
4
Mars
50
50p
2500p
5
TEXT
D
£31.50
NUMBER
DATE
CURRENCY
Relative or Absolute
When you use a formulae to do a series of calculations then it is likely it will be
the same throughout. e.g.
1
2
3
4
5
6
A
Tuck
Item
B
C
D
Shop Sales
No. Cost Total
Apples
20
5
=B3*C3
Crisps
25
10
=B4*C4
Cans
30
20
=B5*C5
E
F
Notice as you go
down the
spreadsheet the
formula changes.
This is RELATIVE
formulae – this
means it changes
each time.
Absolute Formula
•
When you need a formula to take a value from the SAME reference throughout then you use
ABSOLUTE. E.g.
1
2
3
4
5
A
Item
B
Cost
C
Cost +
VAT
Paint
Paste
Brush
£5.00
£2.50
£1.75
=B2*$E$2
D
E
VAT
0.175
=B3*$E$2
=B4*$E$2
The $ sign tells the computer to take the same formula and DO NOT MOVE
DOWN – this is called ABSOLUTE formulae