Week 17 · 2.2 Creating real-life systems and evaluating pre-existing spreadsheets · 3 lessons
2
Weights, bands and tests
Computing 9: Topic 2.2, pp. 198–216
GoalBuild a scoring model with weights, an overall mark, a nested IF band and MAX and MIN, then test it with known answers.
New words
weight · the share of the overall mark that one part can give if it gets full marksoverall mark · the total of the weighted shares, usually out of 100band · a named range of marks, such as Good, chosen with a nested IFtest data · values with an answer you worked out by hand, used to check that the model is correct
Explanation
A scoring model often gives some parts more importance than others. The weight says how many marks of the overall 100 a part is worth. To find a part’s share, divide the score by the full marks and multiply by the weight. Add the shares to get the overall mark. A nested IF then turns the overall mark into a band, with the largest limit tested first. MAX and MIN find the highest and lowest overall marks. Finally, test the model: full marks everywhere must give exactly 100, and a mark exactly on a band limit must give the higher band.
Question
Formula idea
Share of one part?
score ÷ full marks × weight
Overall mark?
add the shares
Band?
nested IF, largest limit first
Best and worst?
MAX and MIN over the overall column
Worked examples
Criterion
Score
Full marks
Weight (%)
Share
Idea
16
20
30
16 ÷ 20 × 30 = 24
Experiment
30
40
50
30 ÷ 40 × 50 = 37.5
Talk
8
10
20
8 ÷ 10 × 20 = 16
Overall
100
24 + 37.5 + 16 = 77.5
The science-fair judges give each project a mark for Idea (out of 20, weight 30), Experiment (out of 40, weight 50) and Talk (out of 10, weight 20). The weights add up to 100. Kenji scores 16, 30 and 8. Each score is divided by its full marks and multiplied by its weight. The shares are added.
E2fx=B2/$B$7*$B$8+C2/$C$7*$C$8+D2/$D$7*$D$8
A
B
C
D
E
1
Project
Idea (/20)
Experiment (/40)
Talk (/10)
Overall (%)
2
Kenji
16
30
8
77.5
3
Ana
20
36
9
93
4
Ben
10
24
6
57
5
Aisha
14
32
5
71
6
Lola
15
30
5
70
7
Full marks
20
40
10
8
Weight (%)
30
50
20
The full marks and weights are stored in rows 7 and 8, and the formula in E2 uses them with dollar signs: =B2/$B$7*$B$8+C2/$C$7*$C$8+D2/$D$7*$D$8. Kenji’s overall is 77.5. Filling the formula down gives 93 for Ana, 57 for Ben, 71 for Aisha and 70 for Lola.
Band and best and worst. The band uses the limits 90, 70 and 55: =IF(E2>=90,"Excellent",IF(E2>=70,"Good",IF(E2>=55,"Fair","Try again"))). Lola’s 70 is exactly on a limit, and >= gives her Good, not Fair. The highest mark is =MAX(E2:E6) = 93 and the lowest is =MIN(E2:E6) = 57.
Class activity
Score card (no computer). Choose a contest you can run at home, for example a paper-aeroplane flight. Give three criteria, such as distance, accuracy and style, each marked out of 10, with the weights 50, 30 and 20. Two people make an entry each and are scored. Work out each overall mark on paper with ‘score ÷ full marks × weight’, then decide the band with these limits: 70 and over Good, 45 to 69 Fair, below 45 Try again. Change one score by 1 and see whether the band changes. (If you have a computer or tablet, you may build the same score card in a spreadsheet.)
Practice
1
A baking contest marks Taste out of 50 (weight 60) and Look out of 10 (weight 40). A baker scores 40 for Taste and 7 for Look. Complete the picture and give the overall mark.
The judges test the model with a project that has full marks everywhere. What overall mark should appear, and what is wrong with this sheet?
E2fx=B2/$B$3*$B$4+C2/$C$3*$C$4+D2/$D$3*$D$4
A
B
C
D
E
1
Project
Idea (/20)
Experiment (/40)
Talk (/10)
Overall (%)
2
Perfect
20
40
10
105
3
Full marks
20
40
10
4
Weight (%)
30
50
25
It should show 100. It shows 105 because the weights are 30, 50 and 25, which add up to 105 and not 100. Change the Talk weight to 20.
3
The formula in B2 is =IF(A2>=70,"A",IF(A2>=45,"B","C")). Work out the four bands.
B2fx=IF(A2>=70,"A",IF(A2>=45,"B","C"))
A
B
1
Mark
Band
2
70
?
3
69.5
?
4
45
?
5
44.9
?
70 gives A (70 is equal to 70). 69.5 gives B. 45 gives B (45 is equal to 45). 44.9 gives C.
4
A learner wrote =IF(E2>=55,"Fair",IF(E2>=70,"Good",IF(E2>=90,"Excellent","Try again"))). What band does a mark of 93 get? Why is this wrong and how is it repaired?
It gives Fair, because 93 is at least 55 and the first test is true, so the other tests are never reached. Put the largest limit first: =IF(E2>=90,"Excellent",IF(E2>=70,"Good",IF(E2>=55,"Fair","Try again"))).