Question 23,24, please.

The datablock below shows the data for 3,050 speed cameras in the UK. Every row in the datablock is a different camera. For each camera there is the following data: Component Description column ID a number from 1 to 3,050 A B Partnership The police district which operates the cameras. There are 23 different partnerships in the datablock, listed alphabetically Installation Type Cameras installed prior to 2002 are listed as Pre' and those from 2002 onwards are listed as 'New' Camera Type D Location E There are three types of camera; Fixed', 'Mobile' or 'Red Light' (meaning at a set of traffic lights) A brief description of where the camera is located, usually in terms of a road and town The speed limit, in mph, of the road where the camera is located. Most cameras are either 20, 30, 40, 50, 60 or 70 mph but some cameras are located at junctions so have dual speeds, such as 30/40' Speed Limit F Date Installed When the camera was installed G H KSI (3yr average) The number of Killed and Seriously Injured in accidents at the camera 'site', over a 3-year period, expressed as an average per year Year The year the camera was installed. A formula based on the date in column G I A B D E F G H 1 2 3 4 counts Fixed & 30 976 Mobile & 30 603 speed counts 30 mph 1668 40 mph 541 50 mph 250 others 591 Fixed KSIE 1401.3 Fixed & Mobile KSI E | 2943.81 counts Fixed 1604 Mobile 1012 Red Light 427 3050 others 5 KSI counts = 0 681 >0 to 1 1282 >1 to 2 595 > 2 431 others 61 6 7 8 9 10 ID Installation Camera Partnership Type Type 1 Avon Pre Fixed 11 Location A3139 Chilcompton > B3139 A358 Creech Castle > Taunton 12 2 Avon Pre Fixed 13 3 Avon Pre A358 Henlade 14 4 Avon Pre Speed Date Limit Installed KSI Year 30 01/07/1995 0.3 1995 50 01/07/1993 0.3 1993 40 01/07/1993 0.3 1993 40 01/10/1994 0.3 1994 40 01/11/1995 0 1995 40 31/12/1997 0.666 1997 40/50 31/12/1997 0.333 1997 40 unknown missing unknown 60 31/03/2002 1.33 2002 n/a 31/05/1994 0.0666 1994 15 5 Avon Pre 3056 3046 Fixed A358 Hornsbury Mill, Chard (Furnham Rd) Fixed A358 Pen Elm (north) Fixed A4259 Queens Drive, Swindon 1 , Red Light A4312 Oxford Road, Swindon unknown A30 Fovant Mobile A30 The Pheasant Wilts 3057 3047 Wilts Pre 3058 3048 Wilts Pre 3059 3049 Wilts Pre 3060 3050 Wilts Pre Red Light A419 Blunsdon 3061 Note 1: Only the first five cameras, and the last five cameras are presented in the screenshot above. The other rows of the datablock are currently hidden Note 2: All data within the datablock is pure data except column I which is a formula Note 3: Column B of the datablock does not contain any blank cells Note 4: However, other columns may contain blank cells, or other text such as "unknown" or "missing" Note 5: All cells that appear as a number (e.g. Speeds, KSI or Year) are all formatted as numbers The following summary table has been created to summarise the data for the 23 Partnerships. L M N O Q R S T U V w Y 1 2 minimum: 2 Partnership: Wilts 3 0 Wilts Cambridge 4 5 Summary Table by Partnership 6 7 max: 15.00 8 3050 Camera counts by speed limit 1668 541 591 Average KSI per camera 250 9 Highest Individual no. of Total 10 row Partnership cameras KSI 30 40 50 other 30 40 50 other All KSI 11 1 Avon 302 187.9 162 67 36 37 0.73 0.51 0.40 0.55 0.62 12.30 12 2 Bedford 79 53.3 54 9 3 13 0.53 0.37 0.78 1.45 0.67 4.30 13 3 35 16 7 0 12 0.73 0.62 1.38 0.93 6.60 Cambridge Cleveland 32.6 37.7 14 4 48 34 7 2 5 0.80 0.56 0.15 1.28 3.70 15 100 144.3 56 24 14 6 1.23 1.29 1.95 2.88 5 Derby 6 Dev and Corn 16 48 58.7 37 7 0 4 1.27 1.24 0.75 8.00 3.00 5.67 17 7 Dorset 122 116.7 61 14 6 41 0.75 1.19 1.50 1.11 18 8 Essex 193 164 25 1 3 0.67 1.03 0.33 0.33 0.79 1.44 1.22 0.96 0.71) 1.371 0.601 1.54 0.811 6.00 19 9 Kent 137.4 87.7 117.7 64 52 4 6 2 1.32 0.93 2.00 1.67 4.33 20 10 Lancs 196 144 21 21 10 0.64 1.03 1.48 2.33 3.67 21 11 Leicester 68 104.5 34 13 4 17 1.51 1.54 1.75 1.53 5.30 22 12 Lincs 41 33.2 10 10 2 19 0.36 0.74 0.15 1.16 23 13 London 692 974.7 267 94 78 253 2.00 0.84 0.87 1.16 1.41 5.30 9.67 6.00 24 14 Norfolk 61 9 9 6 37 1.48 1.41 2.78 2.28 127.0 238.7 2.08 2.84 25 15 Northants 84 28 10 2 44 2.02 2.27 3.33 3.47 10.67 26 16 Notts 49 126.7 36 6 3 4 2.75 3.39 1.22 0.92 2.59 15.00 27 17 S Yorks 141 211.3 67 52 6 16 1.40 1.65 2.28 1.10 7.67 1.50 0.30 28 18 Stafford 209 63.7 101 71 30 7 0.30 0.36 0.21 0.19 3.67 5.33 29 19 Sussex 109 97.9 78 23 2 6 0.83 0.87 0.33 2.06 30 20 Thames 232 132.6 177 30 14 11 0.59 0.47 0.63 0.55 0.90 0.57 1.21 4.00 6.00 31 21 w Yorks 107 65 26 6 10 1.27 1.35 0.50 0.83 129.0 97.7 32 22 Warwick 57 14 9 6 28 1.05 1.81 2.61 1.82 1.71 4.00 33 23 Wilts 13 9.41 2 3 2 6 0.83 0.50 1.00 0.79 0.72 1.67 34 Question 23 The highest observed KSI for any individual camera site is 15.0 (see cell Y7). What single formula will determine the Partnership, camera type and speed for this camera site, expressed as a text string in the following format: Notts - Fixed - 40mph (c Ensure the formula is dynamic such that if the KSI data changes the result would update. marks) Question 24 In the datablock, there are two cameras at the location: A1013 London Road, Grays 8 wwww a) what formula will produce the KSI for the first camera listed in the datablock? marks) b) what formula will produce the KSI for the second camera listed? (4 marks