SECTION B (50% of marks) Excel Model Development The following section is multiple choice, except for questions 23 and 24 Submit one answer to each question. The following summary table has been created to summarise the data for the 23 Partnerships.

Question 13

Column N presents the total number of cameras for each Partnership. Which of the following formulae could be entered into cell N11, and then copied down to cell N33 to produce all 23 values?

A: =COUNT($B$11:$B$3060,$M$11)

B: =COUNTA($B$11:$B$3060,$M11)

C: =COUNTIF(B$11:B$3060,M$11)

D: =COUNTIF(B11:B3060,M11)

E: =COUNTIF($B$11:$B$3060,M11)

F: none of the above

Question 14

Column O presents the total KSI for each Partnership. Which of the following formulae could be entered into cell O11, and then copied down to cell O33 to produce all 23 values?

A: =SUMIF(H11:H3060,B11:B3060,M11)

B: =SUMIFS($H$11:H$3060,B$1:B$3060,$M11)

C: =SUMIFS(H$11:H$3060,B$11:B$3060,M11)

D: =COUNTIF($B$11:$B$3060,M11,$H$11:$H$3060)

E: =SUMIF($B$11:$B$3060,$M$11,$H$11:$H$3060)

F: none of the above

Question 15

Column P presents the total number of 30mph cameras for each Partnership, not including any dual speed cameras. Which of the following formulae could be entered into cell P11, and then copied down to cell P33 to produce all 23 values?

A: =COUNTIFS($F$11:$F$3060,30,$B$30:$B$306,M11)

B: =COUNTIFS($F$11:$F$3060,30,$B$11:$B$3060, Avon)

C: =COUNTIFS(F$11:F$3060,30mph,B$11:B$3060,M$11)

D: =COUNTIFS($F$11:$F$306,30,$B$11:$B$3060,$M11)

E: =COUNTIFS($F$11:$F$3060,30,$B$11:$B$3060,$M$11)

F: none of the above

Question 16

Columns Q and R presents the total number of 40mph and 50mph cameras respectively, for each Partnership, not including any dual speed cameras. Which of the following formulae could be entered into cell Q11, and then copied across to R11 and down to cell R33, to produce all 46 values?

A: =COUNTIFS($F$11:$F$3060,$M11,$B$11:$B$3060,Q$10)

B: =COUNTIFS($F$11:$F$3060,$Q$10,$B$11:$B$3060,$M11)

C: =COUNTIFS($F$11:$F$3060,Q10,$B$11:$B$3060,M11)

D: =COUNTIFS($F$11:$F$3060,Q$10,$B$11:$B$3060,M11)

E: =COUNTIFS($F$11:$F$3060,Q$10,$B$11:$B$3060,$M11)

F: none of the above

Question 17

Column S presents the total number of cameras for each Partnership, which are not 30mph or 40mph or 50mph. Which of the following formulae could be entered into cell S11, and then copied down to cell S33 to produce all 23 values?

A: =P11+$Q11+$R11-$N11

B: =COUNTIFS(F$11:F$3060,NOT(30,40,50),B$11:B$3060,M11)

C: =COUNTIFS(F$11:F$3060,AND(50),B$11:B$3060,M11)

D: =COUNTIFS($F$11:$F$3060,50,$B$11:$B$3060,$M11)

E: =COUNTIFS(F$11:F$3060,OR(50),B$11:B$3060,M11)

F: =$N11-SUM($P11:R11)

G: none of the above.

Question 18

Column T presents the average KSI per camera for 30mph cameras for each Partnership, not including any dual speed cameras. Which of the following formulae could be entered into cell T11, and then copied down to cell T33 to produce all 23 values?

A: =AVERAGEIF($F$11:$F$3060,30,$H$11:$H$3060)

B: =AVERAGEIKSI($H$11:$H$3060,$F$11:$F$3060,30,$B$11:$B$3060,M11)

C: =AVERAGEIFS($H$11:$H$3060,$F$11:$F$3060,T$10,$B$11:$B$3060,$M11)

D: =AVERAGEIFS(H$11:H$3060,F$11:F$3060,30,B$11:B$3060,$M$11)

E: =O11/P11 F: =(O11*(P11/N11))/P11

G: none of the above

Question 19

Column V presents the average KSI per camera for 50mph cameras for each Partnership, not including any dual speed cameras. Which of the following formulae could be entered into cell T11, and then copied down to cell T33 to produce all 23 values? Any Partnerships with no 50mph cameras should return a blank cell, rather than a #DIV/0! error.

