Spreadsheets and Data Modelling: Unit Exam A
Cell references and ranges, formulas and functions, copying formulas, absolute references, IF, conditional formatting, validation and charts.
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 | Item | Price (£) | Quantity | Total (£) |
| 2 | Pens | 1.50 | 4 | |
| 3 | Rulers | 0.80 | 10 | |
| 4 | Glue | 2.00 | 3 | |
| 5 | Grand total |
- LB3
Rulers are in row 3 and prices are in column B: B3.
- L**=B2*C2**
=B2*C2 multiplies the price by the quantity.
- L8
0.80 x 10 = 8.00.
- L=SUM(D2:D4)
=SUM(D2:D4) adds the whole range.
- L20
6.00 + 8.00 + 6.00 = 20.00.
Tick one box.
- LC =B3*C3
Relative references move with the copy: one row down gives B3 and C3.
Tick one box.
- LA Relative referencing
References that change when copied are relative.
Tick one box.
- LB =D2*$F$1
The dollar signs make $F$1 absolute, so it does not change when the formula is copied.
| SUM | AVERAGE | MAX | COUNT | |
|---|---|---|---|---|
| Adds up a range of numbers | ||||
| Finds the mean of a range | ||||
| Finds the largest value | ||||
| Counts how many cells contain numbers |
Tick one box in each row.
- LAdds up a range of numbers: SUM
- LFinds the mean of a range: AVERAGE
- LFinds the largest value: MAX
- LCounts how many cells contain numbers: COUNT
SUM adds, AVERAGE finds the mean, MAX finds the largest and COUNT counts the numeric cells.
- L9
- L5
The largest value is 9. The mean is (4 + 9 + 2 + 5) / 4 = 20 / 4 = 5.
- LExpensive
The pens cost 1.50, which is more than 1, so the IF shows Expensive.
Tick one box.
- LD Conditional formatting
Conditional formatting changes how a cell looks depending on its value.
Tick one box.
- LB Data validation
Data validation restricts what can be entered.
- LInputs such as the ticket price or numbers attending can be changed to see what would happen
- LFormulas recalculate automatically, so it is quick and avoids mistakes
The organisers can ask what if questions, changing the ticket price or the number of students coming, and the formulas recalculate the profit instantly, so they can find a price that covers the costs without doing the sums by hand.
Tick one box.
- LC Pie chart
A pie chart shows parts of a whole.
Tick one box.
- LD Line graph
A line graph shows change over time.
- L6
Columns B and C, rows 2 to 4: 2 x 3 = 6.
- LA formula is a calculation typed into a cell, starting with =
- LA function is a ready made formula with a name, such as SUM or AVERAGE
A formula is any calculation entered in a cell, starting with =, such as =B2*C2. A function is a ready made, named formula built into the spreadsheet, such as SUM or AVERAGE, which you can use inside a formula.
- L=
Every formula starts with an equals sign.
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.