(Declarati eneral) pption Explicit On the "Sheetl" worksheet, you should see a set of numbers (values) in "range Al:G15. For this problem, you need to create a macro that will 'perform the following tasks: 1 1 1. Activate "Sheetl" worksheet. 2. Use the worksheet function Min() to find the minimum number (value) in range Al:G15. Store this minimum value in a variable. You can name this variable MinValue or any other name that you like. 3. Using the For-Each loop, your program should perform a loop to find the first cell within range Al:G15 that contains that minimum value. That is, inside the loop, your program should check if the current cell's value is the same as the MinValue. If it is, using the MsgBox function, display to the user the cell address that contains that minimum number. You can use the Address property of a Range object to display the address of a cell. 4. As soon as your program finds the cell address that contains the minimum value, your program should exit the loop. 5. End the Sub. 1 1 Your macro should start below this line ***** General) (Declarations) pption Explicit "Create a user-defined function that will compute Break-Even Point in units. 'The function will require three inputs: Fixed Costs, Unit Price and Variable Cost per Unit 'The function output will be Break-Even Point in units calculated as: Break-Even Point (in units) = Fixed Costs/ (Unit Price - Variable Cost) "Then, write a macro that does the following: "1. Ask the user for three inputs (Fixed Costs, Unit Price, Variable Costs) "2. Check that the user entered numeric values. If the values are numeric, use CDbi() to convert the values. Otherwise, you should exit the macro. 3. Call the function to get the Break-Even Point. 4. Post the result of the function using a MsgBox. Example: ' if you pass $50,000 as a fixed costs, $0.60 as variable cost per unit, and $3 as unit price I to your user-defined function, it should return 20,834 units (rounded up) Optional: Use ROUNDUP() Excel Function. For example ROUNDUP (20833,0) Syntax: ROUNDUP (number, num_digits) where number is the number to be rounded up. num_digits is number of digits that the number will be rounded up to 'The msgbox in the macro output would look like: The Break-Even Point is 20,834 units Your macro should start below this line ***** Your function should start below this line B F H A 0.985611 0.112515 1 G 0.954531 0.564491 E 0.032962 0.426887 0.383169 0.463244 2 0.722576 0.103819 0.038902 0.036456 0.829887 0.381468 0.253538 0.026087 0.829238 0.961003 0.051528 3 4 5 6 7 0.976329 0.286175 0.153705 0.440628 0.647095 0.047848 0.144558 0.880757 0.787982 0.525614 0.066120 0.751075 0.234081 0.143044 0.317890 0.575403 0.459530 0.895214 0.760352 0.642074 0.703654 0.149285 0.080191 0.795616 0.982999 0.480265 0.462663 0.496118 0.781802 0.153188 0.070168 0.314860 0.100847 0.719807 0.651689 0.275167 0.302873 0.384505 8 9 10 11 0.162230 0.113741 0.730820 0.355364 0.991806 0.180733 0.046512 0.493917 0.876362 0.139271 0.707672 0.010596 0.002635 0.580816 0.086940 0.727323 0.594334 0.106726 0.596190 0.946405 0.935668 0.111120 0.638107 0.178900 0.446090 0.659988 0.142203 0.446737 0.453296 0.046451 0.074015 0.454127 0.798167 0.478211 0.648759 0.265254 0.750189 0.872253 0.168591 0.691904 0.543068 0.662805 0.163300 0.070160 0.901211 0.391034 0.377375 0.081939 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 Start_Here Sheet1 Problem3 C25 X for =C21 K L A B D E F G H 1 Part1 (scroll down for part 2) 2 The table below shows the quarterly sales for years 1995 to 2015. 3 Using Data Validation, insert a validation rule on Quarter of Year column so that the value can only represent the four quarter numbers. Then using Conditional Formatting, add rules that highlight each row in a different color based on quarter number. 5 For example, row with quarter of year = 4 would be in red, row with quarter of year = 3 would be in yellow, row with quarter of year = 2 would be in blue, 6 and row with quarter of year = 1 would be in green. 4 7 8 9 10 11 Year 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 Quarter of Year Sales (in million $) 1995 4 0.511 1996 1 0.875 1996 2 2.23 1996 3 4.173 1996 4 8.468 1997 1 16.005 1997 2 27.855 1997 3 37.887 1997 4 66.04 1998 1 87.361 1998 2 115.982 1998 3 153.649 1998 4 252.893 1999 1 293.643 1999 2 314.376 1999 3 355.778 1999 4 676.042 2000 1 573.889 2000 2 577.876 2000 3 637.858 2000 4 972.36 2001 1 700.356 2001 2 667.625 2001 3 639.281 2001 4 1115.171 28 29 30 31 32 33 34 35 36 Start_Here Sheet1 Problem3 File Home Insert Page Layout Formulas Data Review View Develop Record Macro Use Relative References A Macro Security Fope N Properties Visual Macros Basic Properties Da View Code Insert Design Mode El Run Dialog Add- ins 1870 Excel COM Add-ins Add-ins Code Add-ins Controls C25 X for =C21 Year E 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 Quarter of Year Sales (in million $) 2002 1 847.422 2002 2 805.605 2002 3 851.299 2002 4 1428.61 2003 1 1083.559 2003 2 1099.912 2003 3 1134.456 2003 4 1945.772 2004 1 1530.349 2004 2 1387.341 2004 3 1462.475 2004 4 2540.959 2005 1 1902 2005 2 1753 2005 3 1858 2005 4 2977 2006 1 2279 2006 2 2139 2006 3 2307 2006 4 3986 2007 1 3015 2007 2 2886 2007 3 3262 2007 4 5672 2008 1 4135 2008 2 4063 2008 3 4265 2008 4 6703 2009 1 4889 2009 2 4652 2009 3 5448 2009 4 9520 2010 1 7131 2010 2 6566 2010 3 7560 2010 4 12948 62 63 64 65 66 67 68 69 70 71 72 Start_Here Sheet1 Problem3 + 98 1. A two-way table that shows for each hospital the number of cases based on outcome 99 2. Replicate the pivot table to show the number of cases based on outcome as a percentage of total cases in each hospital. 100 101 102 103 104 Place your pivot tables below 105 106 107 108 Hospital 109 ER 110 ER 111 ER 112 ER 113 ER 114 ER 115 ER 116 ER 117 ER 118 ER 119 ER 120 ER 121 ER 122 ER 123 ER 124 ER 125 ER 126 ER 127 ER 128 ER 129 ER 130 ER 131 ER 132 ER 133 ER Risk Categon Outcome High Die High Die High Die High Die High Die High Die High Die High Die High Die High Die High Die High Die High Die High Die High Die High Die High Die High Die High Die High Die High Die High Die High Die High Die High Die Start_Here Sheet1 Problem + 3 N 3 4 N 3 A Year Quarter of Year Sales (in million $) E F G H 73 2011 1 9857 74 2011 2 9913 75 2011 3 10876 76 2011 4 17431 77 2012 1 13185 78 2012 12834 79 2012 3 13806 80 2012 4 21268 81 2013 1 16070 82 2013 15704 83 2013 3 17092 84 2013 4 25586 85 2014 1 19741 86 2014 19340 87 2014 3 20579 88 2014 4 29328 89 2015 1 22717 90 2015 2 23185 91 2015 3 24815 92 2015 4 34283 93 94 95 96 Part 2- Pivot Table 97 Create two pivot tables based on the below table. 98 1. A two-way table that shows for each hospital the number of cases based on outcome 99 2. Replicate the pivot table to show the number of cases based on outcome as a percentage of total cases in each hospital. 100 3 3 101 (Declarati eneral) pption Explicit On the "Sheetl" worksheet, you should see a set of numbers (values) in "range Al:G15. For this problem, you need to create a macro that will 'perform the following tasks: 1 1 1. Activate "Sheetl" worksheet. 2. Use the worksheet function Min() to find the minimum number (value) in range Al:G15. Store this minimum value in a variable. You can name this variable MinValue or any other name that you like. 3. Using the For-Each loop, your program should perform a loop to find the first cell within range Al:G15 that contains that minimum value. That is, inside the loop, your program should check if the current cell's value is the same as the MinValue. If it is, using the MsgBox function, display to the user the cell address that contains that minimum number. You can use the Address property of a Range object to display the address of a cell. 4. As soon as your program finds the cell address that contains the minimum value, your program should exit the loop. 5. End the Sub. 1 1 Your macro should start below this line ***** General) (Declarations) pption Explicit "Create a user-defined function that will compute Break-Even Point in units. 'The function will require three inputs: Fixed Costs, Unit Price and Variable Cost per Unit 'The function output will be Break-Even Point in units calculated as: Break-Even Point (in units) = Fixed Costs/ (Unit Price - Variable Cost) "Then, write a macro that does the following: "1. Ask the user for three inputs (Fixed Costs, Unit Price, Variable Costs) "2. Check that the user entered numeric values. If the values are numeric, use CDbi() to convert the values. Otherwise, you should exit the macro. 3. Call the function to get the Break-Even Point. 4. Post the result of the function using a MsgBox. Example: ' if you pass $50,000 as a fixed costs, $0.60 as variable cost per unit, and $3 as unit price I to your user-defined function, it should return 20,834 units (rounded up) Optional: Use ROUNDUP() Excel Function. For example ROUNDUP (20833,0) Syntax: ROUNDUP (number, num_digits) where number is the number to be rounded up. num_digits is number of digits that the number will be rounded up to 'The msgbox in the macro output would look like: The Break-Even Point is 20,834 units Your macro should start below this line ***** Your function should start below this line B F H A 0.985611 0.112515 1 G 0.954531 0.564491 E 0.032962 0.426887 0.383169 0.463244 2 0.722576 0.103819 0.038902 0.036456 0.829887 0.381468 0.253538 0.026087 0.829238 0.961003 0.051528 3 4 5 6 7 0.976329 0.286175 0.153705 0.440628 0.647095 0.047848 0.144558 0.880757 0.787982 0.525614 0.066120 0.751075 0.234081 0.143044 0.317890 0.575403 0.459530 0.895214 0.760352 0.642074 0.703654 0.149285 0.080191 0.795616 0.982999 0.480265 0.462663 0.496118 0.781802 0.153188 0.070168 0.314860 0.100847 0.719807 0.651689 0.275167 0.302873 0.384505 8 9 10 11 0.162230 0.113741 0.730820 0.355364 0.991806 0.180733 0.046512 0.493917 0.876362 0.139271 0.707672 0.010596 0.002635 0.580816 0.086940 0.727323 0.594334 0.106726 0.596190 0.946405 0.935668 0.111120 0.638107 0.178900 0.446090 0.659988 0.142203 0.446737 0.453296 0.046451 0.074015 0.454127 0.798167 0.478211 0.648759 0.265254 0.750189 0.872253 0.168591 0.691904 0.543068 0.662805 0.163300 0.070160 0.901211 0.391034 0.377375 0.081939 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 Start_Here Sheet1 Problem3 C25 X for =C21 K L A B D E F G H 1 Part1 (scroll down for part 2) 2 The table below shows the quarterly sales for years 1995 to 2015. 3 Using Data Validation, insert a validation rule on Quarter of Year column so that the value can only represent the four quarter numbers. Then using Conditional Formatting, add rules that highlight each row in a different color based on quarter number. 5 For example, row with quarter of year = 4 would be in red, row with quarter of year = 3 would be in yellow, row with quarter of year = 2 would be in blue, 6 and row with quarter of year = 1 would be in green. 4 7 8 9 10 11 Year 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 Quarter of Year Sales (in million $) 1995 4 0.511 1996 1 0.875 1996 2 2.23 1996 3 4.173 1996 4 8.468 1997 1 16.005 1997 2 27.855 1997 3 37.887 1997 4 66.04 1998 1 87.361 1998 2 115.982 1998 3 153.649 1998 4 252.893 1999 1 293.643 1999 2 314.376 1999 3 355.778 1999 4 676.042 2000 1 573.889 2000 2 577.876 2000 3 637.858 2000 4 972.36 2001 1 700.356 2001 2 667.625 2001 3 639.281 2001 4 1115.171 28 29 30 31 32 33 34 35 36 Start_Here Sheet1 Problem3 File Home Insert Page Layout Formulas Data Review View Develop Record Macro Use Relative References A Macro Security Fope N Properties Visual Macros Basic Properties Da View Code Insert Design Mode El Run Dialog Add- ins 1870 Excel COM Add-ins Add-ins Code Add-ins Controls C25 X for =C21 Year E 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 Quarter of Year Sales (in million $) 2002 1 847.422 2002 2 805.605 2002 3 851.299 2002 4 1428.61 2003 1 1083.559 2003 2 1099.912 2003 3 1134.456 2003 4 1945.772 2004 1 1530.349 2004 2 1387.341 2004 3 1462.475 2004 4 2540.959 2005 1 1902 2005 2 1753 2005 3 1858 2005 4 2977 2006 1 2279 2006 2 2139 2006 3 2307 2006 4 3986 2007 1 3015 2007 2 2886 2007 3 3262 2007 4 5672 2008 1 4135 2008 2 4063 2008 3 4265 2008 4 6703 2009 1 4889 2009 2 4652 2009 3 5448 2009 4 9520 2010 1 7131 2010 2 6566 2010 3 7560 2010 4 12948 62 63 64 65 66 67 68 69 70 71 72 Start_Here Sheet1 Problem3 + 98 1. A two-way table that shows for each hospital the number of cases based on outcome 99 2. Replicate the pivot table to show the number of cases based on outcome as a percentage of total cases in each hospital. 100 101 102 103 104 Place your pivot tables below 105 106 107 108 Hospital 109 ER 110 ER 111 ER 112 ER 113 ER 114 ER 115 ER 116 ER 117 ER 118 ER 119 ER 120 ER 121 ER 122 ER 123 ER 124 ER 125 ER 126 ER 127 ER 128 ER 129 ER 130 ER 131 ER 132 ER 133 ER Risk Categon Outcome High Die High Die High Die High Die High Die High Die High Die High Die High Die High Die High Die High Die High Die High Die High Die High Die High Die High Die High Die High Die High Die High Die High Die High Die High Die Start_Here Sheet1 Problem + 3 N 3 4 N 3 A Year Quarter of Year Sales (in million $) E F G H 73 2011 1 9857 74 2011 2 9913 75 2011 3 10876 76 2011 4 17431 77 2012 1 13185 78 2012 12834 79 2012 3 13806 80 2012 4 21268 81 2013 1 16070 82 2013 15704 83 2013 3 17092 84 2013 4 25586 85 2014 1 19741 86 2014 19340 87 2014 3 20579 88 2014 4 29328 89 2015 1 22717 90 2015 2 23185 91 2015 3 24815 92 2015 4 34283 93 94 95 96 Part 2- Pivot Table 97 Create two pivot tables based on the below table. 98 1. A two-way table that shows for each hospital the number of cases based on outcome 99 2. Replicate the pivot table to show the number of cases based on outcome as a percentage of total cases in each hospital. 100 3 3 101