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

12 A doctor measures the blood pressure of some of his patients regularly. The patients take home a blood pressure machine and record their results on a spreadsheet which the doctor has created. Part of the spreadsheet is shown. A 1 2 3 4 5 B C D F 10-Feb 11-Feb Date 12-Feb 13-Feb 14-Feb 15-Feb 16-Feb 134 137 145 150 180 134 130 74 67 81 86 74 60 120 140 160 180 40 80 90 100 110 75 57 Normal Normal Mild Mild Severe Low Normal Mild Moderate Severe Normal Normal Sys Dia Sys Dia Warning Warning Blood pressure readings Blood pressure warnings 6 7 8 9 E G H I (a) When a reading produces a Severe or Low warning then the text automatically changes to a red font on a light red background. Explain how the doctor could set up automatic colour change for these warnings on the spreadsheet. … … … … … … … … … … [5] The doctor has set up a named range called Bloodpressure for the cells F3 to H7. (b) Explain why the doctor has used a named range. … … … … … … [3] The formula =VLOOKUP(B3,Bloodpressure,3) has been entered into cell D3. (c) Explain what the formula does. … … … … … … … … [4] The doctor has created a graph of the readings for one patient and has displayed it on the spreadsheet. However, there are elements missing. A 1 2 3 4 5 B C D F 10-Feb 11-Feb Date 12-Feb 13-Feb 14-Feb 15-Feb 16-Feb 134 137 145 150 180 134 130 74 67 81 86 74 75 67 Normal Normal Mild Mild Severe Normal Normal Sys Dia Warning Blood pressure readings 6 7 8 9 10 11 12 13 14 15 16 E G H I J K L M Blood pressure warnings 200 180 160 140 120 100 80 60 40 20 0 09-Feb 10-Feb 11-Feb 12-Feb Sys Dia 13-Feb Chart Title 14-Feb 15-Feb 16-Feb 17-Feb (d) Explain how the doctor created the graph. Write in your answer one improvement that could be made to the graph. … … … … … … [3] (e) At the end of the month the doctor needs to produce a hard copy document showing information about a patient: their name, date of birth and patient number, as well as data and the graph from the spreadsheet. Design a suitable document layout. The document must have appropriate spacing. [5]

12(a) Five from: Highlight Column D Select Conditional Formatting Select Highlight Cell Rules Select Equal to Type in the Equal to box…Severe/Low Click on Format Select/Set the red font on light red background Repeat the process for Low/Severe 5 12(b) Three from: Removes the confusion over absolute and relative referencing Reduces the issues involved in changing the range Easier to understand the formula Easier to check for errors in the formula Easier to enter the formula//Less errors in entering the formula Faster to write the name than to select the range 3 12(c) Looks up the value of B3 Looks in the named range Bloodpressure Returns the corresponding value from the 3rd column 4 12(d) Two from: Highlight A2 to C9 Click Insert Select table…Click Chart Select 2D line chart/select type of graph One from: Add a chart title Add axis title Remove 09-Feb/17-Feb as not needed 3 12(e) Five from: Include Name, Date of birth Include Patient Number Graph is displayed Must look like a hard copy report Fills the report Suitable title at the top of the page title must relate to blood pressure readings 5

A teacher is creating a spreadsheet that will record the grades students achieved in recent tests and compare the average grades of these tests with the students’ forecast grades. The grading 4 the teacher is using awards 1 for the highest grade down to 10 for the lowest. The teacher has created a validation rule on the grade entered. Give examples of data used to test the validation rule. Your answers for abnormal test data must be for a different reason in each case. (a) Example of test data Type of test data Normal Abnormal Abnormal Extreme [4] Part of the spreadsheet is shown, column G subtracts the Forecast grade from the Average grade. (b) Describe the steps the teacher can take to edit the spreadsheet to prevent new data being typed into the Forecast grade column. (i) … … … … … … … … … … [5] The teacher has typed a formula in cell E3. The formula is ROUNDUP(AVERAGE(B3:D3),0) Explain, in detail, what the formula does. (ii) … … … … … … … [4] The teacher has produced a graph showing the differences between the forecast grade and the average grade for each student. The graph looks like this. (c) Describe the steps taken to create the graph. Include in your answer three improvements that could be made to the graph. Method… … … … … … Improvement 1… … Improvement 2… … Improvement 3… … [6]

