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
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.

QuestionFormula 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
CriterionScoreFull marksWeight (%)Share
Idea16203016 ÷ 20 × 30 = 24
Experiment30405030 ÷ 40 × 50 = 37.5
Talk810208 ÷ 10 × 20 = 16
Overall10024 + 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
ABCDE
1ProjectIdea (/20)Experiment (/40)Talk (/10)Overall (%)
2Kenji1630877.5
3Ana2036993
4Ben1024657
5Aisha1432571
6Lola1530570
7Full marks204010
8Weight (%)305020
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.
F6fx=IF(E6>=90,"Excellent",IF(E6>=70,"Good",IF(E6>=55,"Fair","Try again")))
ABCDEF
1ProjectIdea (/20)Experiment (/40)Talk (/10)Overall (%)Band
2Kenji1630877.5Good
3Ana2036993Excellent
4Ben1024657Fair
5Aisha1432571Good
6Lola1530570Good
7Full marks204010
8Weight (%)305020
9Highest93
10Lowest57
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.
PartScoreFull marksWeight (%)Share
Taste405060?
Look71040?
Overall100?
2
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
ABCDE
1ProjectIdea (/20)Experiment (/40)Talk (/10)Overall (%)
2Perfect204010105
3Full marks204010
4Weight (%)305025
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"))
AB
1MarkBand
270?
369.5?
445?
544.9?
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?