Tawara school has a shop that sells items needed by pupils in school. Part of a spreadsheet with details of the items is shown. 5 Write down the number of rows that are shown in the spreadsheet that contain text. (a) … [1] Write down the number of columns that are shown in the spreadsheet that contain text. (b) … [1] 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. (c) … … … … … … … … … … [5] Explain the steps that need to be taken to display cell H4 as US dollars. (d) … … … … [2]

5(a) 6 rows 1 5(b) 8 columns 1 5(c) Five from: If Tax is payable then//If F4 is equal to "Y" then If true the tax is paid Multiply the rate of tax/I1 … … by the selling price/D4 … by the amount sold/G4 If Tax is not payable//If F4 <>"Y"//Else//Otherwise … … then display a blank … the tax is not paid 5 5(d) Two from: Highlight/select cell H4 Select format cells Select currency/accounting Select dollar/USD icon 2

A computer system is being created to calculate the scores in a diving competition. Every dive by an athlete is awarded a score out of 10 by each judge. 8 The system is being tested using live data. An athlete completes his dive with a difficulty of 3.4 and achieves the following scores, which are displayed in a spreadsheet. Before the overall score is calculated the judges’ scores need to be sorted into ascending order. Describe the steps that would be carried out to sort the data into ascending order. (a) … … … … … … [3] Cell B12 contains the formula, ROUND((SUM(B5:B9)*D1),1). Explain what the formula in cell B12 does. (b) … … … … … … [3] The judges’ score column will be tested using normal, abnormal and extreme data. Explain, giving examples of test data which would be used, what is meant by: (c) Abnormal test data… … … … Extreme test data… … … … [4]

8(a) One from: Highlight cells A4 to B10 Click anywhere in the range Highlight A4 to A10 and click expand the selection Highlight B4 to B10 and click expand the selection Two from: Click on the down arrow//Click on Custom Sort Select column B Select smallest to largest/A  Z 3 8(b) Three from: Totals cells B5 to B9 Multiplies by cell D1/difficulty/3.4 Rounds the value to 1 decimal place 3 8(c) Matched pairs Abnormal – data that is outside the range//Unacceptable data//Data of the wrong type Example: greater than 10, negative numbers, letters, symbols Extreme – data that is on the boundary of acceptability Example: 10 4

Many teachers feel that monitoring student progress is an important part of the teaching and learning process. 8 Describe how the features of a spreadsheet can be used by a teacher to help monitor student progress. … … … … … … … … … … … … [6]

8 Six from: Student progress can be shown over time using graphs Comparisons between students can be shown using graphs Comparisons with archive data can be shown using graphs Conditional formatting can be used to show how a student is performing compare to other students or past performance Data can be sorted to show the best performers in the class Calculate average marks to enable comparisons Automatic reporting results to parents Filtering students to show those above/below a threshold 6

8 You are creating a presentation on the World Games. You have created a spreadsheet showing the number of gold, silver and bronze medals awarded to different countries. You are planning to create a graph in the presentation showing the country, the number of gold medals and the total number of medals. A 12 3 4 5 6 7 8 9 10 11 12 13 B C D E F World Games Medals Country Gold 1 2 3 4 5 6 7 8 9 10 Italy United States Germany Russia France China Great Britain Japan Ukraine Spain 153 142 137 137 101 68 61 55 47 42 Silver 145 131 111 110 102 55 89 38 36 42 Bronze 142 109 138 72 105 27 210 53 125 42 Total 440 382 386 319 308 150 360 146 208 126 Original data (a) Explain how you would create a vertical bar chart, showing the name of the country, the number of gold medals and the total number of medals awarded to each country, on a new slide in the presentation. … … … … … … … … … … … … [6] (b) The total number of medals has been calculated manually and entered as a value. This could lead to errors. Explain how you could create a formula to calculate the total number of medals awarded for Italy, using only the mouse. … … … … … … … … [4] (c) Explain how the data could be sorted by the total number of medals won by each country. The country with the largest number of medals should be at the top of the list. … … … … … … … … [4]

