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
3

Judge a spreadsheet

Computing 9: Topic 2.2, pp. 198–216
GoalCheck a ready-made spreadsheet against its requirements, find faulty formulas, and recommend improvements.
New words
requirement · something the spreadsheet must do or showsuitable · doing what it is meant to do, in a way that suits the people who use itCOUNTA · a function that counts every cell that is not empty, numbers and textimprovement · a change that would make the spreadsheet more correct, clearer or easier to use
Explanation

To judge a spreadsheet, start with the list of requirements. Check every formula by working the answer out another way, such as by hand. Then look at the non-functional features: is it easy to read, are the instructions clear, and are numbers shown in the format that was asked for? A formula can be wrong even when it shows a number. A range may stop one cell too short. COUNT counts only numbers, so on a column of names it gives 0, and COUNTA is needed. Finish with a list of improvements, each saying what is wrong and how to repair it. A sheet is suitable when it meets all the requirements.

CheckHow
Formulaswork out the answer by hand and compare
Rangesdo they start and end at the first and last cell?
Functionsis it the right one for numbers or for text?
Formatsdecimals, units and widths as required
Readabilitycontrast, font size, headings, instructions
Worked examples
RequirementFormula in the sheetShownBy handVerdict
Number of members=COUNT(A2:A7)06wrong
Total of fines=SUM(D2:D6)2.753.75wrong
Members late=COUNTIF(C2:C7,">0")44correct
The book club’s fine sheet must meet five requirements: easy to read; fines shown with two decimal places; total of all fines; number of members; number of members who returned a book late. Each formula is compared with a hand calculation. The hand count of members is 6, the fines add up to 0.75 + 0 + 1.5 + 0.5 + 0 + 1 = 3.75, and 4 members were late.
B10fx=SUM(D2:D6)
ABCD
1MemberBookDays lateFine ($)
2TomAtlas30.75
3RaviPoems00
4NodiraMaps61.5
5AnaTales20.5
6LeoBirds00
7YukiStars41
8
9Members listed0
10Total fines ($)2.75
11Members late4
Why are two formulas wrong? A2:A7 holds names, which are text, and COUNT counts only numbers, so it shows 0; =COUNTA(A2:A7) gives 6. The SUM range ends at D6, so Yuki’s fine in D7 is left out: 2.75 + 1 = 3.75. The repaired formula is =SUM(D2:D7). The third formula was already correct, so it needs no change.
FindingKindRecommendation
Column headings are pale yellow on whitenon-functionalUse dark text or a darker fill for contrast
Fines show 1.5 and 0.75non-functionalFormat the column with two decimal places: 1.50, 0.75
The rate 0.25 is typed inside every fine formulafunctionalPut the rate in a labelled cell and use $ in the formulas
No note says where to typenon-functionalAdd a short instruction above the table
Non-functional findings and recommendations. Each one says what is wrong, why it matters, and how to repair it. A good evaluation ends with a short verdict: the sheet is not yet suitable, because two requirements fail, but it can be made suitable by the repairs.
Class activity

Fix my sheet (no computer). Draw a small sheet on paper with about six rows of invented data and three formulas, a total, a count and an average. Put one mistake in each formula: a range that stops too early, COUNT on text, or a wrong cell. Work out the correct answers on a separate piece of paper. Swap sheets with a partner, who must find the mistakes using hand calculations and write one recommendation for each. Compare your answer sheet with theirs.

Practice
1
A tool-cupboard sheet is shown. Work out =COUNT(A2:A6), =COUNTA(A2:A6), =COUNTIF(B2:B6,">2") and =MAX(B2:B6).
AB
1ToolHow many
2Hammer3
3Saw2
4Drill1
5Tape5
6Pliers4
2
The sheet should show the average sales for five days. Find the fault, say what the formula shows and what it should show, and repair it.
B8fx=AVERAGE(B2:B5)
AB
1DaySales
2120
3230
4325
5435
6540
7
8Average27.5
3
A sheet has orange title text on a yellow fill, numbers in a very small font, a total formula that stops one row early and no heading over column C. Sort these four findings into functional and non-functional and give one fix for each.
4
Mina says: ‘A sheet with pretty colours and a wrong total is better than a plain sheet with the right total.’ Do you agree? Give a reason.