A: =IFERROR(AVERAGEIFS($H$11:$H$3060,$F$11:$F$3060,50,$B$11:$B$3060,M$11),)

B: =IF(R11=0,,AVERAGEIFS($H$11:$H$3060,$F$11:$F$3060,50,$B$11:$B$3060,$M11))

C: =IF(R11>0,AVERAGEIFS($H$11:$H$3060,$F$11:$F$3060,50,$B$11:$B$3060,$M11))

D: =IF($R11>=1,AVERAGEIFS($H$11:$H$3060,$F$11:$F$3060,50,$B$11:$B$3060,M11)),)

E: =IF(ISBLANK(R11),,AVERAGEIFS(H$11:H$3060,F$11:F$3060,50,B$11:B$3060,M11))

F: =AVERAGEIFS(H$11:H$3060,F$11:F$3060,50,B$11:B$3060,M11)*R11

G: none of the above

Question 20

Cell R3 contains the formula: =MIN(R11:R33) to find the minimum number of 50mph cameras in any Partnership (the answer being zero) Which formula in cell R4 determines which Partnership this is for? (the answer being Cambridge)

A: =Cambridge

B: =M3

C: =INDEX(M11:M33,R3)

D: =INDEX(M11:M33,MATCH(0,R11:R33))

E: =(MATCH(R3,R11:R33,0),INDEX(M11:M33))

F: =(INDEX(M11:M33,MATCH(R3,R11:R33,0)))

G: none of the above

L M N o P Q R s T U V W Y 1 2 3 3 0 minimum: 2 Partnership: Wilts 4 Wilts Cambridge 5 Summary Table by Partnership 6 7 max: 15.00 8 3050 Camera counts by speed limit 1668 541 250 591 9 Average KSI per camera Highest Individual no. of Total 10 row Partnership cameras KSI 30 40 50 other 30 40 50 other All KSI 11 1 Avon 302 162 67 36 37 0.73 0.51 0.40 0.55 0.62 12.30 187.9 53.3 12 2 Bedford 79 54 3 13 0.53 0.37 0.78 1.45 0.67 4.30 13 35 32.6 16 7 0 12. 0.73 0.62 0.93 6.60 14 48 37.7 34 7 2 5 0.80 0.56 0.15 3.70 3 Cambridge 4 Cleveland 5 Derby 6 Dev and Corn 1.38 1.28 2.88 0.75 0.79 1.44 15 100 144.3 56 24 14 6 1.23 1.29 1.95 8.00 16 48 58.7 37 7 0 4 1.27 1.24 1.22 3.00 17 7 Dorset 122 116.7 61 14 6 41 0.75 1.19 1.50 1.11 0.96 5.67 18 8 Essex 193 164 25 1 3 0.67 1.03 0.33 0.33 0.71 6.00 137.4 87.7 19 9 Kent 64 52 4 6 2 1.32 0.93 2.00 1.67 1.37 4.33 20 10 Lancs 196 117.7 144 21 21 10 0.64 1.03 1.48 2.33 0.60 3.67 21 11 Leicester 68 104.5 34 13 4 17 1.51 1.54 1.75 1.53 1.54 5.30 22 12 Lincs 41 33.2 10 10 2 19 0.36 0.74 0.15 1.16 0.81 5.30 23 13 London 692 974.7 267 94 78 253 2.00 0.84 0.87 1.41 9.67 1.16 2.28 24 14 Norfolk 61 127.0 9 9 6 37 1.48 1.41 2.78 2.08 6.00 25 15 Northants 84 238.7 28 10 2 44 2.02 2.27 3.33 3.47 2.84 10.67 26 16 Notts 49 126.7 36 6 3 2.75 3.39 1.22 2.59 15.00 4 16 0.92 1.10 27 17 S Yorks 141 211.3 67 52 6 1.40 1.65 2.28 1.50 7.67 28 18 Stafford 209 63.7 101 71 30 7 0.30 0.36 0.21 0.19 0.30 3.67 29 19 Sussex 109 97.9 78 23 2 6 0.83 0.87 0.33 2.06 5.33 0.90 0.57 30 20 Thames 232 132.6 177 30 14 11 0.59 0.47 0.63 0.55 4.00 31 21 w Yorks 107 129.0 65 26 6 10 1.27 1.35 0.50 0.83 1.21 6.00 32 22 Warwick 57 14 9 6 28 1.05 1.81 2.61 1.71 4.00 97.7 9.4 1.82 0.79 33 23 Wilts 13 2. 3 2 6 0.83 0.50 1.00 0.72 1.67 34