Build and judge a system
Answers are for parents and teachers.
1
Think of a real-life task, such as keeping the scores of a family quiz game. Write a plan: purpose, inputs, outputs and one fixed number, if any. Then draw the layout with the field names in row 1.
Answers differ. A good answer has a one-sentence purpose, a list of typed-in data (inputs), a list of calculated answers (outputs such as totals or the winner, using SUM and MAX), and a clear row of headings with one player per row.
2
A garden contest marks Plants out of 30 (weight 50) and Tidiness out of 10 (weight 50). Work out the overall mark for each garden and its band using =IF(D2>=75,"Fine",IF(D2>=55,"Fair","Weak")).
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Garden | Plants (/30) | Tidiness (/10) | Overall (%) |
| 2 | A | 24 | 9 | ? |
| 3 | B | 18 | 6 | ? |
| 4 | C | 15 | 5 | ? |
| 5 | D | 9 | 8 | ? |
Garden A: 24 ÷ 30 × 50 = 40 and 9 ÷ 10 × 50 = 45, so 85, Fine. Garden B: 18 ÷ 30 × 50 = 30 and 6 ÷ 10 × 50 = 30, so 60, Fair. Garden C: 15 ÷ 30 × 50 = 25 and 5 ÷ 10 × 50 = 25, so 50, Weak (50 is less than 55). Garden D: 9 ÷ 30 × 50 = 15 and 8 ÷ 10 × 50 = 40, so 55, Fair (55 is equal to 55). The weights add up to 100, so full marks would give 100.
3
The sheet should show the total number of books read by the class. Check the formula and write what it shows, what it should show and the repair.
| A | B | |
|---|---|---|
| 1 | Week | Books |
| 2 | 1 | 3 |
| 3 | 2 | 5 |
| 4 | 3 | 4 |
| 5 | 4 | 6 |
| 6 | 5 | 9 |
| 7 | Total | 18 |
The range stops at B5 so it leaves out B6 (9). It shows 3 + 5 + 4 + 6 = 18. The correct total is 27. The repair is =SUM(B2:B6).
4
Write a nested IF for the cell B2 that gives Cold for a temperature below 10, Mild for 10 up to but not including 25, and Hot for 25 or more. Then give the result for 10, 24.9 and 25.
=IF(A2<10,"Cold",IF(A2<25,"Mild","Hot")). The value 10 gives Mild because 10 is not less than 10. The value 24.9 gives Mild. The value 25 gives Hot.
5
Marcia’s sheet has all formulas correct, but the text is light grey on white and the columns have no headings. Is the sheet suitable? List two improvements and say whether each is functional or non-functional.
It is not yet suitable because people cannot read it or know what the columns mean. Improvement 1: use dark text for contrast (non-functional). Improvement 2: add clear column headings (non-functional). The formulas are correct, so no functional change is needed.