GoalUse formulae, absolute references and queries, and choose the best tool for a data job.
New words
absolute reference · a cell reference with $ signs, such as $B$1, that does not move when a formula is copiedprimary key · a field whose value is different in every record, so that each record can be foundquery · a question that asks a database to show the records that match the criteriawhat-if analysis · changing a value in a model to see what happens to the results
Explanation
A spreadsheet is good at calculations. A formula starts with =, uses cell references and keeps its answer up to date when a value changes. SUM and AVERAGE work on a range such as B2:B6. A reference such as B1 moves when a formula is copied down, but $B$1 is locked and always points to the same cell. A conditional formatting rule colours the cells that meet a condition. A database stores many records in a table of fields. Each record needs a primary key, each field has a data type, and a query uses criteria such as Minutes > 30 to select records. A data model uses factors and rules to predict results, and a simulation imitates a real situation, so people can try things safely.
Spreadsheet
Database
cells and formulae
fields, records and queries
SUM, AVERAGE
criteria such as Minutes > 30
$B$1 locks a cell
primary key makes each record unique
conditional formatting rule
highlight rule on a form or report
what-if: change a cell
report: group and count records
Worked examples
C3fx=$B$1*B3
A
B
C
1
Price per bib
3
2
Team
Bibs
Cost
3
Reds
8
24
4
Blues
12
36
5
Greens
10
30
6
Golds
6
18
7
Total
36
108
A sports club buys team bibs at the price in B1. The formula =$B$1*B3 is copied down, so the price stays locked and only the number of bibs changes. The total is 36 bibs and a cost of 108. A conditional formatting rule ‘Cost greater than 25’ shades the two cells that hold 36 and 30.
RouteNo (key)
Start
Minutes
Night service
12
Harbour
25
No
14
Market
40
Yes
21
Harbour
35
No
25
Station
50
Yes
33
Market
20
No
41
Station
45
Yes
A bus company keeps a database of its routes. Start repeats (every start appears twice) and Night service repeats, and two routes could one day take the same number of minutes, so RouteNo is the right primary key. The query ‘Minutes > 30’ shows RouteNo and Minutes for routes 14, 21, 25, 41. The query ‘Night service = Yes’ selects routes 14, 25, 41.
Situation
Best tool or method
Why
add up the rainfall of a school garden and find the average
spreadsheet
formulae such as SUM and AVERAGE do the calculations
800 library books that readers search by title or author
database
a query can search many records quickly
try different ticket prices to see the income
spreadsheet (what-if)
change one cell and every formula updates
learner pilots practise landing in a moving-picture program
simulation
mistakes are safe and the real plane is not needed
Choose the tool by looking at the job. Calculations on numbers and ‘what if?’ questions suit a spreadsheet. Many records that people search suit a database. A simulation is used when real practice would be dangerous, expensive or impossible.
Class activity
Tool chooser (no computer): write eight jobs on cards, such as ‘the total cost of a school trip’, ‘a list of all the pupils in the choir and the days they can come’ and ‘a moving-picture program for practising a fire drill’. Make three boxes: spreadsheet, database and simulation. Put each card in a box and write one reason on the back. Then pick one of the spreadsheet cards and draw a grid on paper with a formula that uses a locked cell reference.
Practice
1
Write the formula for each empty cell and work out what it shows: the total and the average number of pages read.
Write the criteria for each query and list the titles that are selected. (a) the titles of films shorter than 100 minutes (b) the titles of films that have not been seen
FilmID (key)
Title
Minutes
Seen
1
Desert Kite
85
Yes
2
The Silver Fox
102
No
3
Moon Garden
96
Yes
4
Tiny Giants
118
No
5
River Song
90
No
(a) Minutes < 100 selects Desert Kite, Moon Garden, River Song. (b) Seen = No selects The Silver Fox, Tiny Giants, River Song.
3
The cost in D2 shows #VALUE! instead of 24. Explain the mistake and write the correct formula.
D2fx=A2*C2
A
B
C
D
1
Item
Price
Number
Cost
2
Cap
6
4
#VALUE!
The formula =A2*C2 multiplies the label Cap by a number, and a word cannot be multiplied. The price is in B2, so the formula must be =B2*C2, which shows 24.
4
The price of a plant pot rises from 4 to 5. Work out the new cost of each class and the new total. Which costs are shaded by the rule ‘Cost greater than 50’?
A
B
C
1
Price per pot
5
2
Class
Pots
Cost
3
Year 7A
9
?
4
Year 7B
14
?
5
Year 7C
11
?
6
Year 7D
6
?
7
Total
40
?
Costs: 45, 70, 55 and 30. The total is 200. The cells with 70 and 55 are shaded, because 45 and 30 are not greater than 50. Only the cell with the price was changed; the formulae did the rest.