8(a) Max four from: Open spreadsheet Load the file Select B3 to C13 Then hold CTRL and select F3 to F13 Click insert then click bar chart/graph Select the format/type of chart/graph Highlight the chart/graph Copy the bar chart/graph Max four from: Open the presentation software Add a new slide Click on new slide Paste the bar chart/graph Add titles/legend/axes labels Save the file 6 8(b) Four from: Click on the cell F4 Select formulas Click Autosum/Σ Check the correct range has been highlighted/highlight/drag C4 to E4 Click  4 8(c) Four from: Highlight B3 to F13 Click Data then Sort Select data has headers Sort by Total/Column F Select Largest to smallest/Descending/Z to A Click OK/enter 4

10 (a) Explain the differences between a VLOOKUP function and a LOOKUP function. … … … … … … [3] (b) A spreadsheet contains a list of staff and the rooms they work in at a school. A 1 2 3 4 5 B C D E Room Department Name Julieta Diaz 30 History 28 29 26 31 49 Chemistry Ernesto Fernandez Aaron Garcia David James Adriene Martinez Salvador Calbo Languages ICT Mathematics Biology 31 =VLOOKUP(D2,A2:C7,3) 6 7 (i) Explain, in detail, what the formula in cell E2 does. … … … … … … [3] (ii) When certain room numbers are typed into cell D2 unexpected results appear in cell E2. Suggest improvements that could be made to ensure the correct result is displayed. … … … … [2] A teacher in the school has created a spreadsheet to display whether a student has good timekeeping when arriving at lessons. He has produced a formula but thinks it could be improved. The formula is: =IF(B4>=A$18,B$18,IF(B4>=A$17,B$17,IF(B4>=A$16,B$16,IF(B4>=A$15,B$15,IF(B4>=A$14,B$14,""))))) A 1 2 3 4 5 B C =IF(B4>=A$18,B$18,IF(B4>=A$17,B$17,IF(B4>=A$16,B$16,IF(B4>=A$15,B$15,IF(B4>=A$14,B$14,""))))) Half term Lates 1 Name of Student 2 3 4 17 1 12 20 18 0 6 7 8 9 10 11 12 13 14 15 16 17 18 19 5 6 0 1 5 15 25 Excellent Very Good Good Poor Very Poor (c) Write down the value that should appear in cell C4. … [1] (d) The teacher has improved the formula and has typed in =VLOOKUP(B4,A$14:B$18,2) Explain the advantages of using this formula compared to the original one. … … … … … … … … … … [4]

10(a) Three from: LOOKUP allows for horizontal and vertical searching whereas VLOOKUP allows for vertical searching LOOKUP does not require an index value/only works on the second row/column whereas VLOOKUP requires an index value LOOKUP usually only works when the data is sorted VLOOKUP only returns data to the right of the searched column VLOOKUP user can select either an approximate or exact match to the lookup value 3 10(b)(i) Three from: The formula looks up the value in D2… …In the (range) A2 to A7 And returns the corresponding value In the 3rd column/Column C 3 10(b)(ii) Add FALSE/0 to the end of the formula – 1 mark Sort the range into ascending order of column A – 1 mark 2 10(c) Poor 1 10(d) Four from: Quicker to type in the formula Fewer mistakes when typing in the formula Easier to spot mistakes Easier to expand the range Takes up less storage space Easier to remember when retyping 4

Cambridge IGCSE ICT · 0417

IGCSE ICT (0417) Spreadsheets past paper questions

Revise IGCSE ICT (0417) — Spreadsheets with real Cambridge past-paper questions grouped by topic, each shown with its marking scheme. Working one topic at a time targets exactly the skill each question tests.

37 free questions from 2016–2021, each with its marking scheme. Open the full topical bank to filter by subtopic, paper, and year.