For each question in Section A present a single formula. If you present more than one formula, then only the first one will be marked.

Question 1 What formula is in cell B8, to calculate the number of cameras in the datablock?

Question 2 What formula is in cell D5, to calculate the total number of Fixed cameras (1604)?

Question 3 What formula is in cell F5, to calculate the total number of 30mph cameras (1669)?

Question 4 What formula is in cell H4, to calculate the total number of cameras with a KSI of zero (681)?

Question 5 What formula is in cell D8, to calculate the number of cameras that are neither Fixed, Mobile or Red Light?

Question 6 What formula is in cell F2, to calculate the number of Fixed cameras on a 30mph road?

Question 7 What formula is in cell H5, to calculate the number of cameras with a KSI greater than zero to 1?

Question 8 What formula in cell D2 calculates the total KSI for Fixed cameras?

Question 9 What formula in cell D3 calculates the total KSI for Fixed and Mobile cameras?Question 10 What single formula would calculate the average KSI for Fixed and Mobile cameras?

Question 11 What formula would calculate the average KSI for Fixed and Mobile cameras on a 30mph road (do not include any dual speed cameras)?

Question 12 What formula has been entered in cell I11 and copied down to I3060 to determine the year the camera was installed, based on the date value in column G? If no valid date is entered in column G then the formula returns the text unknown.

SECTION A (25% of marks) Excel Functions and Formulas Use the dataset below for Sections A, B and C 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 column Description a number from 1 to 3,050 ID A Partnership B The police district which operates the cameras. There are 23 different partnerships in the datablock, listed alphabetically Cameras installed prior to 2002 are listed as Pre and those from 2002 onwards are listed as 'New' Installation Type Camera Type D 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 Location E Speed Limit F 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' 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 B D E F G H I 1 counts 1401.3 Fixed KSI Fixed & Mobile KSI E 2943.8 KSI counts counts = 0 681 1604 >0 to 1 Fixed & 30 976 Mobile & 30 603 speed counts 30 mph 1668 40 mph 541 50 mph 250 others 591 1282 6 000 UwN Fixed Mobile Red Light 1012 595 427 >1 to 2 > 2 others 431 3050 others 7 61 Installation Camera Speed Date 10 ID Location Limit Installed KSI Year Partnership 1 Avon Type Pre Type Fixed 11 A3139 Chilcompton > B3139 12 Avon Pre Fixed A358 Creech Castle > Taunton WN 13 Avon Pre A358 Henlade 14 4 Avon Pre 15 5 Avon Pre 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 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 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 Wilts 0.666 3057 3047 Wilts Pre 3058 3048 Wilts Pre 3059 3049 Wilts Pre Mobile A30 The Pheasant 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