1 MSCI242 Past Papers (2015 – 2020) Notes: Each exam uses the same dataset/datablock for all three sections. This will be the same for the 2021 exam too. For Part C, the 2015 and 2016 exams asked you to write a macro from scratch whereas from 2017 onwards it presents you with a pre-written macro and then asks you to find the mistakes/bugs in the code, rather than write the whole macro itself. The 2021 exam will do the same. Section A – Excel Functions and Formulas is always worth 25% This section requires you to write simple functions and formulas. The 2021 exam will do the same. Section B – Excel Model Development is always worth 50% This section comprises of 10 multiple-choice questions, each worth 5 marks. The 2021 exam will also have 10 multiple choice questions, with an additional 2 questions where you must write out the formula itself. Section C – VBA is always worth 25% For this section, you will be presented with a macro and your task is to find the mistakes/bugs in the code, and provide the correct version of the code. The 2021 exam will do the same. 2 2015 Exam SECTION A – Excel Functions and Formulas Use the dataset below for Section A and B The dataset shows information for 100 petrol and 100 diesel cars, for 22 different manufacturers. The data includes the model, the type of car, engine size, MPG and CO2 values. Note: rows 31 to 104 have been hidden in the illustration below. There is always the same number of petrol and diesel cars for each manufacturer, although the models are not always the same. For example, all 4 Audi petrol cars are the same model (A3) but for the Audi diesel cars we have 2 A3 models and 2 A6 models. Question 1 Suppose we want to calculate, in cell E2, the average engine size (cc) of all 100 petrol cars. Write the formula that would achieve this. (2 marks) Question 2 Suppose we want to calculate, in cell E3, the average engine size (cc) of all 200 cars (petrol and diesel). Write the formula that would achieve this. (2 marks) Question 3 Suppose we wanted to calculate, in cell G2, the highest petrol MPG value. Write the formula that would achieve this. (2 marks) Question 4 Suppose we wanted to calculate, in cell G3, the difference between the highest and lowest petrol MPG value. Write the formula that would achieve this. (2 marks) 3 Question 5 Suppose we wanted to determine, in cell G4, the Model of the car with the highest petrol MPG. Write the formula that would achieve this. (2 marks) Question 6 Suppose we want to count, in cell D2, the number of petrol Supermini cars. Write the formula that would achieve this. (2 marks) Question 7 Suppose we want to count, in cell D3, how many Supermini cars there are in the whole dataset (petrol or diesel). Write the formula that would achieve this. (3 marks) Question 8 What does the following formula return, and why? =MATCH(LARGE(F8:F18,1),M8:M18,0) (5 marks) Question 9 Suppose we want to count, in cell D4, how many Supermini cars there are in the whole dataset (petrol or diesel) with a MPG above 50. Write the formula that would achieve this. (5 marks) Part A Answers 1. =AVERAGE(E8:E107) 2. =AVERAGE(E8:E107,L8:L107) or, =SUM(E8:E107,L8:L107)/200 3. =MAX(G8:G107) or, =LARGE(G8:G107,1) 4. =MAX(G8:G107)-MIN(G8:G107) or, =LARGE(G8:G107,1)-SMALL(G8:G107,1) 5. =INDEX(C8:C107,MATCH(MAX(G8:G107),G8:G107,0)) or, =INDEX(C8:C107,MATCH(G2,G8:G107,0)) 6. =COUNTIF(D8:D107,"supermini") 7. =COUNTIF(D8:D107,"supermini")+COUNTIF(K8:K107,"supermini") 8. #N/A because the largest/greatest petrol production year for Audi/BMW/Citroen is 2014 which doesn’t appear in the diesel years for these three manufacturers. 9. =COUNTIFS($D$8:$D$107,"supermini",$G$8:$G$107,">50") +COUNTIFS($K$8:$K$107,"supermini",$N$8:$N$107,">50") 4 SECTION B – Excel Model Development Use the same dataset as for section A The following section is multiple choice. Answer either A, B C, D, E or F to each question. Only submit one answer to each question. Suppose we wish to create a summary table to the right of the main dataset, for the 22 manufacturers – see the illustration above. Question 10 Which formula will accurately count the total number cars for each manufacturer (both fuels)? The formula will be placed in cell S8 and be copied down to S29. A: = COUNTIF($B$8:$B$107,R8) B: = SUMIF($B$8:$B$107,R8) C: = COUNTIF($B$8:$B$107,R8)*2 D: = COUNTIF(B8:B107,R$8) E: = COUNTIFS($B$8:$B$107,R8,$J$8:$J$107,R8) F: none of the above (5 marks) Question 11 Which formula will find the average petrol MPG for each manufacturer? The formula will be placed in cell T8 and be copied down to T29. A: = SUMIF($B$8:$B$107,R8,$G$8:$G$107)/4 B: = AVERAGEIF($B$8:$B$107,R$8,$G$8:$G$107) C: = AVERAGEIF($G$8:$G$107,R8,$B$8:$B$107) D: = AVERAGE(G8:G11) E: = AVERAGEIF($B$8:$B$107,R8,$G$8:$G$107) F: none of the above (5 marks) 5 Question 12 Which formula will find the average MPG for each manufacturer (both fuels)? The formula will be placed in cell V8 and be copied down to V29. A: =AVERAGEIF($B$8:$B$107,R8,$G$8:$G$107)+AVERAGEIF($B$8:$B$107,R8,$N$8:$N$107)/2 B: =(SUMIF($B$8:$B$107,R8,$G$8:$G$107)+SUMIF($B$8:$B$107,R8,$N$8:$N$107))/S9 C: =AVERAGEIF($B$8:$B$107,R8,$N$8:$N$107) D: =AVERAGE(G8:G11,N8:N11) E: =SUMIF($B$8:$B$107,R8,$G$8:$G$107)/COUNTIF($B$8:$B$107,R8) F: None of the above (5 marks) Question 13 What will the following formula return? =INDEX($C$8:$C$107,MATCH(R10,$B$8:$B$107,0)) A: A3 B: BMW C: C1 D: 5 E: #N/A F: none of the above (5 marks) Question 14 What will the following formula return? =INDIRECT(INDEX($C$1:$C$107,MATCH(R12,$B$8:$B$107,0))) A: D7 B: Car Type C: Mini D: Focus E: #N/A F: none of the above (5 marks) Question 15 What does the following formula calculate, exactly? =COUNTIFS($B$8:$B$107,R8,$J$8:$J$107,"") A: the number of Kia cars where the petrol version is the same model as the diesel version. B: the number of missing Audi diesel cars C: the number of Audi cars where the diesel version is a different model to the petrol version. D: the number of Audi cars where the petrol version is the same model as the diesel version. E: the number of Audi cars with no model listed. F: it produces an error (5 marks) 6 Question 16 What does the following formula return? =INDEX(B8:B107,MATCH(MAX(H8:H107),H8:H107,0))&"-" &INDEX(C8:C107,MATCH(MAX(H8:H107),H8:H107,0)) A: the manufacturer with the greatest difference between their highest and lowest CO2 value B: it produces a #N/A error C: the manufacturer and model of the car with the highest CO2 in any year D: the manufacturer and model of the petrol car with the highest CO2 in any year. E: the manufacturer with the highest CO2 value in any year F: none of the above (5 marks) Question 17 What does the following formula calculate? =SUMIFS(H8:H107,F8:F107,"<2005",D8:D107,D16)/COUNTIFS(D8:D107,D16,F8:F107,"<2005") A: the average CO2 for pre-2005 MPVs B: the average CO2 for pre-2005 petrol MPVs C: the average CO2 for all pre-2005 ‘Lower Medium’ cars D: the total CO2 for all ‘Lower Medium’ cars E: the proportion of cars produced after 2005 F: none of the above (5 marks) 7 Suppose we wish to create a second summary table, in column W, summarising the data by year of production. Question 18 Suppose we wish to calculate, for each year, the proportion of all diesel cars that have a CO2 value above 150. The answer needs to be a value between 0 and 1. You can assume that all years have diesel cars. The formula will be placed in cell X8 and be copied down to X22. A: =COUNTIFS($M$8:$M$107,W8,$O$8:$O$107,">150")/COUNTIF($M$8:$M$107,W8) B: =COUNTIFS($M$8:$M$107,W8,$O$8:$O$107,>150)/COUNT($M$8:$M$107) C: =COUNTIFS($M$8:$M$107,W8,$O$8:$O$107,>150)/100 D: =COUNTIF($O$8:$O$107,">150")/COUNTIF($M$8:$M$107,W8) E: =PROPORTION($O$8:$O$107,>150) F: none of the above (5 marks) Question 19 Which formula will calculate the average CO2 for each year, for only diesel cars? The formula will be placed in cell Y8 and be copied down to Y22. Note that for this task you cannot assume that all years have diesel cars and the formula must not produce an error. If there are no diesel cars for any year the formula must return a blank cell. A: =IF(ISZERO(COUNTIF($M$8:$M$107,W8)),"",AVERAGEIF($M$8:$M$107,W8,$O$8:$O$107)) B: =IF(ISNA(AVERAGEIF($M$8:$M$107,W8,$O$8:$O$107)),"",AVERAGEIF ($M$8:$M$107,W8, $O$8:$O$107)) C: =IF(COUNTIF($M$8:$M$107,W8)=0,AVERAGEIF($M$8:$M$107,W8,$O$8:$O$107),"") D: =IF(ISERROR(COUNTIF($M$8:$M$107,W8)),"",AVERAGEIF($M$8:$M$107,W8,$O$8:$O$107)) E: =IF(COUNTIF($M$8:$M$107,W8)=0,"",AVERAGEIF($M$8:$M$107,W8,$O$8:$O$107)) F: none of the above (5 marks) 8 Part B Answers 10. C 11. E 12. F 13. C 14. B 15. D 16. D 17. B 18. A 19. E 9 SECTION C – VBA Question 20 The dataset has some bugs in it. Some of the CO2 values are incorrect due to a calibration fault on the testing equipment. The following adjustments need to be made: Adjustment 1: CO2 values for all diesel cars should be increased by 2 units e.g. Audi A3 goes from 112 to 114 Adjustment 2: CO2 values for all ‘Mini’ type petrol cars need to be increased by 5% of their current value Adjustment 3: CO2 values for all petrol Ford cars produced before 2005 are all too high and need to be reduced by 3.5 units. Note: for Ford ‘Mini’-type cars this reduction should be done after Adjustment 2. Write a macro (subroutine) which will make these three adjustments. The adjustments need to be made without moving or copying the data elsewhere. Include comments if you think they add value. Include a message box towards the end of the macro to inform the user how many changes have been made for adjustment 2 and 3 respectively. Comment on any assumptions you have made about the quality of the data which may influence the success of the macro. (25 marks) See overleaf for code 10 End of 2015 Exam 11 2015 Exam (Alternative) SECTION A – Excel Functions and Formulas Use the dataset below for Sections A, B and C The dataset shows information for 108 Local Highways Authorities (LHA) in England. The LHAs are responsible for looking after all roads (except motorways). For each LHA we have data on the following: • Name e.g. Durham [column B] • LHA Type; either Rural, Urban or Mixed, depending on population density [column C] • Population of the LHA [column D] • Area of the LHA, in hectares [column E] • Annual Spending (average over the last 3 years) in thousands of pounds [column F] • Annual Spending per head of population [column G] • Annual Spending per mile of road, in thousands of pounds [column H] • Mileages in the Local Authority by type of road o rural A roads (main roads through low population density areas) [column I] o urban A roads [column J] o rural B roads [column K] o urban B roads [column L] o rural C roads [column M] o urban C roads [column N] o rural U roads [column O] o urban U roads [column P] Note: rows 19 to 107 have been hidden in the illustration below. For example, the Durham LHA has an annual spending of £14,354,000, which equates to £27.97 per head. 12 Question 1 Suppose we want to calculate the total population across all 108 LHAs. Write the formula that would achieve this. (2 marks) Question 2 Suppose we want to calculate the average area of a LHA. Write the formula that would achieve this. (2 marks) Question 3 Suppose we want to calculate the largest LHA by area. Write the formula that would achieve this. (2 marks) Question 4 Suppose we want to calculate the second largest LHA, by area. Write the formula that would achieve this. (2 marks) Question 5 Suppose we want to determine the name of the second largest LHA, by area. Write the formula that would achieve this. (2 marks) Question 6 Suppose we want to how many Rural type LHAs there are. Write the formula that would achieve this. (2 marks) Question 7 Suppose we want to know how many Rural type LHAs have more than 500,000 people. Write the formula that would achieve this. (2 marks) Question 8 Suppose we want to calculate the average annual spending (in pounds) for LHAs with a population greater than 500,000. Write the formula that would achieve this. (2 marks) Question 9 Suppose we want to calculate the total annual spending (in pounds) for Rural LHAs with a population between 500,000 and 1,000,000. Write the formula that would achieve this. (4 marks) Question 10 Suppose we want to know how many Rural or Mixed LHAs are Shire counties i.e. have a name that ends in the text “shire”, as in Gloucestershire. Write the formula that would achieve this. (5 marks) Part A Answers 1. =SUM(D9:D116) 2. =AVERAGE(E9:E116) or, =SUM(E9:E116)/108 3. =MAX(E9:E116) or, =LARGE(E9:E116,1) 4. =LARGE(E9:E116,2) 5. =INDEX(B9:B116,MATCH(LARGE(E9:E116,2),E9:E116,0)) 6. =COUNTIF(C9:C116,"Rural") 7. =COUNTIFS(C9:C116,"Rural",D9:D116,">500000") 8. =AVERAGEIF(D9:D116,">500000",F9:F116)*1000 9.=SUMIFS(F9:F116,C9:C116,"Rural",D9:D116,">500000",D9:D116,"<1000000")*1 000 10.=COUNTIFS(C9:C116,"Rural",B9:B116,"*shire")+COUNTIFS(C9:C116,"Mixed",B 9:B116,"*shire") 13 SECTION B – Excel Model Development Use the same dataset as for section A The following section is multiple choice. Answer either A, B C, D, E or F to each question. Only submit one answer to each question. Suppose we wish to create a small summary table to the right of the main dataset, for the 3 types of LHA – see the illustration above. Question 11 Which formula will calculate the average area for each LHA type? The formula will be placed in cell U3 and be copied down to U5. A: = AVERAGE($E$9:$E$116) B: = AVERAGEIF($C$9:$C$116,U3,$E$9:$E$116) C: = AVERAGEIF($E$9:$E$116,T3,$C$9:$C$116) D: = AVERAGEIF($C$9:$C$116,”Rural”,$E$9:$E$116) E: = SUMIF($C$9:$C$116,T3,$E$9:$E$116)/108 F: none of the above (5 marks) Question 12 Which formula will find the average spending per head for each LHA type? The formula will be placed in cell V3 and be copied down to V5. A: = AVERAGEIF($C9:$C$116,T3,$G9:$G$116) B: = SUMIF($C$9:$C$116,T3,F9:F116)/SUMIF($C$9:$C$116,T3,$D$9:$D$116) C: = SUMIF($C$9:$C$116,T3,$G$9:$G$116) D: = SUMIF($C$9:$C$116,T3,$G$9:$G$116)/COUNTIF($C$9:$C$116,T3) E: = AVERAGEIFS($C$9:$C$116,T3,$G$9:$G$116) F: none of the above (5 marks) Question 13 Which formula will calculate the difference between the average spending for each LHA type and the global average spending across all 108 LHAs? If the average spending for, say, Rural LHAs is less than the global average then the answer should be a negative number. The formula will be placed in cell W3 and be copied down to W5. A: = AVERAGEIF($C$9:$C$116,T3,$F$9:$F$116)-AVERAGE($F9:$F$116) B: = AVERAGE($F$9:$F$116)-AVERAGEIF($C$9:$C$116,T3,$F$9:$F$116) C: = AVERAGEIF($C$9:$C$116,T3,$F$9:$F$116)-AVERAGE($F$9:$F$116) D: = AVERAGE($F$9:$F$116)/COUNTIF($C$9:$C$116,T3) E: = AVERAGE($F$9:$F$116)-(AVERAGE($F$9:$F$116)/COUNTIF($C$9:$C$116,T3)) F: None of the above (5 marks) 14 Suppose we wish to create a second summary table to the right of the main dataset, for the 8 types of road – see the illustration below. Note: the cell ranges for the data for each of the 8 road types is also provided, in column Y. For example, the data for Rural ‘B’ type roads is in cells K9:K116. Question 14 Which formula will find the total road miles for all 8 road types? The formula will be placed in cell AA3 and be copied down to AA10. A: = Y3 B: = SUM(Y3) C: = SUM(INDIRECT(Y$3)) D: = SUMIF($I$7:$P$7,Z3,$I$9:$P$116) E: = INDIRECT(Y3) F: none of the above (5 marks) Question 15 Which formula will find the maximum road miles for all 8 road types? The formula will be placed in cell AB3 and be copied down to AB10. A: = INDIRECT(MAX(Y3)) B: = INDEX(MAX(Y3),1) C: = MAX(Y3) D: = LARGE(INDIRECT(Y3),1) E: = INDEX(Y3,MATCH(MAX(Y3),Y3,0)) F: none of the above (5 marks) Question 16 If the following formula is placed in cell AC3 and copied down to AC10, what will it produce? = COUNTIF(INDIRECT(Y3),">500") A: the number of roads than are more than 500 miles long, for each road type B: the number of LHAs with a total mileage greater than 500 miles, for each road type C: the total mileage if it is greater than 500 miles, for each road type D: the number of LHAs with an average mileage of more than 500 miles, for each road type E: it produces an error for all 8 road types F: none of the above (5 marks) 15 Question 17 If the following formula is placed in cell AD3 and copied down to AD10, what will it produce? = COUNTIF(INDIRECT(Y3),0) A: it produces a #DIV/0! error for at least one road type B: it counts how many road types do not occur in at least one LHA, for each road type C: it counts how many LHAs are missing at least one data point, for each road type D: it counts how many LHAs do not contain this type of road, for each road type E: it counts how many data points are missing, for each road type F: none of the above (5 marks) Question 18 If the following formula is placed in cell AE3 and copied down to AE10, what will it produce? = IF(SUM(INDIRECT(Y3))>0.1*SUM($I$9:$P$116),1, "") A: it produces a #DIV/0! error for at least one road type B: a value of 1 if the road type has a complete set of data i.e. no missing values. C: the total miles for a road type that are non-zero D: it sums all the mileages for LHAs which are in the top 10% of total mileages E: a value of 1 if the road type has a total mileage greater than 10 % of the total mileage for all roads. F: none of the above (5 marks) Question 19 What does this formula calculate? A: the total mileage for all rural roads in rural LHAs B: the total mileage for all urban roads in urban LHAs C: the total mileage for all B roads in all non-rural LHAs D: the total mileage for all rural B roads in all rural LHAs E: the total mileage for all roads in non-rural LHAs F: none of the above (5 marks) Question 20 What does this formula calculate? =SUMIFS(D9:D116,C9:C116,C11,B9:B116,"*"&RIGHT(B11,4)&"*") A: total mileage for the LHA of Hartlepool B: total number of Mixed LHAs which have more than 4 letters in their name C: total population of all LHAs which have more than 4 letters in their name D: total population of all Mixed LHAs which begin with the letter "H" E: total population of all Urban LHAs with more than 4 letters in their name F: total population of all Mixed LHAs with the text "pool" in their name G: none of the above (5 marks) 16 Part B Answers 11. F 12. D 13. C 14. F 15. D 16. B 17. D 18. E 19. A 20. F 17 SECTION C – VBA Question 21 The dataset has some bugs in it. Some of the mileage values are incorrect due to a fault with the GPS program that calculated all the distances. The following adjustments need to be made: Adjustment 1: All Principal rural ‘A’ mileages need to be increased by 1.1% Adjustment 2: All Urban ‘B’ mileages need to be reduced by 0.2 miles (but not below zero) Adjustment 3: If the mileage for an Urban ‘C’ road is below 10 miles then it needs to be increased by 20% Write a macro (subroutine) which will make these three adjustments. The adjustments need to be made without moving or copying the data elsewhere. Include comments if you think they add value. Include a message box towards the end of the macro to inform the user how many mileages in the complete dataset are equal to zero. (25 marks) End of 2015 Exam (Alternative) 18 2016 Exam Use the dataset below for Sections A, B and C The dataset shows sales information for an international company that sells technical medical equipment, via trade shows at different locations around the globe. Data has been collected for 2014 and 2015. Most of the trade shows last between 2 and 5 days. There are 109 trade days listed for 2014 (rows 2 to 110) and 96 days for 2015 (rows 11 to 206), so 205 days in total. Each row in the dataset shows: • The date of the show [column B] This is an 8-digit code, starting with the year (4 digits), then the month number (2 digits) and finally the day number (2 digits). For example, 20140102 is 2nd January 2014. • the total number of transactions for medical equipment sales done on that day [column C] • the number of transactions that are classified as Grade ‘A’ equipment type [column D] • the number of transactions that are classified as Grade ‘B’ equipment type [column E] • the number of warranty issues that had to be dealt with on that day [column F] • the location where the trade show was being held [column G] where 1 = UK, 2 = Europe, 3 = USA, 4 = Asia and 5 = other Note: some rows have been hidden in the illustration below. For example, the first trade show in 2014 lasted 2 days, on the 2nd and 3rd of January, in the UK (code 1). And the first trade show of 2015 lasted 4 days, from the 7th to the 10th of January, this time in the USA (3). 19 Question 1 Suppose we want to calculate the total number of transactions across both years. Write the formula that would achieve this. (2 marks) Question 2 Suppose we want to calculate the mean number of Grade ‘A’ transactions for 2015. Write the formula that would achieve this. (2 marks) Question 3 Suppose we want to know the fewest transactions for any trade day (both years). Write the formula that would achieve this. (2 marks) Question 4 Suppose we want to find the trade day number when the greatest number of transactions occurred. Write the formula that would achieve this. (2 marks) Question 5 What will this formula return? =INDEX(G111:G116,MATCH(SMALL(D111:D116,1),D111:D116,0)) (2 marks) Question 6 Suppose we want to know how many trade days had no warranty issues (both years). Write the formula that would achieve this. (2 marks) Question 7 Suppose we want to know how many trade days had more than 50 transactions (both years). Write the formula that would achieve this. (2 marks) Question 8 Suppose we want to calculate the total number of Grade ‘A’ transactions for shows based in the UK and USA. Write the single formula that would produce this single value. (3 marks) Question 9 Suppose we want to calculate the total number of trade days based in the UK and USA which had 2 or more warranty issues. Write the formula that would produce this single value. (3 marks) Question 10 Suppose we want to calculate the average number of Grade ‘A’ transactions for UK and USA shows. Write the single formula that will produce this single value. (5 marks) 20 Part A Answers 1. =SUM(C2:C206) 2. =AVERAGE(D111:D206) 3. =MIN(C2:C206) or =SMALL(C2:C206,1) 4. =MATCH(MAX(C2:C206),C2:C206,0) 5. 3 6. =COUNTIF(F2:F206,0) 7. =COUNTIF(C2:C206,">50") 8. =SUMIF(G2:G206,1,D2:D206)+SUMIF(G2:G206,3,D2:D206) 9. =COUNTIFS(G2:G206,1,F2:F206,">=2")+COUNTIFS(G2:G206,3,F2:F206,">=2") 10. =(SUMIF(G2:G206,1,D2:D206)+SUMIF(G2:G206,3,D2:D206))/ (COUNTIF(G2:G206,1)+COUNTIF(G2:G206,3)) 21 SECTION B – Excel Model Development Use the same dataset as for section A The following section is multiple choice. Answer either A, B C, D, E, F or G to each question. Submit one answer to each question. Suppose we wish to create a small summary table to the right of the main dataset, for the 5 location codes – see the illustration above. Question 11 Which formula will calculate the total number of trade days for each location code? The formula will be placed in cell L2 and be copied down to L5. A: = SUMIF($G$2:$G$206,LEFT(1)) B: = SUMIF($G$2:$G$206,LEFT(K2,1)) C: = COUNTIF($G$2:$G$206,LEFT(K2,1)) D: = COUNTIF($G$2:$G$206,LEFT(K1,2)) E: = SUM($G$2:$G$206,LEFT(K2,1)) F: none of the above (5 marks) Question 12 Which formula will find the average number of transactions, for each location code? The formula will be placed in cell M2 and be copied down to M5. A: = AVERAGE($C$2:$C$206) B: = AVERAGEIF($G$2:$G$206,ROW(K2),$C$2:$C$206) C: = AVERAGEIF($G$2:$G$206,I2,$C$2:$C$206) D: = SUMIF($G$2:$G$206,K2,$C$2:$C$206)/COUNTIF($G2:$G$206,K2) E: = AVERAGEIF($G$2:$G$206,$K$2,$C$2:$C$206) F: none of the above (5 marks) Question 13 Which formula will find the number of trade days which had at least one warranty issue, for each location code? The formula will be placed in cell N2 and be copied down to N5. A: = SUMIFS($G$2:$G$206, LEFT(K2,1),$F$2:$F$206,">0") B: = COUNTIFS($G$2:$G$206, LEFT(K2,1),$F$2:$F$206,OR(1,2)) C: = COUNTIFS($G$2:$G$206, LEFT(K2,1),$F$2:$F$206,>1) D: = COUNTIFS($G$2:$G$206,ROW(K2),$F$2:$F$206,">=1") E: = COUNTIFS($G$2:$G$206, LEFT(K2,1),$F$2:$F$206,"<>0") F: None of the above (5 marks) 22 Suppose we want to analyse the data in terms of the length of the shows, in days In order to do this a new column (H) has been added to the dataset, like so: Note: some rows have been hidden in the illustration above. Question 14 Which formula will calculate the average length of a show, in days, for each location code? The formula will be placed in cell O2 and be copied down to O5. A: = AVERAGE($H$2:$H$206) B: = COUNTIF($G$2:$G$206,LEFT(K2,1))*COUNTIF($H$2:$H$206,LEFT(K2,1)) C: = IF($G$2:$G$206=LEFT(K2,1),AVERAGE($H$2:$H$206)) D: = SUMIF($G$2:$G$206,LEFT(K2,1),$H$2:$H$206)/COUNTIF($G$2:$G$206,LEFT(K2,1)) E: = AVERAGEIF($G$2:$G$206,LEFT(K2,1),$H$2:$H$206) F: none of the above (5 marks) 23 To analyse the length of shows, a second summary table has been created, like so: Question 15 Which formula will calculate the how many trade shows there are, for each of the six durations? In other words, how many shows last exactly one day? How many last two days? And so on. The formula will be placed in cell R2 and be copied down to R7. A: = SUMIF($H$2:$H$206,Q2) B: = COUNTIF(H$2:H$206,Q2) C: = COUNT($H$2:$H$206,Q2) D: = SUMIF($H$2:$H$206,Q2)/COUNTIF($H$2:$H$206,Q2) E: = COUNT(INDIRECT(Q2) F: none of the above (5 marks) Question 16 Which formula will calculate the total number of transactions, for each of the six durations? The formula will be placed in cell S2 and be copied down to S7. If there aren’t any shows for a particular duration – for example no shows lasting 6 days – then this cell should be left blank. A: = SUMIF($H$2:$H$206,Q2,$C$2:$C$206) B: = SUMIF($H$2:$H$206,Q2,$D$2:$D$206)+SUMIF($H$2:$H$206,Q2,$E$2:$E$206) C: {= IF($H$2:$H$206=Q2,SUM($C$2:$C$206),"")} D: =IF(SUMIF($H$2:$H$206,Q2,$C$2:$C$206)=0,"",SUMIF($H$2:$H$206,Q2,$C$2:$C$206) E: =IF(COUNTIF($H$2:$H$206,Q2)=0,"",SUMIF($H$2:$H$206,Q2,$C$2:$C$206) F: none of the above (5 marks) 24 The next phase of modelling analyses the data by month. In order to extract the month number for each show, the following column of data has been added (column I) Question 17 What formula has been used to extract the month number? This formula has been placed in cell I2 and copied down to I206. A: = MONTH(B2) B: = FIND(MONTH(B2)) C: = MID(B2,4,2) D: = LEFT(RIGHT(B2,4),2) E: = RIGHT(LEFT(B2,4),2) F: = RIGHT(RIGHT(B2,LEN(B2)-4),2) G: none of the above (5 marks) 25 To analyse the data by month, a third summary table has been created, like so: Question 18. The following formula is placed in cell V2 and be copied down to V13. What will it produce? =SUMIFS($C$2:$C$206,$I$2:$I$206,U2,$G$2:$G$206,2)- SUMIFS($D$2:$D$206,$I$2:$I$206,U2,$G$2:$G$206,2) A: it produces a #DIV/0! error B: it produces a #NUM! error C: the total number of Grade ‘B’ transactions, for the Europe shows, for each month D: the difference between the Grade ‘A’ and ‘B’ transactions, for the Europe shows, for each month E: the difference between the Grade ‘A’ and ‘B’ transactions, for the UK shows, for each month F: the total transactions for the Europe shows, for each month G: none of the above (5 marks) Question 19 The following formula is placed in cell W2 and be copied down to W13. What will it produce? =COUNTIF($I$2:$I$206,U2)-COUNTIFS($I$2:$I$206,U2,$H$2:$H$206,"") A: the number of shows for each month B: the number of shows lasting more than one day, for each month C: the average length of the shows, in days, for each month D: the number of Europe or UK shows, for each month E: the number of days each month where there isn’t a show scheduled F: it produces a #NUM! value G: none of the above (5 marks) 26 Question 20 What does this formula return? =RIGHT(INDEX($B$2:$B$206,MATCH(MAX($F$2:$F$206),$F$2:$F$206,0)), INDEX($H$2:$H$206,MATCH(I2,$I$2:$I$206,0))) A: the duration, in days, of the longest show B: the month that the longest show occurs in C: the day of the month for the show with the highest number of warranty issues D: the day of the month for the show with the longest duration E: the number of times the highest number of warranty issues occurs F: the full date code for the show with the highest number of transactions G: none of the above (5 marks) Part B Answers 11. C 12. F 13. E 14. E 15. B 16. F 17. D 18. C 19. A 20. C 27 SECTION C – VBA Question 21 As always, the dataset has some bugs in it. Some of the values are incorrect due to a fault with the database program used to compile the data. The following adjustments need to be made: Adjustment 1: Any show in the USA needs to be given a location code of 4, not 3, and any Asia show needs to be coded with a 3, not a 4. Adjustment 2: Sometimes the total number of transactions (column C) is incorrect in that it does not equal the Grade ‘A’ plus Grade ‘B’ transactions. If this is the case, then you need to adjust the total transactions value so that that it does equal the Grade ‘A’ plus Grade ‘B’ transactions. Adjustment 3: If the number of warranty issues is currently negative, then it needs to be changed to zero Write a macro (subroutine) which will make these three adjustments. The adjustments need to be made without moving or copying the data elsewhere. Include comments in your code if you think they add value. Include a message box towards the end of the macro to inform the user how many changes have been made as a result of adjustments 2 and 3 (a separate counter for each adjustment). (25 marks) 28 End of 2016 Exam 29 2017 Exam Use the dataset below for Sections A, B and C The dataset shows information for 3554 cars tested in 2014. The data is organised alphabetically by manufacturer, and includes the following: • Manufacturer e.g. Alfa Romeo [column B] • Model of car e.g. Giulietta [C] • Description of the car [D] • Transmission e.g. M6 for a 6-speed manual gearbox [E] • Engine Capacity size of the engine, in cubic centimetres [F] • Fuel Type either Petrol or Diesel (no other fuels are listed) [G] • MPG Miles per gallon (of fuel used) [H] • CO2 g/km grams of carbon dioxide emitted per kilometre driven [I] • Noise Level dB A measure of how loud the engine is, in decibels [J] • CO Emissions milligrams of carbon monoxide produced, during the test cycle [K] Note: rows 16 to 3550 have been hidden in the screenshot above. 30 Question 1 Suppose we want to calculate the average MPG across all 3554 cars. Write the formula that would achieve this. (2 marks) Question 2 Suppose we want to know how many diesel cars there are. Write the formula that would achieve this. (2 marks) Question 3 Suppose we want to know what the smallest engine capacity is. Write the formula that would achieve this. (2 marks) Question 4 Suppose we want to know the average MPG for just diesel cars. Write the formula that would achieve this. (2 marks) Question 5 What will this formula return? =MATCH(222,K5:K3558,0)*MATCH(5,A5:A3558,0) (2 marks) Question 6 As you can see, some of the Noise Level values in column J are either missing or N/A values. Suppose we want to know how many cars have an actual number entered for this variable. Write the formula that would achieve this. (2 marks) Question 7 Suppose we want to know how many N/A values are entered in column J. Write the formula that would achieve this. (2 marks) Question 8 Suppose we want to know the percentage of cars that have a noise level above 74 decibels (dB). Write the single formula that would produce this single value. (3 marks) Question 9 Suppose we want to know which manufacturer produces the car with the loudest engine. Write the formula that would produce this single value. (4 marks) Question 10 Suppose we want to know which fuel (diesel or petrol) produces the loudest engines, on average. Write the single formula that will produce this single value. (4 marks) 31 Part A Answers 1. =AVERAGE(H5:H3558) 2. =COUNTIF(G5:G3558,"Diesel") 3. =MIN(F5:F3558) or =SMALL(F5:F3558,1) 4. =AVERAGEIF(G5:G3558,"diesel",H5:H3558) 5. 25 6. =COUNT(J5:J3558) 7. =COUNTIF(J5:J3558,"N/A") 8. =COUNTIF(J5:J3558,">74")/COUNT(J5:J3558) 9. =INDEX(B5:B3558,MATCH(MAX(J5:J3558),J5:J3558,0)) 10. Finds a tie: =IF(AVERAGEIF(G5:G3558,"diesel",J5:J3558)=AVERAGIF(G5:G3558,"petrol",J5:J3558), "a tie",=IF(AVERAGEIF(G5:G3558,"diesel",J5:J3558)>AVERAGIF(G5:G3558,"petrol",J5:J3558), "diesel is the loudest","petrol is the loudest")) 4 marks Simpler version: =IF(AVERAGEIF(G5:G3558,"diesel",J5:J3558)>AVERAGIF(G5:G3558,"petrol",J5:J3558), "diesel is the loudest","petrol is the loudest") 3 marks 32 SECTION B (50% of marks) – Excel Model Development Use the same dataset as for section A The following section is multiple choice. Answer either A, B C, D, E, F or G to each question. Submit one answer to each question. An additional column (L) has been added to the data, stating the number of gears in the Transmission, for each car. The number of gears is always an integer value between 3 and 7, with the number either the last digit of the Transmission code (e.g. M5) or the first digit of the Transmission code (e.g. 6AT). All cars follow this rule. For example, the ABARTH Punto Evo has an M6 transmission, meaning it has 6 gears. Furthermore, any Transmission code with an “M” in it, such as M6, means it is a manual transmission, and any code with an “A” in it, such as 6AT, means it is an automatic transmission. There are no codes which have both letters. In other words, a car cannot be manual and automatic. All Transmission codes contain an “M” or an “A”. Question 11 Which formula will calculate how many diesel cars have 6 gears? A: = SUMIFS(G5:G3558,"diesel",L5:L3558,6) B: = COUNTIFS(G5:G3558,"diesel",L5:L3558,6) C: = COUNTIFS(G5:G3558,diesel,L5:L3558,6) D: = COUNTIF(G5:G3558,"diesel")+COUNTIF(L5:L3558,6) E: = SUMIF("diesel"&6,G5:G3558,L5:L3558) F: none of the above (5 marks) 33 Question 12 Which formula will calculate how many diesel cars have 5 or 6 gears? A: = SUMIFS(G5:G3558,"diesel",L5:L3558,OR(5,6)) B: = COUNTIFS(G5:G3558,"diesel",L5:L3558,5&6) C: = COUNTIFS(G5:G3558,diesel,L5:L3558,5+6) D: = COUNTIF(G5:G3558,"diesel")+COUNTIF(L5:L3558,5)+COUNTIF(L5:L3558,6) E: = COUNTIFS(G5:G3558,"diesel",L5:L3558,AND(<7,>4)) F: none of the above (5 marks) Question 13 Which formula will find the average MPG for cars with 5 or 6 gears? A: = AVERAGEIF(L5:L3558,5,6,H5:H3558) B: = AVERAGEIF(L5:L3558,OR(5,6),H5:H3558) C: = (AVERAGEIF(L5:L3558,5,H5:H3558)+AVERAGEIF(L5:L3558,5,H5:H3558))/2 D: = SUMIF(L5:L3558,OR(5,6),H5:H3558)/COUNTIF(L5:L3558,OR(5,6) E: = (SUMIF(L5:L3558,5,H5:H3558)+SUMIF(L5:L3558,6,H5:H3558))/ (COUNTIF(L5:L3558,5)+COUNTIF(L5:L3558,6)) F: none of the above (5 marks) Question 14 Which formula will calculate how many manual cars there are? A: = IF(E5:E3558="*M*",1,0) B: = COUNTIF(E5:E3558,"*M*") C: = COUNTIF(E5:E3558,"*M")+COUNTIF(E5:E3558,"M*") D: = COUNTIF(E5:E3558,*M*) E: = COUNTIF(E5:E3558,LEFT(E5:E3558,1)="M") F: none of the above (5 marks) Question 15 What does this formula return? = IF(ISNUMBER(LEFT(E6,1)*1),LEFT(E6,1)*1,IF(ISNUMBER(RIGHT(E6,1)*1),RIGHT(E6,1)*1,"")) A: a blank cell B: 5 C: 6 D: an error of any type E: 25 F: 1 G: none of the above (5 marks) Question 16 What does this formula return? = SEARCH(LEFT(B6,1),E6)+FIND("e",G6)+1 A: #VALUE! B: AE1 C: 1 D: a blank cell E: 5 F: 3 G: none of the above (5 marks) 34 The following summary table has been created, with all 49 Manufacturers listed in column X. The yellow cell, Y3, has been named fuel_type and currently contains the word diesel Question 17 Which formula has been used to calculate the how many cars there are, for the fuel type entered in the cell Y3, for each manufacturer? For example, ABARTH has zero diesel cars. Which formula has been placed in cell Y5 and copied down to Y53. A: = COUNTIFS($B$5:$B$3558,X5,$G$5:$G$3558,$Y3) B: = COUNTIFS($B$5:$B$3558,X5,$G$5:$G$3558,diesel) C: = COUNTIFS($B$5:$B$3558,X5,$G$5:$G$3558,diesel_fuel_type) D: = COUNTIFS($B$5:$B$3558,X5,$G$5:$G$3558,chosen_fuel_type) E: = COUNTIFS($B$5:$B$3558,X5,$G$5:$G$3558,fuel_type="diesel") F: = COUNTIFS($B$5:$B$3558,X5,$G$5:$G$3558,fuel_type=$Y$3) G: none of the above (5 marks) Question 18 Which formula has been used to calculate the average MPG for the fuel type entered in cell Y3, for each manufacturer? Which formula has been placed in cell Z5 and be copied down to Z53. Ensure that if there are no cars for that fuel type, the formula does not return a DIV/0! error. A: = IF(X5=0,"",AVERAGEIFS($H$5:$H$3558,$B$5:$B$3558,Y5,$G$5:$G$3558,fuel_type)) B: = IF(Y1>1,AVERAGEIFS($H$5:$H$3558,$B$5:$B$3558,X5,$G$5:$G$3558,fuel_type)) C: = IF(fuel_type="diesel",AVERAGEIF($B$5:$B$3558,X5,$H$5:$H$3558),"") D: = IF(fuel_type="diesel",AVERAGEIF($B$5:$B$3558,X5,$H$5:$H$3558),0) E: = IF(Y5=0,"",AVERAGEIFS($H$5:$H$3558,$B$5:$B$3558,X5,$G$5:$G$3558,fuel_type)) F: = IF(ISERROR(AVERAGEIFS($H$5:$H$3558,$B$5:$B$3558,X5,$G$5:$G$3558,fuel_type)), AVERAGEIFS($H$5:$H$3558,$B$5:$B$3558,X5,$G$5:$G$3558,fuel_type),"") G: none of the above (5 marks) 35 Question 19 If the following formula is placed in cell AA5, what will it produce? =IF(Y5>0,COUNTIFS(B5:B3558,X5,E5:E3558,"*"&"A"&"*",G5:G3558,fuel_type)/Y5,"") A: the percentage of Abarth cars that have an automatic transmission B: a blank cell C: a value of zero D: it produces a DIV/0! error E: the number of Abarth cars that have an automatic transmission F: the number of diesel-fuel Abarth cars that have an automatic transmission G: none of the above (5 marks) Question 20 If the following formula is placed in cell AB5, what will it produce? =INDIRECT(RIGHT(X8,1)&""&INDIRECT(LEFT(X5,1)&W5+4)+4) A: 1 B: count C: it produces a #VALUE! error D: 142 E: AUDI F: 151 G: none of the above (5 marks) 36 Part B Answers 11. B 12. F 13. E 14. B 15. B 16. E 17. G 18. E 19. B 20. D 37 SECTION C (25% of marks) – VBA Question 21 As always, the dataset has some bugs in it. A macro has been written to make the following 3 adjustments: Adjustment 1: All AUDI cars need their MPG reducing by 1.2 units. Adjustment 2: Any BMW car that is currently listed as having a “M6” or “M7” transmission code needs to be changed to “M7Z”. Adjustment 3: If the CO Emission value for any diesel car is currently below a value of 5 units it needs to be multiplied by a 1000. This is because some of the data has been entered as grams, and it needs to be converted into milligrams. A message box has also been added at the end of the macro to inform the user how many changes have been made. However, the macro has some mistakes in the code, which will cause the macro to either crash, or not perform the tasks correctly. Identify 8 separate mistakes and for each mistake: • Identify the line of code by number (1 – 31) • Describe the mistake • Provide the correct version of the code, or describe the solution to the mistake. (25 marks) 38 Solution: Line Problem Solution and/or correct version of the code 1 VBA Task not a valid sub name Should be VBA_task 2 For x = 1 to 3545 Should be For x = 1 to 3554 8 Cells(4 + x , 2).Value Should be Cells(4 + x, 8).Value 8 MPG + 1.2 Should be MPG – 1.2 13 If transmission = "M6" Or "M7" … If transmission = "M6" Or transmission = "M7" 22 counter3 = counter + 1 counter3 = counter3 + 1 20 CO Emission should be for diesel cars needs a diesel variable adding 21 Cell( Should be Cells( 28-30 Chr(10) position Chr(10) should be after the counters not before End of 2017 Exam 39 2018 Exam SECTION A (25% of marks) – Excel Functions and Formulas Use the dataset below for Sections A, B and C The dataset shows sales order information for four big retailers; Boots, Asda, Tesco and Independents. Each cell in the table is an ‘order’ for a number of units of a haircare product made by Elvive, over a 10- week period. For example, in week 1, Boots have submitted 2 orders; one for 60,000 units of 400ml shampoo and one for 50,000 units of 400ml conditioner. Each retailer has a code, e.g. Boots has the code B1, Asda is A1, and so on. [column C] There are 5 types of haircare product sold by Elvive Each product has a code, e.g. Shampoo 400ml is A, Conditioner 400ml is B, and so on. [column D] Each product also has a text description, e.g. “Elvive Shampoo 400ml” [column E] Orders for Boots in weeks 9 and 10 are based on provisional forecasts and thus may change in the future. These are marked with a * after the order amount, e.g. 44000* in week 9 for product A. Note: all data is shown in the screenshot above. Question 1 Suppose we want to know the total number of units ordered by Boots in week 3. Write the formula to calculate this. (2 marks) Question 2 Suppose we want to know the total number of units ordered by Boots and Tesco, for week 3. Write the single formula that would achieve this. (2 marks) Question 3 Suppose we want to know the smallest individual order size for Asda, for any week. Ignore weeks 1 and 2 as they obviously have no orders. Write the formula that would achieve this. (2 marks) 40 Question 4 Suppose we want to know the average number of units ordered by Asda, for all weeks. Again, ignore weeks 1 and 2 as they have no orders. Write the formula that would achieve this. (2 marks) Question 5 Suppose we want to know the average number of units of shampoo products ordered by Asda. Again, ignore weeks 1 and 2 as they have no orders. Write the formula that would achieve this. (2 marks) Question 6 What will this formula return? =SUMIF(D5:D24,D5,F5:F24) (2 marks) Question 7 What will this formula return? =INDEX(E5:E24,COUNTIF(D5:D24,D5)) (2 marks) Question 8 Suppose we want to know how many orders there are in the dataset. Not the total number of units but the number of cells in the table (F5:O24) that contain numerical values. Ignore the provisional figures in weeks 9 and 10 for Boots. Write the formula that would achieve this. (3 marks) Question 9 Suppose we want to know how many orders (not units) are between 11,000 and 20,000 products. If an order is exactly 11,000 or 20,000 then include these in the calculation. Write the formula that would achieve this. (3 marks) Question 10 Between Asda and Tesco, which retailer has ordered the most total shampoo units (400ml and 250ml)? Write the single formula that will produce this answer. (5 marks) 1. =SUM(H5:H9) 2. =SUM(H5:H9)+SUM(H15:H19) or =SUM(H5:H9,H15:H19) 3. =SMALL(H10:O14,1) or =MIN(H10:O14) 4. =AVERAGE(H10:O14) 5. =AVERAGE(H10:O10,H12:O12) 6. 60,000 7. Elvive Conditioner 250ml 8. =COUNTIF(F5:O24,">0") or =COUNT(F5:O24) 9. =COUNTIFS(F5:O24,">=11000",F5:O24,"<=20000") 10. =IF(SUM(H10:O10)+SUM(H12:O12)>SUM(H15:O15)+SUM(H17:O17),"Asda","Tesco") (4 marks) =IF(SUM(H10:O10)+SUM(H12:O12)=SUM(H15:O15)+SUM(H17:O17),"Tie", IF(SUM(H10:O10)+SUM(H12:O12)>SUM(H15:O15)+SUM(H17:O17),"Asda","Tesco")) (5 marks) 41 SECTION B (50% of marks) – Excel Model Development The following section is multiple choice. Submit one answer to each question. Two additional sets of calculations has been added to the worksheet. • in row 26, this shows the weekly totals of products ordered, across all four retailers • in column Q it shows the totals for each product, across all 10 weeks Note: The provisional orders for Boots in weeks 9 and 10 have been excluded from the totals Question 11 What will this formula return? =INDIRECT(D8&9) A: Elvive Serum 50ml B: D9 C: E9 D: DE E: Boots F: none of the above (5 marks) Question 12 What will this formula return? =INDEX(F5:O24,10,MATCH("C",D5:D9,0)) A: 5,040 B: 32000* C: 3 D: 3,200 E: #N/A! F: none of the above (5 marks) 42 Question 13 Which formula will calculate how many orders are provisional (the answer being 10)? A: = COUNT(NOT(ISNUMBER(F5:O24))) B: = COUNTIF(F5:O24,NOT(ISNUMBER(F5:O24))) C: = 200-COUNTIF(F5:O24,">0") D: = COUNTA(F5:O24)-COUNTIF(F5:O24,">0") E: = COUNTIF(F5:O24,*) F: none of the above (5 marks) Question 14 Which formula will calculate the biggest single order of 400ml shampoo product (the answer being 83,600)? A: = MAX(IF(E5:E24="Elvive Shampoo 400ml",F5:O24)) B: = LARGE(F5:O5,F10:O10,F15:O15,F20:O20) C: = MAX(F5:O5,F10:O10,F15:O15,F20:O20) D: = LARGE(F5:O24,MATCH("Elvive Shampoo 250ml",0)) E: = LARGE(F5:F24,1) F: none of the above (5 marks) Question 15 Which formula will determine which week saw the fewest total units ordered (ignoring week 2 which had zero products ordered)? A: = INDEX(F4:O4,MATCH(SMALL(F26:O26,1),F26:O26,0)) B: = INDEX(F4:O4,MATCH(MIN(F5:O24),F26:O26,0)) C: = COLUMN(MIN(F26,F28:O26)) D: = COLUMN(MATCH(SMALL(F26:O26,2),F26:O26,0)) E: = MATCH(SMALL(F26:O26,2),F26:O26,0)) F: none of the above (5 marks) 43 The following summary table has been constructed to summarise the orders across the whole 10-week period. Again, the provisional orders for Boots in weeks 9 and 10 have been excluded. Question 16 Which formula has been placed in cell V5 to calculate the number of orders for each retailer? This formula has been copied down to cell V8. A: = COUNTIFS($C$5:$C$24,T5,$F$5:$O$24,">0") B: = COUNTIF(INDIRECT(U5),>0) C: = COUNTIF("Boots"&">0",$F$5:$O$24) D: = COUNTIFS(ISNUMBER(INDIRECT(U5))) E: = COUNT(INDIRECT(U5)) F: none of the above (5 marks) Question 17 Which formula has been placed in cell W5 to calculate the total units ordered for each retailer? This formula has been copied down to cell W8. A: = SUMIFS($Q$5:$Q$24,$C$5:$C$24,$T$5) B: = COUNTIF(INDIRECT(U5),">0") C: = SUM(U5) D: = SUMIFS($Q$5:$Q$24,$C$5:$C$24,T5) E: = IF($C$5:$C$24=T5,SUM($F$5:$O$24,0) F: none of the above (5 marks) Question 18 Which formula has been placed in cell X5 to calculate the average order quantity for each retailer? This formula has been copied down to cell X8. A: = $W5*(1/$V5) B: = AVERAGEIF(INDIRECT(U5)) C: = AVERAGEINDIRECT(U5) D: = SUMIFS($Q$5:$Q$24,$C$5:$C$24,T5)/(COUNT(INDIRECT(U5)) E: = W5/SUM($W$5:$W$8) F: none of the above (5 marks) 44 Two more summary tables have been constructed to summarise the total units ordered for each week, for the four retailers, as a total number (Table 2) and a percentage of the total for that week (Table 3). Again, the provisional orders for Boots in weeks 9 and 10 have been excluded. For example, in week 1 Boots have 110,000 total units ordered, which is 100% of the week 1 orders. In week 3, Boots have 142,000 total products ordered, which is 47% of the total for week 2 (303,200). Note: when the number of products ordered in Table 2 is zero, e.g. Asda in week 1, the corresponding percentage in Table 3 returns a blank cell, rather than “0%”. Question 19 Which formula has been placed in cell AA5 to calculate the total products ordered for each week, for each retailer? This formula has been copied down and across to cell AJ8, to complete the table. A: = SUMIF(C5:C24,LEFT(Z5,1)&1,F5:F24) B: = SUMIF(C5:C24,LEFT(Z5,1)AND(1),F5:F24) C: = SUMIF(C$5:C$24,LEFT($Z5,1)&1,F$5:F$24) D: = SUMIF($C$5:$C$24,LEFT($Z5,1)&1,F$5:F$24) E: = SUMIF($C$5:$C$24,LEFT($Z5,1)&1,$F$5:$F$24) F: = SUMIF(C$5:C$24,LEFT($Z5,1)&1,$F$5:$F$24) G: none of the above (5 marks) Question 20 Which formula has been placed in cell AA12 to calculate the percentage for that week, for each retailer? If this percentage is zero then leave the cell blank. This formula has been copied down and across to cell AJ15, to complete the table. A: = AA5/F$26 B: = IF(AND(ISNUMBER(AA5),AA5>0),AA5/SUM(AA$5:$AA$8),"") C: = IF(AA5>0,AA5/$W5,"") D: = IF(AA5=0,"",AA5/SUM(AA$5:AA$8) E: = IF(AA5>0,AA5/SUM(AA$5:AA$8),"") F: = IF(AA5=0,ISBLANK(AA12),AA5/SUM(AA$5:AA$8)) G: none of the above (5 marks) 45 Part B Answers 10. F 11. D 12. D 13. C 14. E 15. E 16. D 17. A 18. D 19. E 46 SECTION C (25% of marks) – VBA Question 21 As always, the dataset has some bugs in it. A macro has been written to make the following 2 adjustments: Adjustment 1: The orders for 400ml and 250ml shampoo need to swap places. For example, in week 3 at Boots the 400ml order should be 32,000 and the 250ml order should be 40,000. This applies to all 10 weeks. Adjustment 2: All the Tesco units for weeks 6, 7 and 8 need to be reduced by 5% and increased by 5% for weeks 9 and 10. Ensure the final order number is an integer value. A message box has also been added at the end of the macro to inform the user what the net change in the Tesco units is, as a result of adjustment 2. [the net change is the additional units minus the reduction in units]. However, the macro has some mistakes in the code, which will cause the macro to either crash, or not perform the tasks correctly. Identify 6 separate mistakes and for each ‘mistake’: • Identify the line (or lines) of code by number (1 – 31) • Describe the mistake • Provide the correct version of the code, or describe the solution to the mistake Note: a line may contain more than one mistake. Note: only the first six mistakes presented will be marked. Any other mistakes presented will be ignored. (25 marks) 47 Line Problem Solution and/or correct version of the code 9 = shampoo400ml = shampoo250ml 10 = shampoo250ml = shampoo400ml 13 retialer_code = "T" retialer_code = "T1" 20 tesco_value * 0.05 tesco_value * 1.05 26 Next x Next k 27 Next k Next x 29 Exit Sub End Sub End of 2018 Exam 48 2019 Exam SECTION A (25% of marks) – Excel Functions and Formulas The dataset you will analyse is sales data for 7 products, over a period of one calendar year. The 7 product codes are as follows: A-18/C4, A-18/C5, A-18/C6, A-18/C7, A-18/C8, B-18/C9, B-18/C10 Each month’s sales data is held on their own, separate sheet. The structure of the data is identical on each sheet except that some months have more days than others. The 12 sheet names are as follows: Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov and Dec Below is a screenshot of the sheet for February’s sales. Note: day 11 through to day 24 have been hidden in the screenshot above, for presentation purposes. Column A contains the day of the month number: 1 to 28 for this month, as there are 28 days in February. Column B contains the day of the year number: January has 31 days, so February is 32 through to 59. Column C denotes whether the day is a weekend day: 1 = Saturday or Sunday. All other weekdays are 0. Columns D through to J are the number of units sold for each of the 7 products. For example, on day 1 of February, 65 units of product A-18/C4 were sold. On day 2, 74, and so on. Additional calculations include: • total monthly sales for each product, in cells D2:J2 • average monthly sales for each product, in cells D3:J3 • maximum number of units sold that month, for each product, in cells D4:J4 • total sales for each day, across all 7 products, in column L [e.g. day 1 is 653 units] • total sales for the whole month, in cell L2 [20,002 units] • average daily sales, across all 7 products, in cell L3 [714.36 units per day] • maximum daily sales, across all 7 products, in cell L4 [829 on day 26] 49 All Part A questions only refer to the data for February Question 1 What formula is in cell L7, to calculate the total number of units sold for day 1? (2 marks) Question 2 What formula is in cell D2, to calculate the total number of units sold for product A-18/C4? (2 marks) Question 3 What formula is in cell D3, to calculate the average number of units sold for product A-18/C4? (2 marks) Question 4 What formula is in cell D4, to calculate the maximum number of units sold for product A-18/C4? (2 marks) Question 5 Suppose we want to know how many units (of all products) have been sold this month on just weekend days (Saturday and Sunday). Write the formula that would achieve this. (2 marks) Question 6 What will this formula return? =INDEX(D6:J6,MATCH(MAX(D2:J2),D2:J2,0)) (2 marks) Question 7 Suppose we want to know how many units (of all products) have been sold in total on the first Monday of February. Write the formula that would achieve this. (2 marks) Question 8 What will this formula return? =INDIRECT(RIGHT(G6,2)) (2 marks) Question 9 Suppose we want to know whether the day with the highest sales in February was a weekend day or a weekday. Write the formula that would tell you this. (4 marks) Question 10 Suppose we want to know the total units sold (of all products) on Saturdays. Write the formula that would achieve this. (5 marks) Part A Answers 1. =SUM(D7:J7) 2. =SUM(D7:D34) 3. =AVERAGE(D7:D34) 4. =MAX(D7:D34) 5. =SUMIF(C7:C34,1,L7:L34) 6. A-18/C5 7. =L12 8. 0 9. =IF(INDEX(C7:C34,MATCH(L4,L7:L34,0))=1,"weekend","weekday") 10. =L10+L17+L24+L31 50 SECTION B (50% of marks) – Excel Model Development The following section is multiple choice. Submit one answer to each question. The following summary table has been constructed on a sheet called Summary to present the number of units sold for each product, for every month of the year. In addition to this summary table, a Data Address Matrix has been created on the same Summary sheet to store the addresses for the each month’s sales data, for each of the 7 products: 51 Question 11 Cell F7 calculates the total units sold for the A-18/C4 product in January. Which formula could be placed in cell F7 and be copied across and down to cell L18, to complete the summary table for all 7 products, for all 12 months? A: = Jan!$D$2 B: = MONTH($D$2) C: = SUM(INDIRECT(Q7)) D: = INDIRECT(D7&$D$2) E: = INDIRECT(Q7) F: = INDIRECT(SUM(Q7)) G: none of the above (5 marks) Question 12 Suppose we want to know the total sales for all products, for the months with 31 days. Which single formula could achieve this? A: = SUMIF(E7:E18,31,N17:N18) B: = COUNTIF(E7:E18,31) C: = SUMIF(N7,N9,N11,N13,N14,N16,N18) D: = N7+N9+N11+N13+N14+N16+N18 E: = SUMIF(D7:D18,OR("Jan","Mar","May","Jul","Aug","Oct","Dec"),N7:N18) F: = SUM(MONTHDAYS(31)) G: none of the above (5 marks) Question 13 Suppose we want to know the total sales for all products for the six-month period from March through to August. Which single formula could achieve this? A: = SUMIF(C7:C18,AND(">=3","<=8"),N7:N18) B: = SUM(F9:N14) C: = SUMIF(D7:D18,OR("Mar","Apr","May","Jun","Jul","Aug"),N7:N18) D: = SUMIF(C7:C18,">2",N7:N18)-SUMIF(C7:C18,">8",N7:N18) E: = SUM(N7:N18) F: = SUMMONTHS(C7:C18,3-8,N7:N18) G: none of the above (5 marks) Question 14 What will this formula return? = INDIRECT(LEFT(D8,1)&INDIRECT(RIGHT(K6,2))-1) A: #REF! B: 233015 C: a blank cell D: 34661 E: 35708 F: 35709 G: none of the above (5 marks) 52 Question 15 Cell Q7 produces the data address text string for the A-18/C4 product in January. Which formula could be placed in cell Q7 and be copied across and down to cell W18, to complete the data address matrix for all 7 products, for all 12 months? A: = $D7&"!"&Q$7&"7:"&Q$6&$E7+6 B: = $D7&"!"&Q$6&"7:"&Q$6&$E7+6 C: = D$7&"!"&Q$6&"7:"&Q$6&E7+6 D: = $D7&"!"&Q$6&"7:"&Q6&$E7+6 E: = $D7&"!"&Q$6&"7:"&$Q$6&$E7+6 F: = $D7&"!"&$Q$6&"7:"&Q$6&$E7+6 G: none of the above (5 marks) Question 16 What will this formula return? = MAX(INDIRECT(Q8))-AVERAGE(INDIRECT(R8)) A: #REF! B: 1242.75 C: 0 D: 90 E: 28.11 F: -77.36 G: none of the above (5 marks) 53 An additional column O has been added to the summary table, to extract the data for a dynamic chart, based on a numerical choice inputted by the user in cell O4. Cell O4 has been named chart_choice and currently contains a 1, and thus will extract the data for the 1st of the seven products (A-18/C4). Cell J4 contains the text that will be used for the chart’s dynamic title. Question 17 What is the formula in cell O7 that has been copied down to cell O18, to extract the chosen data based on the value in the chart_choice cell (O4)? A: = INDEX(F7:L18,chart_choice) B: = INDEX(F6:L6,chart_choice) C: = INDEX(F7:L7,"chart_choice") D: = IF(chart_choice=1,F7,IF(chart_choice=2,G7,IF(chart_choice=3,H7,IF(chart_choice=4,I7, IF(chart_choice=5,J7,IF(chart_choice=6,K7,L7))))) E: = INDEX(F7:L7,chart_choose) F: = INDEX(F7:L7,O4) G: none of the above (5 marks) Question 18 What is the formula in cell J4, to create a dynamic title for the chart? A: = "Monthly sales for "&F6 B: = "Monthly sales for "&INDEX(F6:L6,chart_choice) C: = "Monthly sales for "& chart_choice D: Monthly sales for A-18/C4 E: = "Monthly sales for "AND(chart_choice) F: = "Monthly sales for A-18/C4" G: none of the above (5 marks) 54 A macro has been created which compiles all 12 months data onto a single data sheet, called All Data! A screenshot of this compiled datastack is given below. Note it only shows the first 5 days and the last 5 days of the year, just for presentation purposes. Question 19 Suppose we wish to know, for each product, which day of the year saw the most units sold. What is the formula in cell E4 that has been copied across to cell K4? A: =INDEX(B7:B371,MATCH(MAX(E7:E371),E7:E371,0))&"-" &INDEX(A7:A371,MATCH(MAX(E7:$E371),E7:$371,0)) B: =INDEX($B$7:$B$371,MAX(E7:E371))&"-"&INDEX($A$7:$A$371,MAX(E7:E371)) C: =INDEX($B7:$B371,MAX(E7:E371))&"-"&INDEX($A7:$A371,MAX(E7:E371)) D: =INDEX($B7:$B371&"-"&$A7:$A371,MATCH(MAX(E7:E371),E7:E371,0)) E: =FIND(MAX(E7:E371),$B7:$B371&"-"&$A7:$A371) F: =INDEX($B7:$B371,MATCH(MAX(E7:E371),E7:E371,0))&"-" &INDEX($A7:$A371,MATCH(MAX(E7:E371),E7:E371,0)) G: none of the above (5 marks) Question 20 Suppose we wish to know, for each product, how many weekend days saw sales greater than 50% higher than the annual average. What is the formula in cell E5 that has been copied across to cell K5? A: =COUNTIF(E7:E371,">"&E3+E3/2) B: =COUNTIF(E7:E371,E3+E3/2) C: =COUNTIFS($D7:$D371,1,E7:E371,">"&E3*1.5) D: =COUNTIFS($D7:$D371,1,E7:E371,">"&E3+50%) E: =COUNTIFS($D7:$D371,1,E7:E371,1.5*AVERAGE(E7:E371)) F: =COUNTIFS($D7:$D371,1,$E7:$E371,">"&1.5*E3) G: none of the above (5 marks) 55 Part B Answers 11. C 12. D 13. D 14. F 15. B 16. F 17. G 18. B 19. F 20. C 56 SECTION C (25% of marks) – VBA Question 21 As mentioned previously, a macro has been created to compile all 12 months data onto a single data sheet called All Data! The macro is presented below. The macro copies and pastes each month’s sales data onto the All Data sheet, and the month name is added to column A to create the datastack shown on the previous page. A message box is shown at the end to inform the user of a successful compilation. However, the macro has some mistakes in the code, which will cause the macro to either crash, or not compile the data correctly. Identify 5 separate mistakes and for each ‘mistake’: • Identify the line of code by number (1 – 24) • Describe the mistake • Provide the correct version of the code, or describe the solution to the mistake Note: a line may contain more than one mistake. Note: only the first 5 mistakes presented will be marked. Any other mistakes presented will be ignored. (25 marks) 57 End of Exam 5 bugs/mistakes out of a possible 6: line number error correct line 7 "Summary!" "Summary" line 8 , 3 , 4 line 9 , 4 , 5 line 13 “All Data” “All Data!” line 14 , 1 , 2 line 19 runingtotal runningtotal 58 2020 Exam The summary table below is based on the workshop – Modelling Patient Flows. It contains two summary tables: on the left is the number of hospital episodes for a selected SpecAd (specad 4 in the screenshot below) for 28 Districts and 5 Hospitals, and on the right is a summary table for the cost of treatment. Component Description Cell Name of cell SpecAd number a number from 1 to 101, as there are 101 different SpecAds G2 specad_num SpecAd the name of the SpecAd. This text combines two things; a Speciality (there are 37) and the admission type (there are 3) G3 specad SpecAd cost The expected cost for each episode of a SpecAd. G4 specad_cost Admission type There are three types of admission; day case (DC), inpatient elective (IPE) and inpatient nonelective (IPNE) District Where the patient lives. There are 28 districts. C11:C38 Hospital Where the patient was treated. There are 5 hospitals; Altnagelvin, Antrim, Belfast City, Causeway and Craigavon D10:H10 Note 1: Average episodes for each hospital, for the selected SpecAd, are given in cells D6:H6 Note 2: Total episodes for each hospital, for the selected SpecAd, are given in cells D7:H7 Note 2: Average and total costs for each hospital, are given in cells J6:N6 and J7:N7 respectively Note 3: Total costs for each District are given in cells P11:P38 59 Question 1 What formula is in cell E7, to calculate the total episodes for Antrim hospital? (2 marks) Question 2 What formula is in cell E6, to calculate the average episodes for Antrim hospital? (2 marks) Question 3 What formula is in cell P11, to calculate the total costs for Antrim district? (2 marks) Question 4 What formula is in cell K11, to calculate the cost of patients from Antrim district going to Antrim hospital? (2 marks) Question 5 What formula is in cell P7, to calculate the total costs for this Specad? (2 marks) Question 6 Suppose we want to know the total episodes for Antrim district (the answer being 56)? Write the formula that would achieve this. (2 marks) Question 7 For the current Specad, the number of individual districts that supply patients to Altnagelvin hospital is 8 (out of the 38). Write the formula that would achieve this for any given SpecAd. (2 marks) Question 8 For the current Specad, the highest number of patients in the episode summary table is 159 (cell H23). These are patients travelling from Craigavon district to the Craigavon hospital. Write the formula that would find the highest value for any given SpecAd. (2 marks) Question 9 For the current Specad, the district that supplies the highest number of episodes to Belfast City hospital is the SE Belfast district with 100 episodes. Write the single formula that would determine which District supplies the highest number of episodes to Belfast City hospital, for any given SpecAd. (4 marks) Question 10 Suppose we want to calculate the average number of episodes per hospital, across all 5 hospitals, ignoring all zeros. Write the single formula that would produce this for any given SpecAd. (5 marks) Part A Answers 1. =SUM(E11:E38) 2. =AVERAGE(E11:E38) 3. =SUM(J11:N11) 4. =E11*specad_cost or =E11*$G$4 5. =SUM(J7:N7) or =SUM(P11:P38) 6. =SUM(D11:H11) 7. =COUNTIF(D11:D38,">0") 8. =MAX(D11:H38) 9. =INDEX(C11:C38,MATCH(MAX(F11:F38),F11:F38,0)) 10. =AVERAGEIF(D11:H38,">0") or =SUM(D11:H38)/COUNTIF(D11:H38,">0") 60 SECTION B (50% of marks) – Excel Model Development The following section is multiple choice. Submit one answer to each question. The following summary table has been created to summarise the episodes for each hospital. The current SpecAd is 4. Question 11 Column AD presents the total episodes for each hospital. Which formula is in cell AD11, that has been copied down to cell AD15 to produce all five totals? A: =D$7 B: =SUM(D$11:D$38) C: =SUM(AC11) D: =INDEX($D$7:$H$7,Z11) E: =SPECAD(AC11) F: none of the above (5 marks) Question 12 Column AE presents the number of Districts with a non-zero number of episodes, for each hospital. Which formula is in cell AE11, that has been copied down to cell AE15 to produce all five values? A: =COUNTIF(INDIRECT(AC11),">=0") B: =COUNT(INDIRECT(AC11)) C: =COUNTIF(INDIRECT(AB11),"<>0") D: =COUNTIF(INDIRECT(AC11),">0") E: =SUM(INDIRECT(AC11),">0") F: none of the above (5 marks) Question 13 Column AF presents the number of Districts with zero episodes, for each hospital. Which formula is in cell AF11, that has been copied down to cell AF15 to produce all five values? A: =COUNTIF(INDIRECT(AE11),0) B: =COUNT(D$11:D$38)-AE11 C: =28-AE11 D: =COUNTIF(AC11,"0") E: =ZEROCOUNT(INDIRECT(AE11)) F: none of the above (5 marks) 61 Question 14 Column AC presents a text string for the data address, for each hospital. For example, the data for Alnagelvin hospital can be found in cells D11:D38. Which formula is in cell AC11, that has been copied down to cell AC15 to produce all five data addresses? A: =AB11&11&":"&AB11&"38 B: =AB11&11&":"&AB11&38 C: =AB11&"11&":"&AB11&38 D: =AB11&11&":"&AD11&38 E: ="D11:D38" F: none of the above (5 marks) Question 15 Column AG presents the average number of episodes for each hospital, including the zeros. Which formula is in cell AG11, that has been copied down to cell AG15 to produce all five averages? A: =INDEX(D6:H6,1) B: =AVERAGEIF(AC11,NOZEROS) C: =AVERAGE(D11:D38) D: =INDEX(D6:H6,MATCH(AA11,D10:H10,0)) E: =AVERAGE(OFFSET($C$11,0,Z11,28)) F: none of the above (5 marks) Question 16 Column AH presents the average number of episodes for each hospital, excluding the zeros. Which formula is in cell AH11, that has been copied down to cell AH15 to produce all five averages? A: =AE11/AF11 B: =AD11/AF11 C: =AD$11/AE$11 D: =AE11/AE11 E: =INDEX($D$7:$H$7,$Z11)/$AE11 F: none of the above (5 marks) Question 17 What will the following formula return? =AD11/INDIRECT(LEFT(AC11,1)&AE12-specad_num-1) A: 0 B: 308 C: 12 D: a blank cell E: an error F: S G: none of the above (5 marks) 62 Question 18 What will the following formula return? =COUNTIF(F11:F38,MATCH(LARGE(D11:D38,2),D11:D38,0)) A: 0 B: 1 C: 2 D: 60 E: 29 F: 19 G: none of the above (5 marks) Inputting a value of 7 in the SpecAd number cell (cell G2) produces the following changes to the episode summary table, as there are zero episodes for all five hospitals for this particular SpecAd. Question 19 Column AH now presents a #DIV/0! error, as there is no data to calculate the average. In such circumstances, if we wish to replace this #DIV/0! error with a blank cell instead, which formula should we place in cell AH11, and copy down to cell AH15? A: =IF(AE11<>0,AE11/AD11,"") B: =IF(AD11<1,"",AD11/AE11) C: =IF(AD11<>"0",AD11/AE11,"") D: ="" E: =IF(ISERROR(AD11/AE11),ISBLANK(AH11),AD11/AE11) F: =IF(ISERROR(AE11/AD11),"",AE11/DE11) G: none of the above (5 marks) Question 20 Column AA presents the names of the hospitals. Which formula is in cell AA11, that has been copied down to cell AA15? A: =INDEX($D$10:$H$10,Z10) B: =INDEX($D$10:$H$11,Z11) C: =INDEX($D$10:$H$10,ROW(Z1)) D: =INDEX($D$10:$H$10,ROW(Z11)) E: =INDEX($J$10:$N$10,Z11) F: =TRANSPOSE(D10:H10) G: none of the above (5 marks) 63 Part B Answers 11. D 12. D 13. B 14. B 15. E 16. E 17. G 18. B 19. B 20. C 64 SECTION C (25% of marks) – VBA Question 21 A macro has been created to produce a new summary table showing the number of episodes for each SpecAd, for each hospital – see below. The new summary table also contains some adjustments made to the data, as some of the original episode counts needed to be altered. Note: SpecAds 11 to 90 are hidden in the screenshot above The macro also makes the following 3 adjustments to the number of episodes, in the following way: 1. The episodes for SpecAd 10 (DERMIPNE) have been doubled, for Alnagelvin hospital, from 25 to 50 2. The episodes for SpecAds 4, 5 and 6 have all been increased by 20, for Causeway hospital 3. Any SpecAd at any hospital with less than 5 episodes has been adjusted to now show zero episodes The code for a message box has also been placed at the end of the macro to inform the user how many cells in the summary table have been changed as a result of adjustment 3. However, the macro presented below will not produce the desired results as it has some mistakes in the code, which will cause the macro to either crash, or not produce the correct result. 65 Identify 5 separate mistakes and for each ‘mistake’: • Identify the line of code by number (1 – 32) • Briefly describe the mistake • Provide the correct version of the code, or describe the solution to the mistake Note 1: a line may contain more than one mistake. Note 2: only the first 5 mistakes presented will be marked. Any other mistakes presented will be ignored. (25 marks) Line Error Solution 5 x = 1 to 100 x = 1 to 101 8 Application.ScreeUpdating Application.StatusBar 16 specadnum <7 Or .. specadnum <7 And .. 17 Cells(6,7) Cells(7,6) 20 testvalue < 5 to get the correct count you need: testvalue < 5 and testvalue >0 66 Answer sheet to upload to moodle Student ID: A Formula: Q1. Q2. Q3. Q4. Q5. Q6. Q7. Q8. Q9. Q10. Q11. Q12. B Answer (letter): Answer (letter): Q13. Q18. Q14. Q19. Q15. Q20. Q16. Q21. Q17. Q22. Formula: Q23. Q24a. Q24b. C Line no. Code with the error Correct code, or description of the solution 1 2 3 4 5
欢迎咨询51作业君
