MskProdComputing
Back to the topic

The mark scheme starts on a new page, so you can print pages one and two for a student and keep the last page yourself. Everything prints in black on white whichever theme you are using.

Key Stage 3 · Y8.4

Spreadsheets and Data Modelling

Section A: 10 multiple choice questions, 10 marks. Section B: 5 written questions, 16 marks. Total 26 marks.

Name

Class

Date

Mark  / 26

Section A

Circle the letter of the correct answer. One mark each.

  1. 1What must every formula start with?1
    1. A=
    2. B#
    3. C$
    4. D+
  2. 2What does =SUM(B2:B10) do?1
    1. AFinds the average of B2 to B10
    2. BCounts the cells from B2 to B10
    3. CAdds up all the values from B2 to B10
    4. DFinds the largest of B2 to B10
  3. 3What is the difference between B2 and $B$2 when a formula is copied?1
    1. AThey behave identically
    2. B$B$2 stays the same, B2 changes to match the new position
    3. C$B$2 makes the value a currency
    4. DB2 stays the same, $B$2 changes
  4. 4Which formula would display Pass for 50 or more, and Fail otherwise?1
    1. A=IF(B2>50,"Pass","Fail")
    2. B=SUM(B2>=50)
    3. C=IF(B2=50,"Pass","Fail")
    4. D=IF(B2>=50,"Pass","Fail")
  5. 5Which chart type is best for showing how sales changed each month over a year?1
    1. ADoughnut
    2. BLine
    3. CScatter
    4. DPie
  6. 6A VAT rate is stored in cell E1 and a formula in C2 is =B2*E1. What happens when it is copied down?1
    1. AE1 becomes E2, E3 and so on, which gives wrong answers
    2. BThe VAT rate doubles
    3. CIt works correctly
    4. DThe spreadsheet shows an error message
  7. 7What does =COUNTIF(B2:B20,">50") do?1
    1. AFinds the largest value above 50
    2. BAdds up all values above 50
    3. CHighlights cells above 50
    4. DCounts how many cells in the range hold a value above 50
  8. 8Why should values be put in cells rather than typed into formulas?1
    1. AChanging the value once updates every formula that uses it
    2. BIt makes the file smaller
    3. CIt makes the spreadsheet calculate faster
    4. DFormulas cannot contain numbers
  9. 9What is conditional formatting?1
    1. AFormatting a cell as currency
    2. BAdding a chart to a spreadsheet
    3. CAutomatically changing a cell's appearance based on its value
    4. DRestricting what can be typed into a cell
  10. 10A spreadsheet is used to work out the effect of a price rise on profit. What is this an example of?1
    1. AValidation
    2. BFormatting
    3. CCompression
    4. DModelling

Section B

Answer in the space provided. The number of marks tells you how many separate points to make.

  1. 1StateState what a formula in a spreadsheet must begin with, and give one example of a formula that adds cells B2 and C2.2
  2. 2ExplainExplain the difference between a relative and an absolute cell reference, and give an example of when an absolute reference is needed.4
  3. 3WriteWrite a formula that displays Merit if the mark in B2 is 70 or above, Pass if it is 50 or above, and Fail otherwise.3
  4. 4ExplainA student has created a spreadsheet to model the cost of a school trip. Explain two features of a well designed spreadsheet model.4
  5. 5RecommendA spreadsheet records the temperature recorded each day for a month. Recommend a suitable chart type and justify your choice.3

Mark scheme

Section A

1A2C3B4D5B6A7D8A9C10D

Section B

1 State what a formula in a spreadsheet must begin with, and give one example of a formula that adds cells B2 and C2. 2

One mark for each of the following, to a maximum of 2.

  • A formula must begin with an equals sign
  • Correct example such as =B2+C2

Model answer. Every formula must begin with an equals sign, which is what tells the spreadsheet that the contents of the cell should be calculated rather than treated as ordinary text. An example that adds cells B2 and C2 together is =B2+C2.

2 Explain the difference between a relative and an absolute cell reference, and give an example of when an absolute reference is needed. 4

One mark for each of the following, to a maximum of 4.

  • A relative reference changes when the formula is copied
  • An absolute reference stays the same because it is locked with dollar signs
  • Gives an example such as a VAT rate or a fixed price stored in one cell
  • Explains that without the dollar signs the reference would move and give wrong results

Model answer. A relative reference such as B2 adjusts automatically when a formula is copied, so copying a formula down one row changes B2 to B3. This is usually what you want, because each row of a calculation should refer to its own data. An absolute reference such as $E$1 is locked by the dollar signs, so it stays pointing at exactly the same cell no matter where the formula is copied. An absolute reference is needed when every row must refer to the same single value, for example a VAT rate stored once in cell E1. If the formula were written as =B2*E1 and copied down, the reference would slide to E2, then E3, which are empty cells, so every result after the first row would be wrong.

3 Write a formula that displays Merit if the mark in B2 is 70 or above, Pass if it is 50 or above, and Fail otherwise. 3

One mark for each of the following, to a maximum of 3.

  • Uses a nested IF function
  • Tests the highest condition first
  • Correct outcomes for all three cases

Model answer. =IF(B2>=70,"Merit",IF(B2>=50,"Pass","Fail")) The outer IF tests the most restrictive condition first, so a mark of 85 is correctly given Merit. Only if that condition is false does the spreadsheet evaluate the inner IF, which then checks for 50 or above and gives Pass, with Fail as the final alternative. Testing the conditions in the wrong order would mean a mark of 85 was caught by the 50 test and wrongly labelled Pass.

4 A student has created a spreadsheet to model the cost of a school trip. Explain two features of a well designed spreadsheet model. 4

One mark for each of the following, to a maximum of 4.

  • Input values are kept in clearly labelled cells rather than inside formulas
  • So a value can be changed once and every calculation updates
  • Cell references are used in formulas rather than typed numbers
  • Formatting such as currency, headings and conditional formatting makes it clear and reduces errors

Model answer. The first feature is that all the input values, such as the coach hire cost, the entry price per student and the number of students, are kept in their own clearly labelled cells, usually grouped together at the top or on a separate sheet. This means the model can answer what if questions instantly: changing the number of students in one cell updates every dependent calculation automatically, which is the entire point of building a model rather than working the figures out by hand. The second feature is that every formula uses cell references rather than typed numbers, so no value is buried where it cannot be seen or updated. Alongside this, sensible formatting matters: currency values formatted as currency, clear headings and borders so the structure is obvious, and conditional formatting to highlight if the total goes over budget, all of which make the model easier to read and much less likely to be misused.

5 A spreadsheet records the temperature recorded each day for a month. Recommend a suitable chart type and justify your choice. 3

One mark for each of the following, to a maximum of 3.

  • Recommends a line chart
  • The data shows change over time
  • A line chart makes the trend and any patterns easy to see

Model answer. A line chart is the most suitable choice. The data consists of one measurement taken at regular intervals across a month, so it is a time series, and a line chart is specifically designed to show how a value changes over time. Joining the points with a line makes any trend immediately visible, so it is easy to see whether temperatures rose through the month, whether there was a cold spell, and how much the values varied day to day. A pie chart would be completely unsuitable because the daily temperatures are not parts of a single whole, and a bar chart, while readable, would make the overall trend harder to follow than a continuous line.