20. Spreadsheets
A section of Information and Communication Technology, 0417
Listing 10 of 51 questions
A student is creating a spreadsheet of airports for a project. The formula in G2 is: =VLOOKUP(F2,A2:B9,2,0) Explain, in detail, what the formula in G2 does. Before the spreadsheet can be used, data needs to be entered to test the spreadsheet. There are three types of test data: normal, abnormal and extreme. Explain what is meant by these three types of test data. Normal Abnormal Extreme 1 15 Explain, using examples, the differences between a function and a formula. The completed file could have been created using a database. Describe two reasons why databases are used in certain applications rather than spreadsheets. [ 1 15
0417_s22_qp_12
THEORY
2022
Paper 1, Variant 2
View
A student is creating a spreadsheet that includes a formula to check a number entered in a cell is between two values. • The number is entered in cell A10 as an integer. • If the number is between the two values then a message ‘OK’ is displayed, otherwise ‘error’ is displayed. The formula the student has created is: =IF(AND(A10>1,A10 Extreme test data 2 Reason The student has entered an item of abnormal test data. Explain why abnormal test data is used. [ 1 13
0417_s23_qp_12
THEORY
2023
Paper 1, Variant 2
View
The English teacher stores details of her students’ exam scores in a spreadsheet. The exam is marked out of 80 and the results are typed into the Exam result column. A B C D Name Form Exam result Pass/Fail The teacher wants to use a validation check which only allows numbers between 0 and 80 to be typed in. What is the name of this type of validation check? She types in the following exam results to test that the validation works: 0, 25, 80 and 87. Using the data above write down one example of: normal data extreme data abnormal data The teacher decides that the pass mark for the exam should be 45. Depending on the number of marks each pupil gets, the word ‘Pass’ or the word ‘Fail’ will appear in the Pass/Fail column. Write down the formula which should appear in cell D2. Explain how she can replicate the formula in the 30 cells below D2. Give two reasons, apart from safety, why computer models are often used rather than the real thing.
0417_w14_qp_13
THEORY
2014
Paper 1, Variant 3
View
A manager of a computer store has created a relational database to keep track of its orders. She has created two tables that are linked together. Table A stores the products. Table B stores the order details. The following data shows some records from the database. Table A: products Product_ID Product_name Unit_price PC Base unit $300 Monitor TFT $250 Mouse $9 Keyboard $9 Laser printer $250 Table B: order details Order_ID Product_ordered Quantity Name the most suitable primary key in the products table. Name a suitable foreign key in the order details table. On the data shown in the tables above, the following search is to be carried out: (Unit_price>$250) Write down the Order_ID resulting from this search. On the data shown in the tables at the start of question 11, the following search is to be carried out: (Product_ID>2000) AND (Quantity The store has created a report with a new field called total_price to be calculated at run time. This field will contain the number of units ordered multiplied by the price of each unit. Write an expression to produce this result.
0417_w16_qp_12
THEORY
2016
Paper 1, Variant 2
View
A teacher is creating a spreadsheet as an electronic mark book. Part of the electronic mark book displays how well a student did on a piece of work. When a student completes a piece of work the teacher will enter the mark into the electronic mark book and a message will be displayed. • If the student gains 60 or more marks then ‘Merit’ is displayed • If the student gains 50 to 59 marks then ‘Pass’ is displayed • If the student gains a mark below 50 marks then ‘Not Pass’ is displayed. The formula the teacher has used is: =IF(A1>=60,"Merit",IF(A1>50,"Pass","Not Pass")) The formula will need to be tested. The teacher will do this by entering test data into cell A1. Explain the steps that need to be taken to test the formula. Include in your answer any test data used and the reasons why these were chosen. When the teacher enters a mark of 50 he expects ‘Pass’ to be displayed. State the changes he needs to make to the formula to ensure ‘Pass’ is displayed. [ 1 12 When the electronic mark book is complete it needs to be tested with live data. Explain what is meant by live data.
0417_s23_qp_13
THEORY
2023
Paper 1, Variant 3
View
For Examiner's Use 11 The manager of a bookshop uses a database to store data about the books she sells. This is part of the database. Title of book Fiction (Y/N) Number in stock Next order due Full Circle N 20/06/11 Sanditon Y 08/07/11 Vanity Fair Y 16/10/11 The Old Curiosity Shop Y 23/06/11 Pride and Prejudice Y 23/06/11 Sense and Sensibility Y 02/10/11 The Moon’s a Balloon N 21/08/11 How many fields are there in this part of the database? How many records are there in this part of the database? The records shown are to be sorted in ascending order of Number in stock. What will be the title of the book which will be the first record in the database after it has been sorted? What is the title of the book which has no more than 12 copies in stock and is not Fiction? Describe a validation check which could be carried out on the Next order due field.
0417_s11_qp_13
THEORY
2011
Paper 1, Variant 3
View
Justdiscs Limited stocks many music CDs. Below is a small selection of CDs stored on their database. The data has been sorted on two fields. Number_of_tracks Year_released Price CD_title Artist_name $10.99 Let Em Ave It Giggs $9.99 Heroes Sabiton $8.99 Alright on Top Luke Slater $8.99 Humbug Arctic Monkeys $8.99 Definitely Maybe Oasis $7.99 Erasure Erasure $4.99 Cool Blues Jimmy Smith Write down the field which was used as the primary sort in the database and the order in which it was sorted. Write down the field which was used as the secondary sort in the database and the order in which it was sorted. Peter is the owner of the shop. He receives requests from customers about the CDs he has in stock. He converts these requests into search criteria using comparison and Boolean operators. For example, one customer might ask for CDs which were released after 2002 and also those that have at least 11 tracks. Peter would write this down as: Year_released > 2002 OR Number_of_tracks >= 11 Write down the search criteria for a customer who wants a list of all the CDs which were released before 2010 but only if they cost less than $8.99 Write down the names of the artists whose records match the requirements of part . [ 1 17
0417_s16_qp_11
THEORY
2016
Paper 1, Variant 1
View
A student is setting up a database of information about volcanoes for a Geography project. The following is part of the database. Name_of_volcano Country Height State Last_eruption Volcano_type Usu Japan Active Stratovolcano Tor Zawar Pakistan Dormant Fissure Datong China Extinct Cinder Cone Changbaishan China Active Stratovolcano Stromboli Italy Active Stratovolcano Tengchong China Dormant Pyroclastic cone Wudalianchi China Dormant Multi-coned Operators such as AND, OR, NOT, LIKE, >, >=, can be used to search the volcano database. The search criteria for all the dormant volcanoes with a height of less than 1000 metres would look like this: State = “Dormant” AND Height write down the names of the volcanoes that match the requirements of part . The data is sorted into ascending order of height. Write down the name of the volcano which would now be in the first record. Drones are remote-controlled flying devices. Data can be collected by drones that fly through the gas cloud of the volcano. The drones have sensors attached to them which send the data to a computer. The computer analyses the data. Discuss the benefits and drawbacks of using this method of measurement. Explain why the data collected by the sensors cannot be read directly by the computer.
0417_w18_qp_12
THEORY
2018
Paper 1, Variant 2
View
A spreadsheet has been produced showing the final places in one of the events in the 2018 Winter Olympic Games. A B C D E F G H I J Country Name Heat1 KOR LAT GBR OAR LAT KOR GER Yun Sungbin Martins Dukurs Dom Parsons Nikita Tregubov Tomass Dukurs Kim Jisoo Axel Jungk 50.28 50.85 50.85 50.59 50.88 50.80 50.77 Heat2 50.07 50.38 51.41 50.50 50.58 50.86 51.01 Heat3 50.18 50.32 49.33 50.50 50.65 50.51 50.83 Heat4 Min Total Time 50.02 50.76 50.62 50.59 50.63 50.81 50.99 200.6 202.3 202.2 202.2 202.7 203.0 203.6 Seconds 20.6 22.3 22.2 22.2 22.7 23.0 23.6 20.55 22.31 22.21 22.18 22.74 22.98 23.60 The event consists of four heats. The times taken to complete each heat are added together to give the Total Time, in seconds. This is then displayed as minutes and seconds. Two formulae form this calculation; they are stored in column H and column J. The formula in cell H3 is: =INT(G3/60) Explain what the formula in cell H3 does. The formula in cell J3 is: =ROUND(I3,1) Explain what the formula in cell J3 does. At the end of the competition, the table must be sorted into ascending order of Total Time. The top athlete would be in the gold medal position, the second in silver medal position and the third in bronze medal position. The Medal and Rank columns must be added. This display should look like this: A B C D E F G H I Medal Rank Country KOR OAR GBR LAT LAT KOR GER Yun Sungbin Nikita Tregubov Dom Parsons Martins Dukurs Tomass Dukurs Kim Jisoo Axel Jungk Name Heat1 50.28 50.59 50.85 50.85 50.88 50.80 50.77 Heat2 Heat3 50.07 50.50 51.41 50.38 50.58 50.86 51.01 50.18 50.50 49.33 50.32 50.65 50.51 50.83 Heat4 50.02 50.59 50.62 50.76 50.63 50.81 50.99 Total Time 200.55 202.18 202.21 202.31 202.74 202.98 203.60 Gold Silver Bronze Explain the steps that must be taken to amend the spreadsheet and to display the data in this way. The heat times are measured by a computer system, but could be measured by officials with stopwatches. Discuss the advantages and disadvantages of using computers in measurement rather than humans.
0417_w20_qp_13
THEORY
2020
Paper 1, Variant 3
View
Questions Discovered
51