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.
Check
How
Formulas
work out the answer by hand and compare
Ranges
do they start and end at the first and last cell?
Functions
is it the right one for numbers or for text?
Formats
decimals, units and widths as required
Readability
contrast, font size, headings, instructions
Worked examples
Requirement
Formula in the sheet
Shown
By hand
Verdict
Number of members
=COUNT(A2:A7)
0
6
wrong
Total of fines
=SUM(D2:D6)
2.75
3.75
wrong
Members late
=COUNTIF(C2:C7,">0")
4
4
correct
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)
A
B
C
D
1
Member
Book
Days late
Fine ($)
2
Tom
Atlas
3
0.75
3
Ravi
Poems
0
0
4
Nodira
Maps
6
1.5
5
Ana
Tales
2
0.5
6
Leo
Birds
0
0
7
Yuki
Stars
4
1
8
9
Members listed
0
10
Total fines ($)
2.75
11
Members late
4
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.
Finding
Kind
Recommendation
Column headings are pale yellow on white
non-functional
Use dark text or a darker fill for contrast
Fines show 1.5 and 0.75
non-functional
Format the column with two decimal places: 1.50, 0.75
The rate 0.25 is typed inside every fine formula
functional
Put the rate in a labelled cell and use $ in the formulas
No note says where to type
non-functional
Add 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).
A
B
1
Tool
How many
2
Hammer
3
3
Saw
2
4
Drill
1
5
Tape
5
6
Pliers
4
COUNT(A2:A6) is 0, because the names are text. COUNTA(A2:A6) is 5. COUNTIF(B2:B6,">2") is 3, because 3, 5 and 4 are more than 2. MAX(B2:B6) is 5.
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)
A
B
1
Day
Sales
2
1
20
3
2
30
4
3
25
5
4
35
6
5
40
7
8
Average
27.5
The range stops at B5, so the fifth day (40) is missed. The formula shows (20 + 30 + 25 + 35) ÷ 4 = 27.5. The correct average is 150 ÷ 5 = 30. The repair is =AVERAGE(B2:B6).
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.
Functional: the total formula that stops one row early (extend the range). Non-functional: the orange on yellow title (use a darker colour for contrast), the very small numbers (use a larger font) and the missing heading (add a clear heading such as ‘Days late’).
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.
No. A sheet must do its job first, and a wrong total gives wrong decisions. Colours help people read, but they cannot repair a wrong answer. The best sheet has correct formulas and a clear layout.