Automatic Grader for Solver Models
I have developed an automatic grader for Solver models that should take a lot of the tedium out of grading. You, the grader, will still have to use your judgment about partial credit, but this grader should at least let you check whether a model is correct, and if it isn’t, help you locate the errors. In the latter case, you can decide how much partial credit to give.
An important feature of this grader is that you do not need to provide the students with a structuredtemplate (similar to the “shells” I provide to instructors in my books). In fact, you have two options.
In the first option, which I call the no test inputsoption, the grader checks whether the student’s solution, for the inputs values in the statement of the problem, match those in the key. This option is particularly useful if you want students to start from an absolutely blank spreadsheet.
In the second option, which I call the test Inputsoption, the grader checks whether the student’s solution matches those in the key for any number of sets of values for a selected subset of inputs, where you supply the input values you want to test. This option is useful if you want to check whether the student’s solution is robust to changes in selected inputs. In this case, you must give the students a template that contains, at the very least, the subset of the inputs you want to test.In this case, the students should be instructed not to move any of the input cells in the template.
The grader is an add-in contained in the fileGrading Add-In for Solver.xlam. It should be used in conjunction with two files: (1) your key, and (2) the student’s file. Here’s how it works.
Loading the Add-In
To load the add-in, open the Grading Add-In for Solver.xlam file. You can do this with Excel already open or not. Make sure you choose to enable macros! This creates a new Grader for Solver menu on the Add-Ins ribbon. This menu has five items: Create Inputs Range, Generate Results for Key, Run Grader, Instructions for Grading Add-In, and Unload the Grading Add-In. (See Figure 1.) The last two items are obvious. The others are discussed below.
Figure 1: Grader for Solver Menu
Setting Up the Key
Step 1You must first develop a grading key file that includes the correct Solver model.
Step 2For the test inputs option, you must create a table to the right of the model that contains the input values you want to test. See Figure 2 for an example. The input cells for the model are on the left in blue. This example assumes you want to test three sets of values for these inputs, listed in columns J–L. The first set in this example corresponds to the input values in the statement of the problem, but this is not necessary; you can test any values you like. The grading add-in gets you started. Select the Create Inputs Range menu item, and the add-in creates the beginning of a table, as shown in Figure 3, to the right of the model. Then you can finish it by entering the input addresses and values you want to test.
Figure 2: Input Values to Test
Figure 3: Beginning of Inputs Table
Note that for the no test inputs option, you can skip this step and proceed directly to step 3.
Step 3Select the Generate Results for Keymenu item. In the no test inputs option, this solves the original problem and provides summary results for the solution, as shown in Figure 4. These are placed to the right of the model.
Figure 4: Summary results for No Test Inputs Option
For the test inputs option, the summary results are provided for each set of test input values, and they are placed below the test input range, as shown in Figure 5. (If a test problem has no feasible solutions, NFSappears in the corresponding cells.)Note that the blank cells next to the summary values are placeholders for the students’ values. This enables easy checking later on.
Figure 5: Summary Results for Test Inputs Option
In either of these options, the grader names the range in Figure 4 or 5 CopyRange. The grader uses this CopyRange name during the grading process, so you should notdelete it or modify it.
Note about UsedRange. If the tables in Figures 2–5 are not visible when you grade, they are probably way out to the right in your worksheet. The grader places them just to the right of the “UsedRange” of the worksheet. This range, a property of any Excel worksheet,is essentially the largest rectangle that includes all of the data in the worksheet. Sometimes, leftover junk is stored in far corners of your worksheet, and this makes the UsedRange very large. When this occurs, you need to do some detective work to locate the junk and delete a lot of “blank” rows or columns to shrink the UsedRange. There is an easy way to locate the UsedRange in your worksheet. Open the Visual Basic Editor by clicking on Alt-F11. Select Immediate Window from the View menu, enter the line ?ActiveSheet.UsedRange.Addressin the Immediate Window, and press Enter. You might be surprised at what you find. If you then delete all the unnecessary rows and columns, the size of your Excel file can shrink significantly.
Grading the Student’s Model
To grade a student’s model, open their file, activate their model sheet, and select the Run Grader menu item in Figure 1. This will (1) copy the CopyRangerange to the student’s worksheet, to the right of their model, (2) run Solver for the values of the inputs in the problem statement (for the no test inputs option, illustrated in Figure 6) or for each of the sets of test inputs (for the test inputs option, illustrated in Figure 7), and (3) show the results. (Note: The same caution about the UsedRange, discussed above, applies to the student’s worksheet. The results might be far out to the right.)
The results includesummary results (target values and sum of changing cell values) in red next to the results from the key. When a value is correct (to within 0.1%), the corresponding cell is boldfaced and its background is colored pink. This makes correct values easy to identify (see Figures 6 and 7). The student’s solution is not necessarily correct just because their values in this table match those in the key, but this certainly provides strong evidence of correctness.
Figure 6: Correct Solution in No Test Inputs Option
Figure 7: Correct Solution in Test Inputs Option
The grader also places more detailed results about the Solver setup and the results from the run(s) below the summary results. An example (with the test inputs option) appears in Figure 8.
Figure 8: Details Results
Finally, the grader applies the following color coding to the student’s model:
- The font for all cells with formulas is boldfaced and colored blue.
- The background of the target cell is colored gray.
- The background of the changing cells is colored red. If the target is a changing cell, its background is still colored gray, but its font is colored red.
- The ranges for both sides of equality or inequality constraints are colored some shade of green. If either of these ranges is part of the changing cell range, the background stays red, but the font is colored green.
- The font of any changing cells constrained as integer is colored white.
- The font of any changing cells constrained as binary is colored yellow.
Three example of this color coding are shown in Figures 9, 10, and 11. The example in Figure 9 includes integer constraints, the example in Figure 10 includes binary constraints, and the example in Figure 11 includes a target cell that is also a changing cell (and its other changing cells are constrained to be integers).
Figure 9: An Example of Color Coding with Integer Constraints
Figure 10: An Example of Color Coding with Binary Constraints
Figure 11: An Example Where the Target Cell is a Changing Cell
At this point, the grader has done its work. If it indicates obvious errors in the student’s model, it is up to you to find them and assign partial credit. But the results from the add-in should speed up this process significantly.
Once you are finished grading all of the students’ files, just close Excel. The next time you need to grade, just reload the Grading Add-In for Solver.xlam file.
Note: Each time you run theRun Gradermacro, it asks whether your grading key file, or the model sheet within this file, has changed. Although this can be somewhat annoying, the grader has no way of knowing whether you might have started to grade a new problem in a new file or in a different sheet of the same file, so it checks with you before proceeding.
Example files
To see examples of the finished grading keys and graded student models, open the various “Key” and “Student”files I’ve included. The student files include examples of correct and incorrect models. The incorrect models illustrate things you might see when you grade your students’ models, but I’m sure there are plenty of other types of errors that I haven’t even considered. So again, you have to use common sense and some detective work when using this grader.
General Grading Platform
In addition, you can use my more general grading “platform” to promote grading consistency. (See the General grading program bullet at This general program is completely independent of the grader discussed here—it could be used to grade English essays, calculus exams, or anything else—but it can be used in conjunction with the grader discussed here to make the grading process almost enjoyable!