20. Spreadsheets
A section of Information and Communication Technology, 0417
Listing 10 of 51 questions
For Examiner's Use 16 A travel agent keeps a spreadsheet record of his customers’ holiday bookings to various destinations. This is split into two sheets. Sheet 1 contains the Code for each destination. Sheet 2 contains a list of his customer bookings so far. A B Code Destination BAH Bahamas COZ Cozumel EGY Egypt IND India MAL Maldives MEX Mexico Sheet 1 (Commas are used as delimiters in the functions shown below.) A B C Destination Code Trips =VLOOKUP(B2,Sheet1!$A$2:$B$7,2,0) BAH =COUNTIF($A$8:$A$18,B2) =VLOOKUP(B3,Sheet1!$A$2:$B$7,2,0) EGY =COUNTIF($A$8:$A$18,B3) =VLOOKUP(B4,Sheet1!$A$2:$B$7,2,0) MAL =COUNTIF($A$8:$A$18,B4) =VLOOKUP(B5,Sheet1!$A$2:$B$7,2,0) MEX =COUNTIF($A$8:$A$18,B5) Destination code of holidays booked so far this year EGY MAL BAH MEX MAL BAH EGY EGY MAL EGY BAH Sheet 2 For Examiner's Use Explain what the function in cell A2 in Sheet 2 does. What destination would you expect to see in cell A4 in Sheet 2? Explain what the function in cell C2 in Sheet 2 does. What value would you expect to see in cell C3 in Sheet 2? For Examiner's Use Spreadsheets are often used to produce computer models. Discuss the benefits and drawbacks of computer modelling.
0417_s12_qp_11
THEORY
2012
Paper 1, Variant 1
View
A school is creating a spreadsheet as a method of recording academic performance of students in IGCSE Geography. The spreadsheet lists each student’s name and their target level. The levels range from 1 to 9, where 9 is the best level. Teachers enter the current working level. If the working level is the same or better than target level then ‘‘well done’’ is displayed in the comment cell, otherwise ‘‘needs improvement’’ is displayed. A B C F D E G Name Frances Peter Target Level Working Level Comment Brown Smith Write a formula for cell E2 to automatically display ‘‘needs improvement’’ or ‘‘well done’’. 1 1 The school is going to use the data from the spreadsheet in its reports to parents. The school plans to mail merge the report with the data. Describe four advantages of using mail merge. The teacher needs to check the data source and template before the data is merged. Describe three checks that need to be done. [ 1 1
0417_s17_qp_13
THEORY
2017
Paper 1, Variant 3
View
For Examiner's Use 13 A shop owner uses a spreadsheet to calculate his profits. This is part of the spreadsheet. A B C D E F G Producer Food type Number in stock Cost Price Selling Price Profit Total profit Logekks Potato flakes $2.30 $2.90 $0.60 $73.80 Squarebranch Chocolate bar $0.75 $0.95 $0.20 $31.60 Roofs Beefburgers $1.25 $1.55 $0.30 $40.50 Kapats Gravy $3.20 $3.95 $0.75 $66.75 Startle Yoghurt cream $1.50 $1.85 $0.35 $41.65 Total in stock Overall profit $254.30 Give the cell reference of the cell that contains 158. Give the cell reference of a cell that contains a label. Write down the formula in cell F5. Write down the formula in cell G2. Formulae similar to that used in cell G2 have been used in cells G3 to G6. These were not typed. Describe how these were entered.
0417_w11_qp_11
THEORY
2011
Paper 1, Variant 1
View
Using the example of COUNTIF(A2:A10,” Rupinder has created a spreadsheet showing the following data and formula. 1DPH ' ( ) *          $YWDU.KROL 6XUMHHW6LQJK -DZDKDU1HKUX 6XQLO.DSRRU -DVYLU6KHUJLOO ,VKLWD'XWWD 1LNKLO%DQHUMHH $QLWD0HUFKDQW 6DQMLYH.XPDU 0RQWKVLQHPSOR\PHQW  &2817,) ( (µ µ       Write down the value that would be contained in cell G2. Rupinder subtracts the value in part from 9 (the number of workers). He thinks this will tell him the number of workers who have been in employment for more than 33 months. Explain why his reasoning does not work.
0417_m15_qp_12
THEORY
2015
Paper 1, Variant 2
View
The Tawara Tea Company supplies tea to its customers. The company has created a spreadsheet to help its sales team to keep track of its orders. The spreadsheet has two worksheets, named Products and Orders. Part of the spreadsheet is shown below. All prices and costs are displayed in Indian Rupee ₹. Worksheet: Products A B C Tea code Product name Price per unit 9 8 7 6 5 3 349 348 347 Chamong Organic Bannockburn Makaibari Organic Risheehat Goomtee Phuguri Pussimbing Supreme Tumsong Supreme Phuguri Supreme Singbulli Champagne Oolong 272 ₹ 4.00 ₹ 5.00 ₹ 4.90 ₹ 6.40 ₹ 8.00 ₹ 8.50 ₹ 16.00 ₹ 16.00 ₹ 16.00 ₹ 16.00 11 10 9 8 7 6 5 4 3 2 1 Worksheet: Orders A C Quantity ordered B D Order ID Tea code Cost 200 220 225 30 120 105 25 24 125 125 125 124 124 124 124 124 6 5 3 349 9 6 348 272 9 8 7 6 5 4 3 2 1 A salesman needs to create a formula to be placed in cell D2, in the Orders worksheet, that finds the Price per unit of the tea, multiplies it by the Quantity ordered and then displays the result. Write a formula that needs to be entered in cell D2. This formula will be replicated down to cell D9. = The salesman has created a new worksheet called Receipt which automatically produces a receipt for each order. Worksheet: Receipt Risheehat Pussimbing Supreme Chamong Organic Tumsong Supreme Singbulli Champagne Oolong A D C Total Quantity ordered 30 120 105 25 24 Product name B Order ID Tea code 124 349 9 6 348 272 7 6 5 4 3 2 1 ₹ 2416 ₹ 480 ₹ 480 ₹ 672 ₹ 400 ₹ 384 E Cost Explain how he could format the Cost column (column E) to display the values in Indian Rupees as shown.
0417_m19_qp_12
THEORY
2019
Paper 1, Variant 2
View
A school examinations officer has set up a spreadsheet of candidates for the IGCSE examinations. Part of the spreadsheet is shown: The formula in cell A3 is IF(A2<>'''',VLOOKUP(A2,E3:F6,2),'''') Explain what the formula in cell A3 does. 1 21 The examinations officer is planning to change the range E3 to F6 in the formula to a named range. Explain why he should do this rather than using the cell references. Give an example of an appropriate name he should give the range. A formula has been placed in cell F9. The formula is COUNTIF(A4:A15,''E'') Explain what the formula in cell F9 does. [ 1 21
0417_m20_qp_12
THEORY
2020
Paper 1, Variant 2
View
For Examiner's Use 11 A DVD shop owner wants to use a spreadsheet to calculate his profits. This is part of a spreadsheet he could use. Give the cell reference of the cell that contains $18. Give the cell reference of a cell that contains a label. Write down the formula that has been used to calculate the value in cell D4. The Total profit per title is calculated by multiplying the Profit per DVD by the Number in stock. Write down the formula that should go in cell F2. The owner wants to enter a similar formula to calculate the Total profit per title in cells F3 to F5. Write down the name of the technique he could use to do this. How many columns are shown in the spreadsheet? A B C D E F DVD title Cost price Selling Price Profit per DVD Number in stock Total profit per title Alice in Wonderland $21 $24 $3 Toy Story 2 $18 $24 $6 How to train your dragon $15 $20 $5 Alpha and Omega $22 $31 $9 Total profit of the four titles For Examiner's Use The owner wants to find out his total profit for these four titles using a formula. Write down the formula he should use. Give the reference of the most appropriate cell to insert this formula. If the owner changes the Selling price of Alice in Wonderland to $28 which other three cells would now change as a result?
0417_s10_qp_13
THEORY
2010
Paper 1, Variant 3
View
For Examiner's Use 15 Paul, a manufacturer of goods, offers discounts. The discounts are coded D, N or P. Paul keeps the details on this spreadsheet. (Commas are used as delimiters in the formulae shown below.) A B C D Code Rate D 10% N 15% P 20% Code Price Discount Discounted Price D $4.00 =IF(A7="D",B7*$B$2,IF(A7="N",B7*$B$3,IF(A7="P",B7*$B$4))) P $2.85 =IF(A8="D",B8*$B$2,IF(A8="N",B8*$B$3,IF(A8="P",B8*$B$4))) D $1.55 =IF(A9="D",B9*$B$2,IF(A9="N",B9*$B$3,IF(A9="P",B9*$B$4))) N $0.98 =IF(A10="D",B10*$B$2,IF(A10="N",B10*$B$3,IF(A10="P",B10*$B$4))) D $4.05 =IF(A11="D",B11*$B$2,IF(A11="N",B11*$B$3,IF(A11="P",B11*$B$4))) D $10.00 =IF(A12="D",B12*$B$2,IF(A12="N",B12*$B$3,IF(A12="P",B12*$B$4))) P $11.95 =IF(A13="D",B13*$B$2,IF(A13="N",B13*$B$3,IF(A13="P",B13*$B$4))) D $5.15 =IF(A14="D",B14*$B$2,IF(A14="N",B14*$B$3,IF(A14="P",B14*$B$4))) D $5.25 =IF(A15="D",B15*$B$2,IF(A15="N",B15*$B$3,IF(A15="P",B15*$B$4))) N $4.50 =IF(A16="D",B16*$B$2,IF(A16="N",B16*$B$3,IF(A16="P",B16*$B$4))) N $7.25 =IF(A17="D",B17*$B$2,IF(A17="N",B17*$B$3,IF(A17="P",B17*$B$4))) N $14.50 =IF(A18="D",B18*$B$2,IF(A18="N",B18*$B$3,IF(A18="P",B18*$B$4))) Explain what the formula in cell C7 does. What value would you expect to see in cell C12? For Examiner's Use The formula was entered into cell C7 using a keyboard. Explain how to make this formula appear in cells C8 to C18 by just using a mouse. What formula should be typed into cell D7 to calculate the discounted price? Paul could extend this spreadsheet to calculate his total profit. He could then use it as a model. Give two reasons why financial models are used.
0417_s12_qp_13
THEORY
2012
Paper 1, Variant 3
View
( 0, , ) 11:25 E =IF(B18>0,"Y","N") Sheet 2 For Examiner's Use Explain what the function in cell A2 in Sheet 2 does. What station would you expect to see in cell A3 in Sheet 2? Explain what the function in cell C2 in Sheet 2 does. What value would you expect to see in cell C4 in Sheet 2? Explain what the function in cell D9 in Sheet 2 does. What value would you expect to see in cell D11 in Sheet 2? For Examiner's Use 15 Chi has employed Sarbjit, a systems analyst, to create a new database system for his mobile telephone business. • He keeps a number of different models in stock. • Most of the phones have a camera but some do not. • The rental plans have 100, 200 or 500 free texts depending on the amount the customer pays per month. • The minimum monthly payment is $10 and the maximum is $100. Complete the data dictionary below filling in the field names and the most appropriate data types to create a database using only the above information. Field name Data type Model Monthly payment Name and describe three different validation checks that could be used on the data in this database.
0417_s13_qp_12
THEORY
2013
Paper 1, Variant 2
View
Questions Discovered
51