Web.cse.ohio-state.edu



CSE4194 SP15 Midterm Exam in ExcelName ______________________________________________Test Letter MERGEFIELD studentID ?studentID?Cheat Sheet? YesNoINSTRUCTIONSTurn all personal electronic equipment off; no headphones; no using your phone, etc. Also, no reaching into your backpack or pockets; no being excused to go to the bathroom, etc.You are not allowed to communicate with anyone, in any way, except the instructor during the testing period.You must follow the directions exactly per your own exam. There are no re-do’s or exceptions. Points will be deducted for not following directions even if “you prove you know the material”. Today, you are not allowed to deviate!Download the MTLabFile.xlsx file from the following link: you are done, submit your completed MTLabFile.xlsm file to Dropbox Lab14 folder. Only files submitted during the testing timeframe will count.You MUST turn in all three pages of this exam as well as your cheat sheet or you will get a zero for the exam!Sign here to verify that you have read and understood the instructions given above:SIGNATURE ____________________________________________FYI: If you don’t sign here, your exam will not be graded and you will get a zero.PART 1. SOLVERIn the Excel file you downloaded, on the Ques1 worksheet, use Solver to find the value of x that minimizes/maximizes (as defined below) your given function, subject to constraints defined to be a <= x <= b where:Your function is: MERGEFIELD "fx" ?fx?NOTE: *the x2 is really x2 – format issue on mail mergea = MERGEFIELD a ?a?b = MERGEFIELD b ?b?Minimize/Maximize option is: MERGEFIELD minmax ?minmax?PART 2. RECORDING A MACROIn the Excel file you downloaded, on the Ques2 worksheet, record a macro to solve for your above assigned function for each consecutive integer value between a and b, inclusive. For example, for a=-1 and b=3 and f(x) = x2 + 3x – 2, the result of your recorded macro should look like: Where:You type in the labels as part of the macro (cells A1:B1)x is in ascending order in column A (starting at A2) based on your given values a through bYour assigned function is typed into cell B2 and copied down and calculated for each of your values of x Be sure to assign CTRL-SHIFT-F to run this macro, save the file as a macro-enabled file and clear out this worksheet when you are done so I can run it from scratch to check it.PART 3. VBA CODEIn the Excel file you downloaded, on the Ques3 worksheet, write a VBA module to do the following:Ask the user to enter an input value between 0 and 100For each cell value in column Adetermine if the value in the cell is MERGEFIELD comparison ?comparison? to the input valueif true, then put “ MERGEFIELD TRUE ?TRUE?“ in column B If false, then put “ MERGEFIELD FALSE ?FALSE?” in column BOutput a message that specifies the MERGEFIELD calculation ?calculation? values in column B.NOTE: Do not assume that you know the number of values in column A i.e. make sure your code works for an unknown number of values, listed consecutively, in column A which means you will end the loop when you find a null/blank cell. For example, based on the following given values in column A and an input value requirement to put “Yes” in column B for each value in column A that equals zero, the VBA code generates the following values in column B; and then outputs that 20% of the letter grades match the input criteria i.e. 20% of the values are equal to zero.PART 4. PIVOT TABLEOpen the Excel file called MTLabPivotFiles from the MTExam link given in the instructions on the first page. Copy the data from the MERGEFIELD File ?File? worksheet to the Ques4Data worksheet in the Excel file you originally downloaded. In the Excel file you downloaded, on the Ques4 worksheet, use the data you just copied onto the Ques4Data worksheet to create a pivot table using the following criteria:Report Filter: MERGEFIELD ReportFilter ?ReportFilter?RowLabels: MERGEFIELD RowLabels ?RowLabels?ColumnLabels: MERGEFIELD ColumnLabels ?ColumnLabels?Values: MERGEFIELD Values ?Values? ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download