8. Spreadsheets
A section of Information Technology, 9626
Listing 10 of 40 questions
The spreadsheet shows the values of used cars and the deposits required to buy them. Car make Diap Diap Sentel Sentel Panther WRM WRM Diap Panther WRM Diap Panther Sentel WRM D5 D1 M810 R500 ZE WB WA D4 ZK WB D3 ZJ G47 WB $4,752.00 $6,732.00 $6,655.00 $5,445.00 $8,470.00 $10,285.00 $12,100.00 $4,235.00 $9,438.00 $7,502.00 $5,082.00 $10,890.00 $11,374.00 $10,527.00 $475.00 $673.00 $666.00 $545.00 $847.00 $1,029.00 $1,210.00 $424.00 $944.00 $750.00 $508.00 $1,089.00 $1,137.00 $1,053.00 Car model Car value Deposit required A B C D E D2 ✘ ✔ =ROUND(C2*10%,0) fx Explain what is meant by the term ‘formula’. Refer to the contents of cell D2 in your answer. Explain what is meant by the term ‘function’. Refer to the contents of cell D2 in your answer. The spreadsheet has now been sorted. Car make Diap Diap Diap Diap Panther Panther Panther Sentel Sentel Sentel WRM WRM WRM WRM D5 D4 D3 D1 ZK ZJ ZE R500 M810 G47 WB WB WB WA $4,752.00 $4,235.00 $5,082.00 $6,732.00 $9,438.00 $10,890.00 $8,470.00 $5,445.00 $6,655.00 $11,374.00 $7,502.00 $10,285.00 $10,527.00 $12,100.00 $475.00 $424.00 $508.00 $673.00 $944.00 $1,089.00 $847.00 $545.00 $666.00 $1,137.00 $750.00 $1,029.00 $1,053.00 $1,210.00 Car model Car value Deposit required A B C D E Identify the steps that have been taken by a user to produce this sorted spreadsheet.
9626_s21_qp_13
THEORY
2021
Paper 1, Variant 3
View
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
Below is part of a spreadsheet Jose has created to model his personal finances.  A B 1 $2,600 Wages/salary 3 $120 Utilities 4 $100 Satellite/TV 5 $80 Phone 6 $30 Pet food 7 $1,000 Mortgage/rent 8 $80 Internet 9 $100 Insurance 10 $75 Hair/beauty 11 $550 Groceries 12 $150 Eating out 13 $100 Clothing 14 $40 Cinema 15 $160 Car/travel 17 $2,585 Total outgoings 19 $15 Balance Using only the sort feature explain how you would be able to get the following values in the given cells. $80 in cell A11 with Internet in cell B11 and $1,000 in cell A3. $100 in cell A8 with Clothing in cell B8.
9626_m17_qp_12
THEORY
2017
Paper 1, Variant 2
View
Questions Discovered
40