4(a) One from: Normal data: Any integer between 1 and 10 Two from: (must be a different type) Abnormal data: Any number outside the range/alphabetic/symbol/negative number/decimal number One from: Extreme: 1 or 10 4 4(b)(i) Five from: Select the whole sheet Right click then Format cells Click Protection tab ... ... unlock cells Highlight F3:F6/Forecast grades column/column F Lock cells Click on Review then protect sheet 5 4(b)(ii) Calculates the Average of the range B3 to D3 Rounds up the answer to 0 decimal places/integer 4 4(c) Three from: Highlight A3:A6 Hold CTRL Select G3:G6 Click insert Column/bar graph Three from: Add legend Add chart title Add axes labels Remove gridlines Change colour/thickness of the bars 6

6 Khalid keeps a spreadsheet to record his expenses and to plan for future spending. This spreadsheet is split into two separate sheets, which he has named Plan and Model. Plan contains details of his future spending. Model contains a model of his income and expenses. Plan A Income Statement Income Expenditure items Taxes Housing Food Transportation Entertainment / Other Healthcare Investment return Income Growth Expense Ratio % % of spending 63,784 12,757 19,135 6,378 9,568 6,378 3,189 4% 2.5% 90% 22.22% 33.33% 11.11% 16.67% 11.11% 5.56% 20% 30% 10% 15% 10% 5% % of income B C D 15 14 13 12 11 10 9 8 7 6 5 4 3 2 1 Housing Food Transportation Model A Model Income Investment Income Total Income Taxes Entertainment / Other Can I afford a car? Savings Balance Sheet Net Income (Savings) Total Expenses Healthcare 2025 2024 2023 2022 2021 2020 72,166 70,406 68,688 67,013 1,450 1,123 816 527 255 65,379 73,616 71,529 69,504 67,540 65,634 64,950 63,366 61,820 60,312 58,842 57,405 63,784 63,784 14,433 14,081 13,738 13,403 13,076 12,757 21,650 21,122 20,607 20,104 19,614 19,135 10,825 10,561 10,303 10,052 9,807 9,568 7,217 7,041 6,869 6,701 6,538 3,608 3,520 3,434 3,351 3,269 3,189 7,217 7,041 6,869 6,701 6,538 44,912 36,246 28,083 20,399 13,171 8,666 8,163 7,684 7,228 6,792 Y Y N N N N 6,379 6,379 6,378 6,378 B C G F E D 22 21 20 19 18 17 16 15 14 13 12 11 10 9 8 7 6 5 4 3 2 1 (a) Khalid has entered a formula in cell C4 of the Model sheet. The formula is B4+(B4*Plan!$B13) Explain, in detail, what the formula does. Include in your answer an explanation of why the $ and the ! are used in the formula. … … … … … … … … … … [5] (b) He is saving up to buy a new car; this will cost at least $35,000. Khalid has entered a formula in cell B21 of the Model sheet. The formula is IF(B20>35000,"Y","N") Explain, in detail, what the formula does. … … … … … … [3] Plan A Income Statement Income Expenditure items Taxes Housing Food Transportation Entertainment / Other Healthcare Investment return Income Growth Expense Ratio % % of spending 63,784 12,757 19,135 6,378 9,568 6,378 3,189 4% 2.5% 90% 22.22% 33.33% 11.11% 16.67% 11.11% 5.56% 20% 30% 10% 15% 10% 5% % of income B C D 15 14 13 12 11 10 9 8 7 6 5 4 3 2 1 (c) Khalid is planning to create an appropriate graph/chart to be placed in a new sheet. The graph/chart will display the % of income and the names of the expenditure items from the Plan sheet. Identify the most appropriate graph/chart he could use and describe the steps he needs to take to produce this graph/chart in a new sheet. … … … … … … … … … … … … … … [7]

6(a) B4+(B4*Plan!$B13) Three from: Calculates the 2021 income in the Model sheet By increasing the 2020 income by 2.5% The 2.5% is taken from the Income Growth cell in the Plan sheet The $ is needed as the column B needs to be retained when the formula is replicated – 1 mark The ! is needed to show that the data is being taken from a different worksheet – 1 mark 5 6(b) IF(B20>35000,“Y”,“N”) The formula automatically displays a Y/can buy the car if the Savings cell/B20 is greater than $35 000 otherwise it displays an N/cannot buy the car 3 6(c) Pie chart – 1 mark Five from: Select Plan sheet Highlight A5:A10 Press CTRL and highlight D5:D10 Click on insert chart Select pie chart Choose style of chart Add a title Add data/axes labels Add legend Add a name for the new sheet Right click on the chart and move to a new sheet//copy and paste in the new sheet – 1 mark 7

Cambridge IGCSE ICT · 0417

IGCSE ICT (0417) Graphs and charts past paper questions

Revise IGCSE ICT (0417) — Graphs and charts 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.

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