AdvertisementYour brand could be hereYour brand hereOn every Darslab page · students, parents and teachersinfo@darslab.comAsk for pricesPricesinfo@darslab.com
☰ Year plan · Computing

Build and judge a system

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.

StepQuestion to answer
1 PurposeWhat must the sheet do, and who uses it?
2 Data and calculationsWhat goes in (inputs)? What must come out (outputs)? Is any fixed number needed?
3 LayoutWhat goes in the rows and what in the columns?
4 DesignWhich colours, borders and widths make it clear?
5 Data and formulasType or import the data, then add the formulas
6 TestDo the answers match a hand calculation?
Worked examples
Part of the planLap log
PurposeRecord laps for three sessions; show each swimmer’s total laps and distance
InputsSwimmer name and laps in each session
OutputsTotal laps and distance in metres
OtherPool 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
ABCDEF
1SwimmerSession 1Session 2Session 3Total lapsDistance (m)
2Mia12101436900
3Omar89825625
4Lola151512421050
5
6Pool 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.
FeatureKind
F2 multiplies the laps by the pool lengthfunctional
Laps are typed as numbers, not as textfunctional
Heading row is bold with a light fillnon-functional
Columns are wide enough to show every valuenon-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 planReading challenge
Purpose?
Inputs?
Outputs?
Other?
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
ABC
1RiderLapsDistance (m)
2Ravi7?
3Ana12?
4
5Track length (m)400
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.
4
Explain why the pool length is kept in one labelled cell and not typed as 25 inside every distance formula.