20.1. Create a data model
A subsection of Information and Communication Technology, 0417, through 20. Spreadsheets
Listing 10 of 47 questions
The table shows part of a spreadsheet file. A B C D E Sweet shop Item Cost price Selling price Profit Amount sold Nutty bar $1.10 $1.30 $0.20 Choco bar $0.95 $1.10 $0.15 Cocoa bar $1.05 $1.30 $0.25 Chocolate digits $0.80 $0.90 $0.10 Give the cell reference of one cell containing a label. Give the cell reference that contains $0.15. Write down how many columns are shown in the spreadsheet. Give the cell reference for a cell that should be formatted as currency. State the formula that should be in cell D5. =
0417_s14_qp_12
THEORY
2014
Paper 1, Variant 2
View
Explain how a VLOOKUP function works, using the example of VLOOKUP(38,A2:C10,3,FALSE). A spreadsheet contains the following data and formula. Months in employment :ŽďĚĞƐĐƌŝƉƟŽŶ Name D E F G H I Teacher Chemist Engineer Lecturer Bricklayer Manager Shopkeeper Zoo owner Explorer John Avtar Ahmed Ho David Jose Simon James /ŶƟŬŚĂď =VLOOKUP(33,D2:F10,3) Explain why the cell H2 might not contain James. Suggest the value that would be contained in that cell, giving reasons why.
0417_s15_qp_12
THEORY
2015
Paper 1, Variant 2
View
Below is a spreadsheet showing contributions to a charity from different regions. Regional analysis Region Africa Asia Australasia Europe North America South America RCode AF AS AU EU NA SA Currency Rand Yuan Dollar Euro Dollar Real ZAR 65.00 ¥3,752.50 $142.50 €1,076.00 $2,240.50 R$75.75 20/01/2016 20/01/2016 20/01/2016 20/01/2016 21/01/2016 21/01/2016 21/01/2016 21/01/2016 21/01/2016 22/01/2016 22/01/2016 22/01/2016 23/01/2016 23/01/2016 23/01/2016 23/01/2016 24/01/2016 25/01/2016 25/01/2016 26/01/2016 26/01/2016 AF AS NA EU NA SA AF AS EU AU EU NA SA EU NA AS AS AS AU EU AS 15.00 150.00 2000.00 25.00 120.50 50.00 50.00 25.00 15.00 140.00 1000.00 20.00 25.75 16.00 100.00 55.00 12.50 10.00 2.50 20.00 3500.00 Regional income Date Rcode Amount The Manta Conservaon Project A B C D E F G H I Number of donaons Cell E4 contains the formula =SUMIF($H$4:$H$24,B4,$I$4:$I$24) Using cell references explain what this formula does. What formula would you expect to see in cell E8? The formula in I26 only refers to the values in column I. Write down the formula you would expect to see in cell I26 1 19
0417_s16_qp_12
THEORY
2016
Paper 1, Variant 2
View
Here is a spreadsheet showing the countries which have been selected to host the Commonwealth Games. Canada England Australia New Zealand Canada Wales Australia Jamaica Scotland New Zealand Canada Australia Scotland New Zealand Canada Malaysia England Australia India Scotland Australia Canada England Australia New Zealand Wales Jamaica Scotland Malaysia India A B C D E South Africa South Africa Cells E5 to E14 contain the number of times each country has been selected to host the Commonwealth Games. Write down the formula which should go in cell E5 so that it can be replicated down to cell E14. Papua New Guinea are interested in bidding to host the 2026 Commonwealth Games. If they are successful the spreadsheet will need to be updated. Apart from those in column E, write down the cell references of those cells which will need to change. 1 20
0417_s16_qp_13
THEORY
2016
Paper 1, Variant 3
View
Below is part of a spreadsheet of candidates’ examination grades. A teacher needs to create formulae to analyse these grades. There are 64 grades in the full list, in cells B6 to B69. A B C F D E G Results for Centre XX123 Qualification: IGCSE Cand. No ICT E C C D C C A E C E C D Totals A* A % A* - C % A* - G B C D E F G U X Write the most efficient formula that could be entered in cell E6 to display the number of A* grades for ICT in the full list. The formula must be easy to replicate for the other grades. 1 3 The teacher wants to copy the formula down to E15 without having to re-type any of the cells. Describe how he could achieve this. The teacher needs to calculate the percentage of students gaining A* to C grades. Write the most efficient formula to achieve this. [ 1 3 The teacher wishes to create an appropriate chart or graph to be placed on a separate sheet. It will show the grades and the corresponding total number of students gaining those grades. Describe how the teacher could achieve this. 1 3
0417_s17_qp_11
THEORY
2017
Paper 1, Variant 1
View
The Xtreme Adventure Holiday Company are setting up a spreadsheet for their payroll. They have job codes for each of their employees. Below is part of the payroll spreadsheet. A B C F D E G The Xtreme Adventure Holiday Company Job codes A B H S SA TA TB TM TS Employees Name Luca Gisi Livio Stager Nico Gantert Fabian Ging Mattia Polt Sofia Walp Laura Haener Alessandro Messerli Lara Forrer Arda Hegler Simone Aebi Administration/Office Snowboard Instructor Host/Hostess Ski Instructor Senior Administrator Transport Assistant Trainee Snowboard Instructor Transport Manager Trainee Ski Instructor Job code B S B S S H S S B S B Job description Describe, giving a suitable name for the range (A6:B14), how the payroll manager could name this range. 1 2 Write a formula for cell C18 to display the job description, using the named range from part .
0417_s17_qp_12
THEORY
2017
Paper 1, Variant 2
View
Tawara school has a shop that sells items needed by pupils in school. Part of a spreadsheet with details of the items is shown. Write down the number of rows that are shown in the spreadsheet that contain text. Write down the number of columns that are shown in the spreadsheet that contain text. 1 8 Tax is paid on certain items sold in the shop. The tax rate that has to be paid is 20% of the selling price. If tax is to be paid on an item, then ‘Y’ is placed underneath the Tax heading. The formula in I4 is: IF(F4=''Y'',($I$1*D4*G4),'''') Explain, in detail, what the formula does. Explain the steps that need to be taken to display cell H4 as US dollars. [ 1 8
0417_s21_qp_11
THEORY
2021
Paper 1, Variant 1
View
For Examiner's Use A sports shop owner uses a spreadsheet to calculate his profits. This is part of the spreadsheet. A B C D E Item Selling Price Cost price Profit per item Number in stock Kino Trainers $45.00 $40.00 $5.00 Goalagame Boots $44.00 $38.00 $6.00 Melchester Rovers shirt $78.00 $65.00 $13.00 IOM football $51.00 $42.00 $9.00 Beckett shinpads $40.00 $32.00 $8.00 Give the cell reference of the cell that contains $42.00 Give the cell reference of a cell that contains an integer. How many rows are shown in the spreadsheet? Write down the formula which should go in cell D2. When the spreadsheet was created a similar formula to D2 had to go in cells D3 to D6. Describe how to enter these formulae without typing them. For Examiner's Use This spreadsheet is an example of a financial model. Give two examples of computer models, other than financial models. Tick three reasons why computer models are used.  The real thing may need too large a timescale. Computer models waste a lot of raw materials. The real thing is quicker to build. If a computer model fails it does not need rebuilding. Once a computer model is built it does not cost a lot to run. A model does not cost anything to make.
0417_w12_qp_13
THEORY
2012
Paper 1, Variant 3
View
Questions Discovered
47