Skip to the worksheet
Revision Library
Choose a subject

Spreadsheets and ICT (Foundation) - Worksheets, Questions and Revision

60 original exam-style questions - 38 pages of questions with a full mark scheme - free printable PDF.

This topic is chapter 3 of GCSE Statistics Foundation Topic Workbook 2 and chapter 11 of GCSE Statistics Foundation 10-Minute Tests 1.

Revision Library
revisionlibrary.co.uk
FOUNDATION

2.1 Spreadsheets and ICT (Foundation)

EDEXCEL 1ST0 · Calculator allowed · about 225 minutes
Total Marks
Name: _______________________________    Date: ____ / ____ / ______
Answer ALL questions. Show all your working.
1
A market stall holder keeps a record of the items she sells in a spreadsheet, as shown below.
ABC12345ItemPrice (£)Quantity soldCrisps0.6040Juice0.8525Chocolate bar0.5560Apple0.3015
(a)Write down the value stored in cell B3.(1)
(b)Write down the cell reference of the cell that contains the value 60.(1)
(c)Write down the value stored in cell A5.(1)
(Total for Question 1 is 3 marks)
2
For each statement about spreadsheets, write down whether it is True or False.
(i)A formula typed into a spreadsheet cell must begin with an equals sign (=).(1)
(ii)Once a number has been typed into a spreadsheet cell, it can never be changed afterwards.(1)
(Total for Question 2 is 2 marks)
3
The stall holder from Question 1 could instead add up her total sales by hand, using a calculator and a pen-and-paper list.

Give one advantage of using a spreadsheet, rather than doing the calculation by hand, to keep a record of her sales.
(Total for Question 3 is 1 mark)
4
The stall holder now adds a fourth column, Total takings (£), to work out how much money each item brought in.
ABCD12345ItemPrice (£)Quantity soldTotal takings (£)Crisps0.6040Juice0.8525Chocolate bar0.5560Apple0.3015
(a)Write down the formula that should be typed into cell D2 to work out the total takings for crisps.(1)
(b)Work out the value that would appear in cell D2.(2)
(c)The formula from part (a) is copied down from cell D2 into cell D3. Write down the value that will appear in cell D3.(1)
(Total for Question 4 is 4 marks)
5
A student records the rainfall, in mm, for one week in a spreadsheet, as shown below. She wants cell B9 to show the total rainfall for the week.
AB12345678910DayRainfall (mm)Mon4.5Tue0Wed2.5Thu11.5Fri6Sat1.5Sun2TotalAverage
(a)Write down a spreadsheet formula, using a named function, that could be typed into cell B9 to add up all the rainfall values in cells B2 to B8.(2)
(b)Work out the value that would appear in cell B9.(1)
(Total for Question 5 is 3 marks)
6
The student now wants cell B10 to show the mean rainfall per day for the week, using the spreadsheet from Question 5.
(a)Write down a spreadsheet formula, using a named function, that could be typed into cell B10 to find the mean rainfall for the week.(2)
(b)Given that the total rainfall for the week (cell B9) is 28 mm, work out the value that would appear in cell B10.(2)
(Total for Question 6 is 4 marks)
7
A PE teacher enters the scores, out of 20, that 6 pupils achieved in a fitness test into a spreadsheet, as shown below. She then sorts the data using column B, smallest to largest.
AB1234567NameScore (out of 20)Amir14Beth18Chen9Dana20Ewan11Farah16
(a)Write down the name that would appear in row 2 after the data has been sorted.(1)
(b)Write down the name that would appear in row 7 after the data has been sorted.(1)
(c)Write down the value that would appear in cell B4 after the data has been sorted.(1)
(Total for Question 7 is 3 marks)
8
The teacher then applies a filter to the original (unsorted) spreadsheet from Question 7, so that only pupils who scored 15 or more are shown.
(a)Write down the names of the pupils that would remain visible after this filter is applied.(2)
(b)Give one advantage of using a spreadsheet filter, rather than checking each score by hand, to find the pupils who scored 15 or more.(1)
(Total for Question 8 is 3 marks)
9
A student records, in a spreadsheet, the number of pupils who chose each of 4 sports as their favourite.
SportNumber of pupils
Football12
Swimming6
Athletics5
Rugby7

