8.1. Creating a spreadsheet
A subsection of Information Technology, 9626, through 8. Spreadsheets
Listing 10 of 27 questions
Sachin has typed in three dates in column A in the spreadsheet shown below. He has used the formula =TEXT(A1,"dd mmmm yyyy") in C1 and replicated it down. He has used another formula in column E to extract the name of the month from C1. Write down the formula he used in E1 which he then replicated down to E3 to extract the months shown. 03/11/2017 26/09/2017 03/06/2017 03 November 2017 26 September 2017 03 June 2017 November September June A B C D E = You can use the space below for any working out.
9626_m18_qp_12
THEORY
2018
Paper 1, Variant 2
View
Here is part of a spreadsheet showing the earnings of a selection of workers at a company. A Worker ID 42158P 63119M 63214P 89261N 97643M As As Se Se Se Cl Cl As Assistant Assistant Secretary Secretary Secretary Clerk Clerk Assistant $468 $800 $700 $540 $480 Job code Job type Hours worked this week Wage paid this week $12 B C D F E10 ✗ ✔ fx E Standard rate per hour $15 $20 =D10*VLOOKUP(B10,jobrate,3,0) The formula in cell E10 contains a named range, shaded in the spreadsheet. Describe, step by step, how the named range was created. Explain, step by step, what the formula in cell E10 does. Give two reasons why a named range is used. Explain, step by step, how to perform a filter on secretaries earning more than $750. Here is the same spreadsheet showing the formula in cell C7, VLOOKUP(B7,C$2:D$4,2,0). A Worker ID 42158P 63119M 63214P 89261N 97643M As As Se Se Se Cl Cl As Assistant Assistant Secretary Secretary Secretary Clerk Clerk Assistant $468 $800 $700 $540 $480 Job code Job type Hours worked this week Wage paid this week $12 B D F C7 ✗ ✔ fx E Standard rate per hour $15 $20 =VLOOKUP(B7,C$2:D$4,2,0) C By explaining what each part of the formula does, describe how it displays ‘Assistant’ in C7.
9626_m20_qp_12
THEORY
2020
Paper 1, Variant 2
View
Juan is the owner of a small company. He has created a spreadsheet to calculate the payroll. His spreadsheet is shown below. Each worker pays one of four different rates of tax. Each worker has one of four different tax allowances. A tax allowance is the amount that can be earned before workers start paying tax. Worker’s name Patrick O’Reilly John McDonnell Louise Gimenez Jeanette Grimault Xavier Hernandes Paolo Riva David Kennedy Sian Evans Karl Gustaffson Weekly wage Tax rate Tax allowance Taxable pay Wage after tax deduction Tax allowance range W X Y Z $247 $284 $268 $256 $233 $228 $272 $264 $242 B D A A C B D A B Y Z X Y W W Z Y W Range of tax rates A B C D 20% 30% 35% 40% A B C D E F G H I J To make it easier to do the calculations Juan has used named ranges. The Range of tax rates is called Rate and the Tax allowance range is called Allowance. Describe how you would set up the named range Allowance. Each worker has an amount, related to their Tax allowance, deducted from their weekly wage to calculate the Taxable pay. Write down the formula that should go in cell F11 to calculate the Taxable pay for Patrick O’Reilly. The formula should be easy to replicate and use a named range. = You can use the space below for any working you need. The Taxable pay is multiplied by the rate of tax to calculate the tax which must be paid, which is then deducted from their Weekly wage. The result will be their Wage after tax deduction. Write down the formula that should go in cell G11 to calculate the Wage after tax deduction. The formula should be easy to replicate and use a named range. Identify the steps which need to be taken to display the value in G11 exactly as shown in the spreadsheet. = You can use the space below for any working you need.
9626_s18_qp_11
THEORY
2018
Paper 1, Variant 1
View
Sc Wa 0.63 0.38 August 2019 August 2019 Spreadsheet Column D Formulae Using this spreadsheet as an example, explain, in detail, what is meant by absolute and relative cell referencing and explain why both are used. Describe, in detail, how other characteristics of spreadsheet software would enable this spreadsheet to be used for modelling purposes. Each characteristic must be different. 11 Normalisation is often used to refine the structure of a relational database. Describe four advantages of using normalisation.
9626_w21_qp_11
THEORY
2021
Paper 1, Variant 1
View
Here is a spreadsheet showing the prices of various toys in a shop. The discounted price is found by using a VLOOKUP formula in cells E8:E13. Discount code A 20% 15% 10% Price Discounted price Number sold $45 $35 $55 $50 $40 $30 $36.00 $28.00 $49.50 $42.50 $32.00 $25.50 B C A A C B A B Elephant Cheetah Giraffe Lion Leopard Zebra A B C D E F E G H Toy E8 fx ✘ =D8-(VLOOKUP(C8,$C$3:$D$5,2,FALSE)*D8) By describing what the formula in cell E8 does, explain the need for absolute and relative cell referencing. The owner of the shop wants to see which toys have a Discounted price that could be considered cheap and those that could be considered expensive. He would like to see all the cells with toys which are more than $40 shaded blue, all those that are less than $30 shaded yellow and the remainder shaded green. Describe how this could be achieved without each individual cell having to be changed. The owner wants a chart showing the Number sold of each toy as a proportion of the total number of toys sold. Identify the most suitable type of chart which would be used to show this information and describe how it would be produced.
9626_s18_qp_12
THEORY
2018
Paper 1, Variant 2
View
Paolo is the owner of a small company with ten workers. He is developing a spreadsheet to calculate the payroll. He has started by importing a text file of the workers’ names. The list consisted of each worker’s family name followed by a comma then a space then their first name. He wants to split the names into separate family names and first names by finding the position of the comma in each cell in column A. He has discovered a new function, LOCATE, which gives the position of the first occurrence of a single character in a word. For example, =LOCATE(“e”,I4) gives the result 2. His first attempt at the spreadsheet is shown below. Position of comma Rate per hour Family name Smith, John McDonnell, Louise Hernandes, Juan Kennedy, Sean Gustaffsson, Johan Davies, Iueuan Lopez Ramon Polishing Secretary Welding Orders Fitter Electrician Manager A B C D E F G I J L M K Name H Hours worked 31.25 First name Job Weekly pay
9626_s19_qp_11
THEORY
2019
Paper 1, Variant 1
View
Here is part of a spreadsheet showing the wages earned by a group of workers in a company. A J51549 P30731 C41827 E41231 Workers Number Hours worked this week Hours overtime worked Rate per hour paid Wage paid this week Overtime earned this week P77168 C43839 E34572 E48505 P40813 C32408 C69848 $14.00 $17.50 $12.50 $14.00 $12.50 $14.00 $17.00 $17.00 $12.50 $12.50 $14.00 $93.75 $252.00 $75.00 $42.00 $204.00 $112.50 $21.00 $532.00 $700.00 $593.75 $812.00 $575.00 $602.00 $612.00 $884.00 $612.50 $487.50 $581.00 B C F E3 ✗ ✔ fx D E F =IF(B3>40,C3*D3*1.5," ") Without referring to other cell references, explain, in detail, what every part of the formulae in cells E3 and E5 do. You may assume that the formula has been replicated down for all workers. This spreadsheet has not been tested. Describe the test plan you would use to make sure there are no errors in the formulae in column E of this spreadsheet. You can assume that the formula in column C works perfectly and does not need changing. Using only the sort feature explain how you would be able to make the spreadsheet used in part produce this display. A C32408 P77168 C41827 P40813 Workers Number Hours worked this week Hours overtime worked Rate per hour paid Wage paid this week Overtime earned this week E41231 C43839 C69848 J51549 E48505 E34572 P30731 $12.50 $12.50 $12.50 $12.50 $14.00 $14.00 $14.00 $14.00 $17.00 $17.00 $17.50 $93.75 $75.00 $112.50 $252.00 $42.00 $21.00 $204.00 $487.50 $575.00 $593.75 $612.50 $812.00 $602.00 $581.00 $532.00 $884.00 $612.00 $700.00 B C D E F
9626_s20_qp_11
THEORY
2020
Paper 1, Variant 1
View
Questions Discovered
27