2018 EDSIGCON Proceedings - Abstract Presentation
Automated Assessment with Formative Feedback using Excel
Students learn better when given precise formative feedback on their work. The system described here gives precise feedback on Microsoft Excel assignments down to the cell level even for large enrollment classes. Better feedback makes it easier for students to identify and fix errors.
Prior attempts at automating assessments have typically been summative and focused on grading final answers (Cohenour & Hilterbran, 2016; Hill, 2003). In cases where the formula itself is analyzed for correctness, limited solutions are allowed. This can lead to imperfect assessments, since there can be multiple valid ways to arrive at a solution in Excel. For example, in some cases, a nested IF is logically equivalent to an IF combined with the boolean operator AND.
The Assessment System
A system was created to automatically grade Excel assignments and provide formative feedback to students. The professor begins with a complete assignment--the answer key. The system then generates a unique copy of the assignment for each student in order to discourage cheating. The copies are unique both in terms of assumptions values, and unique in the position where the formulas appear on the spreadsheet. The students complete their copy of the assignment and then submit it for grading.
The grading system reviews every cell on every worksheet and compares it to each student’s unique answer key. The system looks at each cell to determine if it is a reasonable answer and is properly formatted. Importantly, it then looks inside each formula to see if the correct precedent cells were referenced and if the cell used absolute vs. relative references appropriately. The system operates in batch mode, grading hundreds of assignments at a time while providing precise feedback for each student.
per the instructions, we wrote this abstract with a figure in mind, which did not attach. The following notes pertain to the missing figure, which will be included in the presentation:
The system provides detailed feedback, as seen in the figure. In the figure, note that for cell J32 the student got the right answer (89152) and received partial credit for it. However he/she lost points for hard coding values that should have been referenced (missing precedents). Furthermore, those missing references should have been absolute references (missing absolute references) to facilitate copying the formula down the column. The point values for each error are set by the professor through the User Interface. On a separate sheet, the system also provides rolled up macro level feedback summarizing errors by type of error.
Our system is the first to automatically verify whether a solution is not just correct, but was arrived at by using correct formulas and best practice--e.g., using absolute references where appropriate. This relieves the professor of most of the burden of grading. Moreover, the system generates feedback right where the error occurred, reducing the need to transfer information between a graded rubric and a graded solution. We believe that the more accessible feedback facilitates better consumption thus leading to improved learning.
Cohenour, C., Hilterbran, A. (2016). Automated Grading of Excel Workbooks using Matlab, Proceedings of the American Society Engineering Education, Conference, June 2016
Hill, T. G. (2003). MEAGER: Microsoft Excel automated grader. Journal of Computing Sciences in Colleges, 18(6), 151-164.
Recommended Citation: Frost, R., Matta, V., (2018). Automated Assessment with Formative Feedback using Excel. Proceedings of the EDSIG Conference, (2018) n.4789, Norfolk, Virginia