She highlights this data and uses the spreadsheet's chart tool to produce a chart.
(a)State the type of chart that would be most appropriate for this data: a bar chart, a line graph, or a scatter graph.(1)
(b)Give a reason for your answer to part (a).(1)
(c)A second student says a pie chart would also be a suitable way to display this data, because it can show each sport as a proportion of the 30 pupils asked. Is the second student correct? Give a reason.(1)
(Total for Question 9 is 3 marks)
10
A spreadsheet formula, =B2*C2, is typed into cell D2, and is then copied down into cells D3, D4 and D5.
(a)Explain what happens to the cell references in the formula when it is copied down from cell D2 into cell D3.(2)
(b)A different formula, =B2*$G$1, is typed into cell E2, where cell G1 holds a fixed discount rate. This formula is also copied down into cells E3, E4 and E5. State which cell reference stays exactly the same in every copy of the formula, and name this type of cell reference.(2)
(Total for Question 10 is 4 marks)
11
A market research company plans to find out how often people in a town use public transport. They will use ICT, such as a spreadsheet, at different stages of the statistical enquiry cycle: plan, collect, process and interpret.
(a)Give one way ICT could be used at the collect stage of this enquiry.(1)
(b)Give one way ICT could be used at the process stage of this enquiry.(1)
(c)Give one way ICT could be used at the interpret stage of this enquiry.(1)
(d)Give one limitation of relying on ICT during this enquiry.(1)
(Total for Question 11 is 4 marks)
12
A researcher is deciding how to collect data from 500 customers about their shopping habits. She is considering two methods: a paper questionnaire filled in by hand, or an online survey using ICT.
(a)Give one advantage of using an online survey, rather than a paper questionnaire, to collect data from this many customers.(1)
(b)Give one disadvantage of using an online survey, rather than a paper questionnaire, to collect data from this many customers.(1)
(c)The researcher decides to store all 500 responses in a database, rather than in a single spreadsheet. Give one reason why a database might be more suitable than a spreadsheet for this large amount of data.(1)
(Total for Question 12 is 3 marks)
13
A sports club uses a spreadsheet to keep track of the monthly fees paid by its 5 members, as shown below.
ABCD1NameMonthly fee (GBP)Months paidTotal paid (GBP)2Aaliyah1583Ben12104Chidi15125Divya1866Elin1297
(a)Write down the formula that should be typed into cell D2 to calculate the total amount paid by Aaliyah this year.(1)
(b)Work out the value that would appear in cell D4 (Chidi's total amount paid).(2)
(c)Write down a spreadsheet formula, using a named function, that could be typed into cell D7 to find the grand total paid by all 5 members.(2)
(d)Given that the total amounts paid are Aaliyah £120, Ben £120, Chidi £180, Divya £108 and Elin £108, work out the value that would appear in cell D7.(1)
(Total for Question 13 is 6 marks)
14
The sports club treasurer relies entirely on the spreadsheet formulas from Question 13 to work out how much each member owes.
(a)Give one disadvantage of relying on a spreadsheet formula, rather than checking the calculation by hand, to work out these totals.(1)
(b)Give one way the club could check that the spreadsheet formulas are working correctly.(1)
(Total for Question 14 is 2 marks)
15
A shopkeeper uses a spreadsheet to add VAT to the price of items. Cell F1, elsewhere on the sheet, holds the VAT rate as a decimal, 0.2. Column A holds the item name, column B holds the price before VAT (£), and column C is used to calculate the price including VAT.
ItemPrice before VAT (£)
Kettle25.00
Toaster18.00
Blender32.00

(Kettle is in row 2, Toaster in row 3, Blender in row 4.)
(a)Write down a formula that could be typed into cell C2 to work out the price of the kettle including VAT, using an absolute reference to cell F1.(2)
(b)This formula is copied down from cell C2 into cells C3 and C4. Explain why the reference to cell F1 must be absolute (contain $ signs) for this to work correctly.(2)
(c)Work out the value that would appear in cell C4 (the price of the blender including VAT).(2)
(Total for Question 15 is 6 marks)
16
A student says: "A chart produced by spreadsheet software is always more reliable than a chart drawn by hand."

Comment on this statement, giving reasons for and against it.
(Total for Question 16 is 3 marks)
17
A spreadsheet lists the times, in seconds, taken by 6 runners to finish a 100 m race.
RunnerTime (seconds)
Priya13.9
Omar14.5
Sam12.8
Tia15.2
Leo13.1
Noor14.0
(a)The times are sorted smallest to largest. Write down the runner's name that would appear in row 4 of the sorted list (row 2 is the first data row).(1)
(b)A filter is then applied to the sorted list to show only runners with a time under 14 seconds. Write down the names that would remain visible.(2)
(Total for Question 17 is 3 marks)
18
A national charity wants to survey 10 000 supporters about how often they volunteer, then compare the results year on year.

Recommend whether the charity should collect responses using a paper questionnaire or an online survey tool. Use the statistical enquiry cycle to justify your answer, referring to at least two stages (for example plan, collect, process or interpret).
(Total for Question 18 is 3 marks)
19
A spreadsheet has these entries. Column A holds the labels, column B and column C hold the numbers, and row 1 is the heading row (not shown).
Row (A)B: HoursC: Rate
2: Lemons203
3: Pears52
4: Melons813
5: Cherries1715
6: Plums205

Work out the value produced by each formula.
(a) =SUM(C2:C6)
(b) =COUNTIF(B2:B6,">9")
(c) =AVERAGE(B2:B6)
(Total for Question 19 is 3 marks)
20
Here are the number of hours of sleep per night recorded for 9 people. Valid values are whole numbers from 3 to 12 inclusive.

12, 7, 6, 4, 999, 11, 8, 9, 11

(a) Write down the invalid values.
(b) Work out the mean of all 9 values, to 1 decimal place if needed.
(c) Work out the mean after the invalid values are removed, to 1 decimal place if needed.
(Total for Question 20 is 5 marks)
21
A spreadsheet has these entries. Column A holds the labels, column B and column C hold the numbers, and row 1 is the heading row (not shown).
Row (A)B: HoursC: Rate
2: Lemons1413
3: Melons715
4: Apples1512
5: Plums177
6: Cherries204

Work out the value produced by each formula.
(a) =MAX(C2:C6)
(b) =B3+C3
(c) =MAX(B2:B6)-MIN(B2:B6)
(Total for Question 21 is 3 marks)
22
A spreadsheet has these entries. Column A holds the labels, column B and column C hold the numbers, and row 1 is the heading row (not shown).
Row (A)B: Mark 1C: Mark 2
2: Melons1113
3: Cherries1813
4: Apples209
5: Pears87
6: Grapes69

Work out the value produced by each formula.
(a) =MIN(C2:C6)
(b) =SUM(C2:C6)
(c) =SUM(B2:B6)
(Total for Question 22 is 3 marks)
23
Here are the ages of club members in years recorded for 10 people. Valid values are whole numbers from 11 to 17 inclusive.

11, 17, 12, 17, 17, 14, 17, 999, 0, 13

(a) Write down the invalid values.
(b) Work out the mean of all 10 values, to 1 decimal place if needed.
(c) Work out the mean after the invalid values are removed, to 1 decimal place if needed.
(Total for Question 23 is 5 marks)
24
A spreadsheet has these entries. Column A holds the labels, column B and column C hold the numbers, and row 1 is the heading row (not shown).
Row (A)B: Week 1C: Week 2
2: Cherries209
3: Grapes158
4: Limes78
5: Melons166
6: Plums2011

Work out the value produced by each formula.
(a) =B2*C2
(b) =COUNTIF(B2:B6,">8")
(c) =AVERAGE(B2:B6)
(Total for Question 24 is 3 marks)
25
A spreadsheet has these entries. Column A holds the labels, column B and column C hold the numbers, and row 1 is the heading row (not shown).
Row (A)B: Mark 1C: Mark 2
2: Lemons156
3: Cherries814
4: Plums1415
5: Grapes62
6: Limes1312

Work out the value produced by each formula.
(a) =MAX(B2:B6)-MIN(B2:B6)
(b) =AVERAGE(B2:B6)
(c) =MAX(C2:C6)
(Total for Question 25 is 3 marks)
26
Here are the number of hours of sleep per night recorded for 10 people. Valid values are whole numbers from 3 to 12 inclusive.

120, 5, 8, 9, 1, 10, 3, 9, 8, 8

(a) Write down the invalid values.
(b) Work out the mean of all 10 values, to 1 decimal place if needed.
(c) Work out the mean after the invalid values are removed, to 1 decimal place if needed.
(Total for Question 26 is 5 marks)
27
A spreadsheet has these entries. Column A holds the labels, column B and column C hold the numbers, and row 1 is the heading row (not shown).
Row (A)B: Mark 1C: Mark 2
2: Limes132
3: Plums1612
4: Grapes113
5: Melons202
6: Cherries95

Work out the value produced by each formula.
(a) =MIN(C2:C6)
(b) =AVERAGE(B2:B6)
(c) =B3+C3
(Total for Question 27 is 3 marks)
28
A spreadsheet has these entries. Column A holds the labels, column B and column C hold the numbers, and row 1 is the heading row (not shown).
Row (A)B: Week 1C: Week 2
2: Pears315
3: Melons814
4: Apples514
5: Cherries46
6: Limes153

Work out the value produced by each formula.
(a) =SUM(B2:B6)
(b) =B3+C3
(c) =MIN(C2:C6)
(Total for Question 28 is 3 marks)
29
Here are the heights of pupils in centimetres recorded for 10 people. Valid values are whole numbers from 130 to 190 inclusive.

153, 1900, 172, 170, 147, 134, 999, 186, 190, 169

(a) Write down the invalid values.
(b) Work out the mean of all 10 values, to 1 decimal place if needed.
(c) Work out the mean after the invalid values are removed, to 1 decimal place if needed.
(Total for Question 29 is 5 marks)
30
A spreadsheet has these entries. Column A holds the labels, column B and column C hold the numbers, and row 1 is the heading row (not shown).
Row (A)B: Mark 1C: Mark 2
2: Lemons1814
3: Plums811
4: Grapes87
5: Apples175
6: Cherries712

Work out the value produced by each formula.
(a) =COUNTIF(B2:B6,">11")
(b) =MAX(B2:B6)-MIN(B2:B6)
(c) =AVERAGE(B2:B6)
(Total for Question 30 is 3 marks)
31
A spreadsheet has these entries. Column A holds the labels, column B and column C hold the numbers, and row 1 is the heading row (not shown).
Row (A)B: Week 1C: Week 2
2: Cherries1512
3: Pears1312
4: Plums53
5: Lemons33
6: Apples1110

Work out the value produced by each formula.
(a) =SUM(C2:C6)
(b) =AVERAGE(B2:B6)
(c) =MAX(C2:C6)
(Total for Question 31 is 3 marks)
32
Here are the shoe sizes recorded for 10 people. Valid values are whole numbers from 3 to 12 inclusive.

3, 5, 10, 5, 9, 9, 120, 8, 11, 9

(a) Write down the invalid values.
(b) Work out the mean of all 10 values, to 1 decimal place if needed.
(c) Work out the mean after the invalid values are removed, to 1 decimal place if needed.
(Total for Question 32 is 5 marks)
33
A spreadsheet has these entries. Column A holds the labels, column B and column C hold the numbers, and row 1 is the heading row (not shown).
Row (A)B: HoursC: Rate
2: Melons1810
3: Pears1411
4: Limes69
5: Apples47
6: Grapes95

Work out the value produced by each formula.
(a) =COUNTIF(B2:B6,">11")
(b) =AVERAGE(B2:B6)
(c) =B6*C6
(Total for Question 33 is 3 marks)
34
A spreadsheet has these entries. Column A holds the labels, column B and column C hold the numbers, and row 1 is the heading row (not shown).
Row (A)B: Price (pounds)C: Quantity
2: Melons1610
3: Lemons515
4: Cherries412
5: Pears139
6: Plums204

Work out the value produced by each formula.
(a) =COUNTIF(B2:B6,">10")
(b) =MAX(B2:B6)-MIN(B2:B6)
(c) =SUM(C2:C6)
(Total for Question 34 is 3 marks)
35
Here are the ages of club members in years recorded for 10 people. Valid values are whole numbers from 11 to 17 inclusive.

0, 15, 14, 12, 16, 15, 11, 13, 17, 15

(a) Write down the invalid values.
(b) Work out the mean of all 10 values, to 1 decimal place if needed.
(c) Work out the mean after the invalid values are removed, to 1 decimal place if needed.
(Total for Question 35 is 5 marks)
36
A spreadsheet has these entries. Column A holds the labels, column B and column C hold the numbers, and row 1 is the heading row (not shown).
Row (A)B: Price (pounds)C: Quantity
2: Grapes99
3: Limes1914
4: Pears85
5: Lemons72
6: Apples1613

Work out the value produced by each formula.
(a) =MAX(C2:C6)
(b) =COUNTIF(B2:B6,">12")
(c) =AVERAGE(B2:B6)
(Total for Question 36 is 3 marks)
37
A spreadsheet has these entries. Column A holds the labels, column B and column C hold the numbers, and row 1 is the heading row (not shown).
Row (A)B: Week 1C: Week 2
2: Pears122
3: Lemons156
4: Plums207
5: Melons184
6: Cherries1712

Work out the value produced by each formula.
(a) =AVERAGE(B2:B6)
(b) =SUM(C2:C6)
(c) =SUM(B2:B6)
(Total for Question 37 is 3 marks)
38
Here are the number of hours of sleep per night recorded for 8 people. Valid values are whole numbers from 3 to 12 inclusive.

999, 9, 11, 6, 10, 12, 6, 7

(a) Write down the invalid values.
(b) Work out the mean of all 8 values, to 1 decimal place if needed.
(c) Work out the mean after the invalid values are removed, to 1 decimal place if needed.
(Total for Question 38 is 5 marks)
39
A spreadsheet has these entries. Column A holds the labels, column B and column C hold the numbers, and row 1 is the heading row (not shown).
Row (A)B: Price (pounds)C: Quantity
2: Lemons214
3: Pears127
4: Cherries1512
5: Apples1813
6: Limes142

Work out the value produced by each formula.
(a) =COUNTIF(B2:B6,">12")
(b) =SUM(B2:B6)
(c) =MAX(B2:B6)-MIN(B2:B6)
(Total for Question 39 is 3 marks)
40
Here are the ages of club members in years recorded for 10 people. Valid values are whole numbers from 11 to 17 inclusive.

11, -1, 14, 17, 3, 17, 16, 13, 11, 17

(a) Write down the invalid values.
(b) Work out the mean of all 10 values, to 1 decimal place if needed.
(c) Work out the mean after the invalid values are removed, to 1 decimal place if needed.
(Total for Question 40 is 5 marks)
41
A spreadsheet has these entries. Column A holds the labels, column B and column C hold the numbers, and row 1 is the heading row (not shown).
Row (A)B: Mark 1C: Mark 2
2: Apples147
3: Plums182
4: Lemons1011
5: Cherries813
6: Limes28

Work out the value produced by each formula.
(a) =MIN(C2:C6)
(b) =COUNTIF(B2:B6,">8")
(c) =AVERAGE(B2:B6)
(Total for Question 41 is 3 marks)
42
Here are the number of hours of sleep per night recorded for 8 people. Valid values are whole numbers from 3 to 12 inclusive.

12, 11, 3, -1, 11, 3, 3, 12

(a) Write down the invalid values.
(b) Work out the mean of all 8 values, to 1 decimal place if needed.
(c) Work out the mean after the invalid values are removed, to 1 decimal place if needed.
(Total for Question 42 is 5 marks)
43
A spreadsheet has these entries. Column A holds the labels, column B and column C hold the numbers, and row 1 is the heading row (not shown).
Row (A)B: HoursC: Rate
2: Pears132
3: Lemons69
4: Plums1614
5: Melons89
6: Apples510

Work out the value produced by each formula.
(a) =B2*C2
(b) =AVERAGE(B2:B6)
(c) =MIN(C2:C6)
(Total for Question 43 is 3 marks)
44
A spreadsheet has these entries. Column A holds the labels, column B and column C hold the numbers, and row 1 is the heading row (not shown).
Row (A)B: Mark 1C: Mark 2
2: Grapes47
3: Plums104
4: Melons152
5: Lemons2015
6: Cherries1011

Work out the value produced by each formula.
(a) =B6*C6
(b) =B2*C2
(c) =COUNTIF(B2:B6,">10")
(Total for Question 44 is 3 marks)
45
Here are the number of hours of sleep per night recorded for 8 people. Valid values are whole numbers from 3 to 12 inclusive.

12, 6, 12, 4, 120, 6, 7, 0

(a) Write down the invalid values.
(b) Work out the mean of all 8 values, to 1 decimal place if needed.
(c) Work out the mean after the invalid values are removed, to 1 decimal place if needed.
(Total for Question 45 is 5 marks)
46
A spreadsheet has these entries. Column A holds the labels, column B and column C hold the numbers, and row 1 is the heading row (not shown).
Row (A)B: HoursC: Rate
2: Cherries188
3: Plums1311
4: Limes313
5: Lemons118
6: Apples813

Work out the value produced by each formula.
(a) =B6*C6
(b) =B3+C3
(c) =B2*C2
(Total for Question 46 is 3 marks)
47
A spreadsheet has these entries. Column A holds the labels, column B and column C hold the numbers, and row 1 is the heading row (not shown).
Row (A)B: Price (pounds)C: Quantity
2: Melons193
3: Apples135
4: Pears93
5: Plums1815
6: Grapes1313

Work out the value produced by each formula.
(a) =B2*C2
(b) =SUM(C2:C6)
(c) =MAX(B2:B6)-MIN(B2:B6)
(Total for Question 47 is 3 marks)
48
Here are the shoe sizes recorded for 10 people. Valid values are whole numbers from 3 to 12 inclusive.

10, 120, 4, 12, 3, 8, -1, 9, 9, 12

(a) Write down the invalid values.
(b) Work out the mean of all 10 values, to 1 decimal place if needed.
(c) Work out the mean after the invalid values are removed, to 1 decimal place if needed.
(Total for Question 48 is 5 marks)
49
A spreadsheet has these entries. Column A holds the labels, column B and column C hold the numbers, and row 1 is the heading row (not shown).
Row (A)B: Mark 1C: Mark 2
2: Plums172
3: Grapes23
4: Cherries155
5: Pears135
6: Melons1612

Work out the value produced by each formula.
(a) =COUNTIF(B2:B6,">12")
(b) =SUM(B2:B6)
(c) =MAX(C2:C6)
(Total for Question 49 is 3 marks)
50
A spreadsheet has these entries. Column A holds the labels, column B and column C hold the numbers, and row 1 is the heading row (not shown).
Row (A)B: HoursC: Rate
2: Apples1415
3: Grapes175
4: Cherries1510
5: Melons214
6: Pears143

Work out the value produced by each formula.
(a) =B3+C3
(b) =COUNTIF(B2:B6,">7")
(c) =SUM(B2:B6)
(Total for Question 50 is 3 marks)
51
Here are the ages of club members in years recorded for 10 people. Valid values are whole numbers from 11 to 17 inclusive.

13, 14, 17, 14, 14, 11, 17, 170, 16, 11

(a) Write down the invalid values.
(b) Work out the mean of all 10 values, to 1 decimal place if needed.
(c) Work out the mean after the invalid values are removed, to 1 decimal place if needed.
(Total for Question 51 is 5 marks)
52
A spreadsheet has these entries. Column A holds the labels, column B and column C hold the numbers, and row 1 is the heading row (not shown).
Row (A)B: HoursC: Rate
2: Pears136
3: Limes64
4: Grapes134
5: Apples196
6: Plums1711

Work out the value produced by each formula.
(a) =B2*C2
(b) =COUNTIF(B2:B6,">7")
(c) =SUM(C2:C6)
(Total for Question 52 is 3 marks)
53
Here are the ages of club members in years recorded for 9 people. Valid values are whole numbers from 11 to 17 inclusive.

15, 11, 17, 17, 15, 3, 15, 999, 17

(a) Write down the invalid values.
(b) Work out the mean of all 9 values, to 1 decimal place if needed.
(c) Work out the mean after the invalid values are removed, to 1 decimal place if needed.
(Total for Question 53 is 5 marks)
54
A spreadsheet has these entries. Column A holds the labels, column B and column C hold the numbers, and row 1 is the heading row (not shown).
Row (A)B: HoursC: Rate
2: Limes1011
3: Pears206
4: Melons184
5: Plums1912
6: Grapes156

Work out the value produced by each formula.
(a) =MIN(C2:C6)
(b) =AVERAGE(B2:B6)
(c) =B2*C2
(Total for Question 54 is 3 marks)
55
Here are the shoe sizes recorded for 8 people. Valid values are whole numbers from 3 to 12 inclusive.

8, 11, 4, 5, 6, 11, 5, 1

(a) Write down the invalid values.
(b) Work out the mean of all 8 values, to 1 decimal place if needed.
(c) Work out the mean after the invalid values are removed, to 1 decimal place if needed.
(Total for Question 55 is 5 marks)
56
A spreadsheet has these entries. Column A holds the labels, column B and column C hold the numbers, and row 1 is the heading row (not shown).
Row (A)B: Mark 1C: Mark 2
2: Pears138
3: Plums1911
4: Cherries172
5: Grapes155
6: Melons1911

Work out the value produced by each formula.
(a) =B3+C3
(b) =SUM(B2:B6)
(c) =B2*C2
(Total for Question 56 is 3 marks)
57
A spreadsheet has these entries. Column A holds the labels, column B and column C hold the numbers, and row 1 is the heading row (not shown).
Row (A)B: Mark 1C: Mark 2
2: Grapes1310
3: Melons1414
4: Pears911
5: Cherries73
6: Apples112

Work out the value produced by each formula.
(a) =B6*C6
(b) =MIN(C2:C6)
(c) =B2*C2
(Total for Question 57 is 3 marks)
58
A spreadsheet has these entries. Column A holds the labels, column B and column C hold the numbers, and row 1 is the heading row (not shown).
Row (A)B: Price (pounds)C: Quantity
2: Grapes1010
3: Cherries63
4: Plums185
5: Lemons1215
6: Apples1513

Work out the value produced by each formula.
(a) =MAX(C2:C6)
(b) =COUNTIF(B2:B6,">11")
(c) =MIN(C2:C6)
(Total for Question 58 is 2 marks)
59
A spreadsheet has these entries. Column A holds the labels, column B and column C hold the numbers, and row 1 is the heading row (not shown).
Row (A)B: Price (pounds)C: Quantity
2: Cherries67
3: Lemons65
4: Apples39
5: Grapes414
6: Melons127

Work out the value produced by each formula.
(a) =COUNTIF(B2:B6,">10")
(b) =MIN(C2:C6)
(c) =MAX(B2:B6)-MIN(B2:B6)
(Total for Question 59 is 3 marks)
60
A spreadsheet has these entries. Column A holds the labels, column B and column C hold the numbers, and row 1 is the heading row (not shown).
Row (A)B: Price (pounds)C: Quantity
2: Grapes103
3: Melons123
4: Cherries1914
5: Apples68
6: Plums69

Work out the value produced by each formula.
(a) =B2*C2
(b) =AVERAGE(B2:B6)
(c) =B3+C3
(Total for Question 60 is 3 marks)
Mark scheme · 2.1 Spreadsheets and ICT (Foundation)

Question 1

  • (a) 0.85 oe (£0.85) caoB1
  • (a) Answer: 0.85
  • (b) C4 caoB1
  • (b) Answer: C4
  • (c) Apple caoB1
  • (c) Answer: Apple

Question 2

  • (i) True caoB1
  • (i) Answer: True
  • (ii) False caoB1
  • (ii) Answer: False

Question 3

  • any valid advantage, e.g. it recalculates totals automatically when a value changes, oe it is faster, oe it reduces the chance of an arithmetic errorB1
  • Answer: A spreadsheet updates every total automatically when a number is changed, so there is less chance of making an arithmetic error.

Question 4

  • (a) =B2*C2 oe (accept =C2*B2)B1
  • (a) Answer: =B2*C2
  • (b) 0.60 x 40, oeM1
  • (b) 24 or 24.00 caoA1
  • (b) Answer: 24.00
  • (c) 21.25 ft (0.85 x 25)B1
  • (c) Answer: 21.25

Question 5

  • (a) SUM used as a named function, e.g. =SUM(...)B1
  • (a) correct range B2:B8B1
  • (a) Answer: =SUM(B2:B8)
  • (b) 28 (mm) caoB1
  • (b) Answer: 28

Question 6

  • (a) AVERAGE used as a named function, e.g. =AVERAGE(...)B1
  • (a) correct range B2:B8B1
  • (a) Answer: =AVERAGE(B2:B8)
  • (b) 28 / 7, oeM1
  • (b) 4 (mm) caoA1
  • (b) Answer: 4

Question 7

  • (a) Chen caoB1
  • (a) Answer: Chen
  • (b) Dana caoB1
  • (b) Answer: Dana
  • (c) 14 caoB1
  • (c) Answer: 14

Question 8

  • (a) Beth and Dana identified (scores of 18 and 20, both at least 15)B1
  • (a) Farah also identified, with Amir, Chen and Ewan correctly excludedB1
  • (a) Answer: Beth, Dana, Farah
  • (b) any valid advantage, e.g. it is quicker, oe it is less likely that a qualifying pupil is missed or an error is madeB1
  • (b) Answer: It is quicker and less likely that a pupil who qualifies is accidentally missed.

Question 9

  • (a) bar chart caoB1
  • (a) Answer: Bar chart
  • (b) valid reason, e.g. the sports are categories, not points in time or paired numerical values, so a line graph or scatter graph would not be suitableB1
  • (b) Answer: A line graph is for data collected over time and a scatter graph is for pairs of numerical values, but sport is a category, so neither would be suitable here.
  • (c) Yes, with a valid reason, e.g. the data is categorical and the frequencies add up to a total (30 pupils), so a pie chart can show each sport's share of that totalB1
  • (c) Answer: Yes. Because the sports are categories that add up to a total of 30 pupils, a pie chart can show each sport's share of the whole group.

Question 10

  • (a) the cell references adjust/change automatically as the formula is copied downB1
  • (a) correctly states the copied formula becomes =B3*C3B1
  • (a) Answer: The references shift down a row with the formula, so the copy in D3 becomes =B3*C3.
  • (b) G1 stays the same in every copyB1
  • (b) named as an absolute cell reference oe (fixed by the $ signs)B1
  • (b) Answer: G1 stays the same in every copy; this is called an absolute cell reference.

Question 11

  • (a) any valid use, e.g. an online survey/questionnaire gathers responses directly into a spreadsheet or databaseB1
  • (a) Answer: An online questionnaire could collect people's answers directly into a spreadsheet.
  • (b) any valid use, e.g. a named function such as SUM or AVERAGE calculates a total or the mean number of journeysB1
  • (b) Answer: A spreadsheet function such as AVERAGE could calculate the mean number of journeys per person.
  • (c) any valid use, e.g. a chart/graph produced from the spreadsheet data helps to identify patterns or trendsB1
  • (c) Answer: A chart produced from the spreadsheet data could help show which age groups use public transport most.
  • (d) any valid limitation, e.g. data entered wrongly or a formula containing an error could lead to a wrong conclusion if nobody checks itB1
  • (d) Answer: If the data is typed in wrongly, or a formula has an error, the conclusions could be wrong unless someone checks the results.

Question 12

  • (a) any valid advantage, e.g. responses are collected and stored automatically, so results do not need to be entered by hand and can be processed much fasterB1
  • (a) Answer: Responses go straight into a spreadsheet or database, so nobody has to type up 500 paper forms by hand, which saves a lot of time.
  • (b) any valid disadvantage, e.g. customers without internet access or a suitable device cannot take part, so the sample might not be representativeB1
  • (b) Answer: Customers who do not use the internet cannot respond, so the results might not represent all 500 customers fairly.
  • (c) any valid reason, e.g. a database can store, search and organise a much larger amount of data more efficiently, or allows several people to access and update records at the same timeB1
  • (c) Answer: A database is built to store and search through large numbers of records efficiently, and can let several people update it at once.

Question 13

  • (a) =B2*C2 oeB1
  • (a) Answer: =B2*C2
  • (b) 15 x 12, oeM1
  • (b) 180 caoA1
  • (b) Answer: 180
  • (c) SUM used as a named function, e.g. =SUM(...)B1
  • (c) correct range D2:D6B1
  • (c) Answer: =SUM(D2:D6)
  • (d) 636 caoB1
  • (d) Answer: 636

Question 14

  • (a) any valid disadvantage, e.g. if the formula contains an error (such as the wrong cell reference), every value calculated from it will also be wrong, and this might not be noticedB1
  • (a) Answer: If a formula has a mistake in it, such as the wrong cell reference, every total it produces will be wrong, and this could easily go unnoticed.
  • (b) any valid check, e.g. calculate one member's total by hand and compare it with the spreadsheet's answerB1
  • (b) Answer: Work out one member's total by hand and check that it matches the value the spreadsheet gives.

Question 15

  • (a) correct structure, e.g. =B2*(1+F1) oe (accept =B2+B2*F1)B1
  • (a) the reference to F1 is written as an absolute reference, $F$1B1
  • (a) Answer: =B2*(1+$F$1)
  • (b) without $ signs, the reference would change/shift when copied down, e.g. to F2 then F3B1
  • (b) F2 and F3 are empty, so the copied formulas would give the wrong answer (e.g. zero VAT) instead of using the rate in F1B1
  • (b) Answer: Without the $ signs, the reference would shift down to F2 and then F3 as the formula is copied, and those cells are empty, so the toaster and blender would be given the wrong price instead of using the VAT rate in F1.
  • (c) 32 x 1.2, oeM1
  • (c) 38.40 cao (accept 38.4)A1
  • (c) Answer: 38.40

Question 16

  • a clear judgement is given (e.g. partly agree/it depends), rather than a simple yes or no with no commentB1
  • a reason supporting the statement, e.g. the spreadsheet plots values exactly as entered, avoiding the plotting/measuring errors that can happen when a chart is drawn by handB1
  • a reason against the statement, e.g. the chart is only as accurate as the data or formulas entered, so wrong data or a formula error still produces a wrong (but neatly drawn) chartB1
  • Answer: It depends. A spreadsheet chart avoids hand-drawing errors because it plots the exact values entered, but it is only as reliable as the data and formulas behind it, so a mistake when entering the data would still produce an inaccurate chart.

Question 17

  • (a) Priya caoB1
  • (a) Answer: Priya
  • (b) Sam and Leo identified (12.8 and 13.1, both under 14 seconds)B1
  • (b) Priya also identified, with Noor, Omar and Tia correctly excluded (Noor's 14.0 is not under 14)B1
  • (b) Answer: Sam, Leo, Priya

Question 18

  • a clear recommendation is given (online survey tool, for a group this size)B1
  • a justified point linked to one stage of the enquiry cycle, e.g. at the collect stage an online tool gathers 10 000 responses directly into a spreadsheet/database without anyone re-typing paper formsB1
  • a justified point linked to a second stage of the enquiry cycle, e.g. at the process/interpret stage the same spreadsheet functions and charts can be reused each year, making year-on-year comparison quicker and more consistentB1
  • Answer: An online survey tool. At the collect stage it gathers all 10 000 responses directly into a spreadsheet or database without anyone re-typing paper forms, and at the process/interpret stage the same formulas and charts can be reused each year, making year-on-year comparison quicker and more consistent.

Question 19

  • 38 caoB1
  • 3 caoB1
  • 14 caoB1
  • Answer: (a) 38; (b) 3; (c) 14

Question 20

  • invalid values 999B1
  • sum of all values divided by 9M1
  • 118.6 caoA1
  • sum of valid values divided by 8M1
  • 8.5 caoA1
  • Answer: (a) 999; (b) 118.6; (c) 8.5

Question 21

  • 15 caoB1
  • 22 caoB1
  • 13 caoB1
  • Answer: (a) 15; (b) 22; (c) 13

Question 22

  • 7 caoB1
  • 51 caoB1
  • 63 caoB1
  • Answer: (a) 7; (b) 51; (c) 63

Question 23

  • invalid values 999, 0B1
  • sum of all values divided by 10M1
  • 111.7 caoA1
  • sum of valid values divided by 8M1
  • 14.8 caoA1
  • Answer: (a) 999, 0; (b) 111.7; (c) 14.8

Question 24

  • 180 caoB1
  • 4 caoB1
  • 15.6 caoB1
  • Answer: (a) 180; (b) 4; (c) 15.6

Question 25

  • 9 caoB1
  • 11.2 caoB1
  • 15 caoB1
  • Answer: (a) 9; (b) 11.2; (c) 15

Question 26

  • invalid values 120, 1B1
  • sum of all values divided by 10M1
  • 18.1 caoA1
  • sum of valid values divided by 8M1
  • 7.5 caoA1
  • Answer: (a) 120, 1; (b) 18.1; (c) 7.5

Question 27

  • 2 caoB1
  • 13.8 caoB1
  • 28 caoB1
  • Answer: (a) 2; (b) 13.8; (c) 28

Question 28

  • 35 caoB1
  • 22 caoB1
  • 3 caoB1
  • Answer: (a) 35; (b) 22; (c) 3

Question 29

  • invalid values 1900, 999B1
  • sum of all values divided by 10M1
  • 422 caoA1
  • sum of valid values divided by 8M1
  • 165.1 caoA1
  • Answer: (a) 1900, 999; (b) 422; (c) 165.1

Question 30

  • 2 caoB1
  • 11 caoB1
  • 11.6 caoB1
  • Answer: (a) 2; (b) 11; (c) 11.6

Question 31

  • 40 caoB1
  • 9.4 caoB1
  • 12 caoB1
  • Answer: (a) 40; (b) 9.4; (c) 12

Question 32

  • invalid values 120B1
  • sum of all values divided by 10M1
  • 18.9 caoA1
  • sum of valid values divided by 9M1
  • 7.7 caoA1
  • Answer: (a) 120; (b) 18.9; (c) 7.7

Question 33

  • 2 caoB1
  • 10.2 caoB1
  • 45 caoB1
  • Answer: (a) 2; (b) 10.2; (c) 45

Question 34

  • 3 caoB1
  • 16 caoB1
  • 50 caoB1
  • Answer: (a) 3; (b) 16; (c) 50

Question 35

  • invalid values 0B1
  • sum of all values divided by 10M1
  • 12.8 caoA1
  • sum of valid values divided by 9M1
  • 14.2 caoA1
  • Answer: (a) 0; (b) 12.8; (c) 14.2

Question 36

  • 14 caoB1
  • 2 caoB1
  • 11.8 caoB1
  • Answer: (a) 14; (b) 2; (c) 11.8

Question 37

  • 16.4 caoB1
  • 31 caoB1
  • 82 caoB1
  • Answer: (a) 16.4; (b) 31; (c) 82

Question 38

  • invalid values 999B1
  • sum of all values divided by 8M1
  • 132.5 caoA1
  • sum of valid values divided by 7M1
  • 8.7 caoA1
  • Answer: (a) 999; (b) 132.5; (c) 8.7

Question 39

  • 3 caoB1
  • 61 caoB1
  • 16 caoB1
  • Answer: (a) 3; (b) 61; (c) 16

Question 40

  • invalid values -1, 3B1
  • sum of all values divided by 10M1
  • 11.8 caoA1
  • sum of valid values divided by 8M1
  • 14.5 caoA1
  • Answer: (a) -1, 3; (b) 11.8; (c) 14.5

Question 41

  • 2 caoB1
  • 3 caoB1
  • 10.4 caoB1
  • Answer: (a) 2; (b) 3; (c) 10.4

Question 42

  • invalid values -1B1
  • sum of all values divided by 8M1
  • 6.8 caoA1
  • sum of valid values divided by 7M1
  • 7.9 caoA1
  • Answer: (a) -1; (b) 6.8; (c) 7.9

Question 43

  • 26 caoB1
  • 9.6 caoB1
  • 2 caoB1
  • Answer: (a) 26; (b) 9.6; (c) 2

Question 44

  • 110 caoB1
  • 28 caoB1
  • 2 caoB1
  • Answer: (a) 110; (b) 28; (c) 2

Question 45

  • invalid values 120, 0B1
  • sum of all values divided by 8M1
  • 20.9 caoA1
  • sum of valid values divided by 6M1
  • 7.8 caoA1
  • Answer: (a) 120, 0; (b) 20.9; (c) 7.8

Question 46

  • 104 caoB1
  • 24 caoB1
  • 144 caoB1
  • Answer: (a) 104; (b) 24; (c) 144

Question 47

  • 57 caoB1
  • 39 caoB1
  • 10 caoB1
  • Answer: (a) 57; (b) 39; (c) 10

Question 48

  • invalid values 120, -1B1
  • sum of all values divided by 10M1
  • 18.6 caoA1
  • sum of valid values divided by 8M1
  • 8.4 caoA1
  • Answer: (a) 120, -1; (b) 18.6; (c) 8.4

Question 49

  • 4 caoB1
  • 63 caoB1
  • 12 caoB1
  • Answer: (a) 4; (b) 63; (c) 12

Question 50

  • 22 caoB1
  • 4 caoB1
  • 62 caoB1
  • Answer: (a) 22; (b) 4; (c) 62

Question 51

  • invalid values 170B1
  • sum of all values divided by 10M1
  • 29.7 caoA1
  • sum of valid values divided by 9M1
  • 14.1 caoA1
  • Answer: (a) 170; (b) 29.7; (c) 14.1

Question 52

  • 78 caoB1
  • 4 caoB1
  • 31 caoB1
  • Answer: (a) 78; (b) 4; (c) 31

Question 53

  • invalid values 3, 999B1
  • sum of all values divided by 9M1
  • 123.2 caoA1
  • sum of valid values divided by 7M1
  • 15.3 caoA1
  • Answer: (a) 3, 999; (b) 123.2; (c) 15.3

Question 54

  • 4 caoB1
  • 16.4 caoB1
  • 110 caoB1
  • Answer: (a) 4; (b) 16.4; (c) 110

Question 55

  • invalid values 1B1
  • sum of all values divided by 8M1
  • 6.4 caoA1
  • sum of valid values divided by 7M1
  • 7.1 caoA1
  • Answer: (a) 1; (b) 6.4; (c) 7.1

Question 56

  • 30 caoB1
  • 83 caoB1
  • 104 caoB1
  • Answer: (a) 30; (b) 83; (c) 104

Question 57

  • 22 caoB1
  • 2 caoB1
  • 130 caoB1
  • Answer: (a) 22; (b) 2; (c) 130

Question 58

  • 15 caoB1
  • 3 caoB1
  • Answer: (a) 15; (b) 3; (c) 3

Question 59

  • 1 caoB1
  • 5 caoB1
  • 9 caoB1
  • Answer: (a) 1; (b) 5; (c) 9

Question 60

  • 30 caoB1
  • 10.6 caoB1
  • 15 caoB1
  • Answer: (a) 30; (b) 10.6; (c) 15

Mark your answers

Check answers without an account. Some answers can be checked automatically; use the mark scheme to mark the rest.

Question 1

3 marks
Did your answer earn the marks?

Question 2

2 marks
Did your answer earn the marks?

Question 3

1 mark
Did your answer earn the marks?

Question 4

4 marks
Did your answer earn the marks?

Question 5

3 marks
Did your answer earn the marks?

Question 6

4 marks
Did your answer earn the marks?

Question 7

3 marks
Did your answer earn the marks?

Question 8

3 marks
Did your answer earn the marks?

Question 9

3 marks
Did your answer earn the marks?

Question 10

4 marks
Did your answer earn the marks?

Question 11

4 marks
Did your answer earn the marks?

Question 12

3 marks
Did your answer earn the marks?

Question 13

6 marks
Did your answer earn the marks?

Question 14

2 marks
Did your answer earn the marks?

Question 15

6 marks
Did your answer earn the marks?

Question 16

3 marks
Did your answer earn the marks?

Question 17

3 marks
Did your answer earn the marks?

Question 18

3 marks
Did your answer earn the marks?

Question 19

3 marks
Did your answer earn the marks?

Question 20

5 marks
Did your answer earn the marks?

Question 21

3 marks
Did your answer earn the marks?

Question 22

3 marks
Did your answer earn the marks?

Question 23

5 marks
Did your answer earn the marks?

Question 24

3 marks
Did your answer earn the marks?

Question 25

3 marks
Did your answer earn the marks?

Question 26

5 marks
Did your answer earn the marks?

Question 27

3 marks
Did your answer earn the marks?

Question 28

3 marks
Did your answer earn the marks?

Question 29

5 marks
Did your answer earn the marks?

Question 30

3 marks
Did your answer earn the marks?

Question 31

3 marks
Did your answer earn the marks?

Question 32

5 marks
Did your answer earn the marks?

Question 33

3 marks
Did your answer earn the marks?

Question 34

3 marks
Did your answer earn the marks?

Question 35

5 marks
Did your answer earn the marks?

Question 36

3 marks
Did your answer earn the marks?

Question 37

3 marks
Did your answer earn the marks?

Question 38

5 marks
Did your answer earn the marks?

Question 39

3 marks
Did your answer earn the marks?

Question 40

5 marks
Did your answer earn the marks?

Question 41

3 marks
Did your answer earn the marks?

Question 42

5 marks
Did your answer earn the marks?

Question 43

3 marks
Did your answer earn the marks?

Question 44

3 marks
Did your answer earn the marks?

Question 45

5 marks
Did your answer earn the marks?

Question 46

3 marks
Did your answer earn the marks?

Question 47

3 marks
Did your answer earn the marks?

Question 48

5 marks
Did your answer earn the marks?

Question 49

3 marks
Did your answer earn the marks?

Question 50

3 marks
Did your answer earn the marks?

Question 51

5 marks
Did your answer earn the marks?

Question 52

3 marks
Did your answer earn the marks?

Question 53

5 marks
Did your answer earn the marks?

Question 54

3 marks
Did your answer earn the marks?

Question 55

5 marks
Did your answer earn the marks?

Question 56

3 marks
Did your answer earn the marks?

Question 57

3 marks
Did your answer earn the marks?

Question 58

2 marks
Did your answer earn the marks?

Question 59

3 marks
Did your answer earn the marks?

Question 60

3 marks
Did your answer earn the marks?
Mark my answers

Worksheets are written for Revision Library and come with a full mark scheme; a few English packs quote an out-of-copyright text, and name it on the page. Exam board names and specification codes say which qualification a pack suits: we are not affiliated with, endorsed by or approved by any exam board, and no past-paper question is reproduced here. Rights queries: hello@revisionlibrary.co.uk