Information and Communication Technology 0417/31 May/June 2023: question paper and mark scheme

Question

Open and examine the file j31weather.csv in your spreadsheet software. Save this as a spreadsheet with the file name Weather_ followed by your centre number_ candidate number, for example, Weather_ZZ999_9999 Place in the footer: • left aligned, the automated file name with no file path • right aligned, your name, centre number and candidate number. [2]

Question

Format rows 1 to 17 of the spreadsheet to look like this. A B C D E F G H I J 1 Average hours of sunshine per month 2 Amarta Bingchen Chelsmy 3 January 4 February 5 6 Number of days with rain in Amarta 7 Number of days with rain in Bingchen 8 Number of days with rain in Chelsmy 9 Number of days with heavy rain in Amarta 10 Average wind speed in Amarta in January Number of days with a wind speed of less than 5 11 knots in Amarta in January 12 13 Town Amarta Bingchen Chelsmy Sunshine Rainfall Wind speed Sunshine Rainfall Wind speed Sunshine Rainfall Wind speed Date 14 (hours) (mm) (knots) (hours) (mm) (knots) (hours) (mm) (knots) 15 1st January 2023 4.224 6.86 13 3.246 5.36 19 2.109 0.5 10 16 2nd January 2023 0.479 0 20 3.212 0 23 4.456 0 10 17 3rd January 2023 4.748 0 20 3.964 9.88 18 4.019 2.97 0 18 4th January 2023 3.013 0 4 3.256 1.6 16 1.827 3.52 18 Apply the formatting in rows 15 to 17 down to row 73. Cells in rows 1 and 13 must be merged and centre aligned with a 16-point font as shown. Cells in the range A6 to C11 must be merged as shown. [10]

Question

Print your spreadsheet showing the values. Make sure: • the page orientation is landscape • the contents of all cells are fully visible and can be easily read • the row and column headings are displayed. [1]

Question

Place in cells B3 to D3 formulae to calculate for each town the average hours of sunshine for January, rounded to one decimal place. Place in cells B4 to D4 formulae to calculate for each town the average hours of sunshine for February, rounded to one decimal place. [8]

Question

Place in cells D6 to D8 formulae to calculate the total number of days that it rained in each of the three towns. [3]

Question

Place in cell D9 a formula to calculate the number of days with more than 7.5 mm of rain in Amarta. [2]

Question

Place in cell D10 a formula to calculate the average wind speed in Amarta in January rounded up to the nearest knot. [2]

Question

Place in cell D11 a formula to calculate the number of days with a wind speed of less than 5 knots in Amarta in January. [1]

Question

Save your spreadsheet. Print your spreadsheet showing the formulae. Make sure: • the row and column headings are displayed • the page orientation is landscape • the contents of all cells are fully visible and can be easily read. [1]

Question

Print your spreadsheet showing the values. Make sure: • only cells A1 to D11 are displayed • the row and column headings are not displayed • the page orientation is portrait • it fits on a single page • the contents of all cells are fully visible and can be easily read. [3]

Question

In cell E3 calculate the total rainfall for Chelsmy for January. In cell E4 calculate the total rainfall for Chelsmy for February. Create an appropriate chart to compare the average hours of sunshine with the total amount of rainfall for the months of January and February in Chelsmy. Set the month as the category axis. Set the average hours of sunshine as the primary value axis and the rainfall as the secondary value axis. Set the rainfall axis to have a maximum value of 100 mm. Fully label the chart. Save your chart. Place a copy of your chart in your Evidence Document. [9] [Total: 42] Task 3 – Web Page You are working as part of a team of web developers at TawaraWeb and have been asked to create parts of a web page for a client.

Question

Create CSS to be added to an external stylesheet to meet these specifications: Set the font for h1, h2, h3 and the paragraph styles so that the browser selects and displays the font Calibri. If this font is not available, the browser selects and displays the font Helvetica Neue. If neither of these fonts is available, the browser should display its default sans-serif font. Place a copy of your CSS in your Evidence Document. [6]

Question

Create all the head section of a web page to meet these specifications: Title for the web page is Weather Data Metatags: • set the character encoding to ISO-8859-1 • set the name of the author to TawaraWeb followed by a space then your name • set the web page description to Weather data for the Tawara region • set the viewport to the width of the device being used with an initial scaling of 2 • set two appropriate metatag keywords for use by search engines for this page. Set the default target window to _blank if a new web page is opened from within this web page. Place a copy of your HTML source in your Evidence Document. [22] [Total: 28] Task 4 – Printing the Evidence Document Make sure your name, centre number and candidate number appear on every page of your Evidence Document. Save and print your Evidence Document.