Spreadsheets and Data Modelling: Unit Exam B
Averages and maximums, copying formulas, relative and absolute references, IF, charts, validation, good design and what if questions.
Instructions
- Time allowed
- 25 minutes
- Total marks
- 25
- Calculator
- No calculator is permitted.
- Answer all the questions.
- The number of marks for each question is shown in brackets. Use it as a guide to how much to write.
- The clock at the top counts down from the real time allowed. When it reaches zero the paper is locked and marked, as it would be collected in the exam hall.
- Read every question to the end before answering. Where a question says tick two boxes, tick exactly two: ticking more than asked scores nothing. Type one word and number answers carefully, because they are marked exactly.
Questions
Answer every question, then finish the paper to have it marked. Marking as you go teaches you far less.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Name | Test 1 | Test 2 | Average |
| 2 | Ali | 12 | 18 | |
| 3 | Bea | 15 | 15 | |
| 4 | Cy | 9 | 13 | |
| 5 |
- L=AVERAGE(B2:C2)
=AVERAGE(B2:C2) finds the mean of the two scores.
- L15
(12 + 18) / 2 = 15.
- L11
(9 + 13) / 2 = 11.
- L=MAX(B2:B4)
=MAX(B2:B4).
- L15
The highest of 12, 15 and 9 is 15.
Tick one box.
- LD The cell references change relative to the row they are copied into
Relative references adjust: in D3 the formula uses B3 and C3.
| Relative | Absolute | |
|---|---|---|
| B2 | ||
| $B$2 | ||
| $C$1 | ||
| D7 |
Tick one box in each row.
- LB2: Relative
- L$B$2: Absolute
- L$C$1: Absolute
- LD7: Relative
Dollar signs fix a reference so it does not change when copied.
- L13
The smallest of 18, 15 and 13 is 13.
- LPass
- LFail
Ali's average is 15, so Pass. Cy's is 11, so Fail.
Tick one box.
- LA Bar or column chart
A bar chart compares separate categories.
- LClear headings and labels, with consistent formatting
- LValidation, conditional formatting, charts, or input cells kept separate from calculations
Clear headings and labels show what each column holds, with consistent formatting such as currency. Data validation on the input cells stops impossible values being typed in.
- LIt asks what would happen if an input changed, such as a price rising
- LYou change that value in the model and the formulas recalculate to show the result
A what if question asks what would happen if something changed, such as what if the ticket price rose to £6. You change that input cell and every formula recalculates, so the model shows the new result straight away.
Tick one box.
- LC Only allowing whole numbers from 0 to 20 in a test score cell
Validation limits what can be entered.
- L20
4 columns x 5 rows = 20.
Tick one box.
- LB A group of cells, such as B2:B4
A range runs from one cell to another.
| True | False | |
|---|---|---|
| A formula updates automatically when a cell it uses changes | ||
| =SUM(B2:B4) adds only B2 and B4 | ||
| Conditional formatting changes the value in a cell |
Tick one box in each row.
- LA formula updates automatically when a cell it uses changes: True
- L=SUM(B2:B4) adds only B2 and B4: False
- LConditional formatting changes the value in a cell: False
Formulas recalculate. B2:B4 includes B3 too. Conditional formatting changes appearance, not values.
- L30
Bea scored 15 and 15: 30.
This is an original Key Stage 3 assessment. Most questions are closed, so the marking is exact, and the written answers are marked against a mark scheme in the same style as a GCSE paper, so the format is familiar long before it counts.