Week 17 · 2.2 Creating real-life systems and evaluating pre-existing spreadsheets · 3 lessons
1
Plan the system first
Computing 9: Topic 2.2, pp. 198–216
GoalPlan a spreadsheet system by writing its purpose, inputs, outputs and layout, and tell functional features from non-functional ones.
New words
real-life system · something people use to deal with events in the real world, such as a lap log, a booking sheet or a stock listlayout · the way the rows, columns and headings are arranged on the sheetfunctional feature · a part of the sheet that does the work, such as a formula or a functionnon-functional feature · a part that makes the sheet easy to read and use, such as colour, borders and column width
Explanation
Before you open a spreadsheet, decide its purpose: what must it do, and who will use it? Next list the inputs (what a person types in), the outputs (what the sheet works out) and any fixed number the formulas need. Then choose the layout. The column headings are the field names, and each row usually holds one person or one item. Last, add design features such as colours, borders and column widths so that people can read the sheet quickly. The data can be typed in, or imported from a plain text file with commas between the values (a CSV file). A sheet whose formulas are wrong is useless, however pretty it looks, so the functional features matter most.
Step
Question to answer
1 Purpose
What must the sheet do, and who uses it?
2 Data and calculations
What goes in (inputs)? What must come out (outputs)? Is any fixed number needed?
3 Layout
What goes in the rows and what in the columns?
4 Design
Which colours, borders and widths make it clear?
5 Data and formulas
Type or import the data, then add the formulas
6 Test
Do the answers match a hand calculation?
Worked examples
Part of the plan
Lap log
Purpose
Record laps for three sessions; show each swimmer’s total laps and distance
Inputs
Swimmer name and laps in each session
Outputs
Total laps and distance in metres
Other
Pool length 25 m, kept in one cell
The plan for a swimming club’s lap log. Each swimmer swims three sessions, and the club wants each swimmer’s total laps and distance. The pool is 25 m long, a fixed number used in every distance, so it gets its own labelled cell instead of being typed into each formula.
F2fx=E2*$B$6
A
B
C
D
E
F
1
Swimmer
Session 1
Session 2
Session 3
Total laps
Distance (m)
2
Mia
12
10
14
36
900
3
Omar
8
9
8
25
625
4
Lola
15
15
12
42
1050
5
6
Pool length (m)
25
The plan becomes a sheet. Row 1 holds the field names and each swimmer has one row. In E2 the formula =SUM(B2:D2) gives Mia’s total, 12 + 10 + 14 = 36 laps. In F2 the formula =E2*$B$6 multiplies the laps by the pool length in B6: 36 × 25 = 900 m. The dollar signs keep the pool-length cell fixed when the formula is filled down.
Feature
Kind
F2 multiplies the laps by the pool length
functional
Laps are typed as numbers, not as text
functional
Heading row is bold with a light fill
non-functional
Columns are wide enough to show every value
non-functional
Functional or non-functional? A feature is functional if it is needed for the sheet to give correct answers. It is non-functional if it only helps people to read and use the sheet. Both kinds count when you judge a sheet, but a wrong formula is the more serious fault.
Class activity
Plan on paper (no computer). Choose something your family could track for a week, for example the minutes each person reads, the glasses of water drunk or the steps walked. Write the purpose in one sentence and list the inputs, the outputs and any fixed number. Then draw the sheet on squared paper: put the field names in row 1 and fill in three rows of invented data. Ask a partner to find one output they could work out from your data. (If you have a computer or tablet, you may type your plan into a spreadsheet afterwards.)
Practice
1
A class runs a four-week reading challenge. The sheet must show each pupil’s total pages and the pupil with the most pages. Complete the plan in the picture.
Part of the plan
Reading challenge
Purpose
?
Inputs
?
Outputs
?
Other
?
Purpose: record the pages each pupil reads in four weeks and show totals and the best total. Inputs: pupil name and pages for weeks 1–4. Outputs: each pupil’s total pages (SUM) and the largest total (MAX). Other: none needed, because there is no fixed number.
2
A track is 400 m long and its length is in cell B5. Work out the values in C2 and C3 and say why $B$5 is used.
C2fx=B2*$B$5
A
B
C
1
Rider
Laps
Distance (m)
2
Ravi
7
?
3
Ana
12
?
4
5
Track length (m)
400
C2 = 7 × 400 = 2800 m. C3 = 12 × 400 = 4800 m. The $ signs lock the reference to B5, so when the formula is filled down to C3 it still multiplies by B5 and not by B6.
3
Write F (functional) or N (non-functional) for each fault. a) A total formula adds the wrong column. b) The title is pale yellow text on a white background. c) A column is so narrow that it shows ####. d) A number was typed as text, so SUM ignores it.
a) F, the answer is wrong. b) N, the title is hard to read. c) N, the value is hidden but the formula is fine; widening the column fixes it. d) F, SUM leaves the value out, so the total is wrong.
4
Explain why the pool length is kept in one labelled cell and not typed as 25 inside every distance formula.
If the pool length changes, only one cell has to be changed and every distance updates. Typing 25 into many formulas is slower and it is easy to miss one. The labelled cell also tells a reader what the number means.