NASS S4_2022_Prelim P2

Name: ................................ ................................ ........... Register no: ............. Class: ............... NGEE ANN SECONDARY SCHOOL O PRELIMINARY EXAMINATION COMPUTING 7155/02 Paper 2 (Lab-based) 31 August 2022 2 hr 30 min Additional materials: Electronic version of YUMCOM.xlsx file Electronic version of PASSWORD.py file Electronic version of NUMSYS.py file Insert Quick Reference Glossary Instructions to Candidates Write your name, register number and class at the top of this page. Answer all questions. All tasks must be done in the computer laboratory. You are not allowed to bring in or take out any pieces of work or materials on paper or electronic media or in any other form. Programs are to be written in Python. Save your work using the file name given in the question as and when necessary. The number of marks is given in brackets [ ] at the end of each question or part question. The total number of marks for this paper is 50. Checked by student: _____________________ Date: _________ This document consists of 8 printed pages and 2 blank pages. For Examiner’s Use Marks /50

2 NAS/2022/PRELIM/4E/COMPUTING(7155/02) Task 1 The following spreadsheet is used to store the particulars of participants who had taken part in a competitive eating competition. You are required to finish setting up the spreadsheet to complete the details of the participants. You may make use of the following EXCEL function to join text s together: CONCATENATE(<text1>,<text2>) Open the file YUMCOM.xlsx. You will see the following data. Save the file as MYYUMCOM_2022_<your name>_<centre number>_<index number>.xlsx

3 NAS/2022/PRELIM/4E/COMPUTING(7155/02) 1 The Tag ID of the participants consists of the last 3 letters of their name in column B, followed by the first 2 letters of the area they represent in column F. In cells D6 to D13, enter a formula that uses functions to create the Tag ID of all the participants. [2] 2 The age of the participants may be approximated to be the difference between the current year and their birth year. In cells E6 to E13, enter a formula that calculates the age (in years) of all the participants. [1] 3 The table in cell range B16:E17 shows the categories in the competition based on the age of the participants. In cells I6 to I13, enter a formula that displays the category of each participant based on their calculated ages. [2] 4 In cells F21 and F22, enter the most appropriate formula for each cell to show the fastest 1st and 2nd timings among all the participants. [2] 5 In cell I24, enter a formula that uses functions to calculate the average age (years) of all the participants, rounded off to the nearest whole number. [2] 6 In cells A6 to I13, use a formatting tool to change the colour o