Excel Auto Grading

A t Autograding di E Excel l spreadsheets: d h t An A alternative lt ti method th d off assessmentt Moira M i Sarsfield S fi ld and d Steve St Cook C k Department of Life Sciences Sciences, Faculty of Natural Sciences, Sciences Imperial College London London. ABSTRACT

THE AUTOGRADING PROCESS

In the first year of the biology degree stream at Imperial we now use three different forms of assessment for practical classes: • online q quizzes in Blackboard;;

The autograding process was developed by Dr Steve Cook Cook, and it works as follows: • Students St d t download d l d a spreadsheet d h t template t l t from f Bl kb d (A). Blackboard (A) The Th template p can include calculations,, graphs, g p , MCQs,, short answer q questions,, and more.

•

autograding using Excel spreadsheets; and

•

traditionally marked write-ups write ups, usually prepared by the student in Word Word.

•

The drivers for the introduction of the new assessment methods were: • to deal with increasing numbers – from 110 to 220 students per class – meaning many more papers to mark;

They complete the spreadsheet, following the instructions given in the practical schedule ((B)) and on the spreadsheet p p itself and upload p the completed p version into the Blackboard assignment dropbox. dropbox

•

The tutor constructs a model answer file ((C), ), which includes information on the correct answers. These may be simple comparisons, Excel calculations based on data in the spreadsheet or regexes. regexes General feedback and scores for correct answers ans ers are also incl included ded in this file file.

•

All of the completed spreadsheets are downloaded from Blackboard in ZIP format format.

•

A Perl script grades all the spreadsheets in the ZIP file by comparing the data with the model answer answer.

•

Marks and feedback are inserted automatically y into a copy py of each student's spreadsheet (D), (D) which is renamed with their username username.

•

Ag gradebook is p prepared p ((E)) showing g overall results from all students.

•

Results are returned via Blackboard: general feedback (F), (F) individual grade and link to individual marked spreadsheet including feedback.

•

to improve the quality of feedback, feedback making it more extensive extensive, more consistent and more quickly delivered delivered.

This poster describes the autograding method which is particularly useful for assessing the ability of first year students to analyse and present data from practical classes classes. The autograding g g process p is described,, and examples p are p presented to highlight g g educational and technical features. features Extensions and alternative uses of autograding are discussed discussed, and details are provided on the reactions of staff and students to this new assessmentt method. th d

Full instructions are provided p and each spreadsheet sp s iss colour coded in the same way so that students become accustomed to the requirements requirements.

Students must calculate l l t th i answers their using i E Excell f formulae, l which h h is a very useful f l transfer-able skill.

The h model d l answer checks h k the h student’s d ’ answer, using g the student’s own data.

The h spreadsheet p d h is p protected d so that h the h students d can only y enter data into the appropriate pp p cells.

Numerical comparisons, p regexes g and ffunctions can be used s within the model answer.

Boolean logic g can be used in the model answer – e.g. g AND D (&&), OR R (|). (|)

The program can be run once without marking to ascertain class data for use within the model answer.

Electronic assessment Follow the instructions in the Excel spreadsheet. For Biuret and Bradford, use your data to perform linear regressions of BSA mass (x) vs. absorbance (y) and calculate the slope (m), y-intercept (c) and the correlation coefficient (r). Use these parameters to calculate the mass of ovalbumin in the assay tube, and from this, the ovalbumin concentration in the original i i l UNKNOWN UNKNOWN. The Th fi finall result lt should h ld be b th the protein t i concentration t ti iin th the original i i l UNKNOWN iin mg mL L−11.

Statistics for f each h question are p produced in the gradebook g allowing g a check to be made on the overall results. Common errors can also be noted and general feedback provided on these. these

Marks and general g feedback f are automatically y inserted onto a copy py off the sp spreadsheet s which iss returned to the sstudent and further f feedback f iss provided for p f all students s s via the discussion sc ss forum. f

The gradebook Th d b k collates ll details d l off the h marks k and d answers for f each h student.

EXTENSIONS AND ALTERNATIVE USES Paragraph g p answers The autograding g g system y can also be used to assess paragraph answers which are manually marked. marked In this case case, the marker gives a mark and feedback for each paragraph answer within the gradebook and then this i f information ti is i copied i d back b k into i t each h student’s t d t’ spreadsheet. d h t M Markers k commentt th thatt this improves the consistency of their marking, as they can cross-check marks, and that they can easily provide more extensive feedback for each student, as text fields are automatically completed within Excel. Excel

REACTIONS OF STUDENTS A survey y of first year y students showed that 90% p preferred autograding g g spreadsheets to paper write write-ups ups. The reasons given for this preference were:

Peer review Spreadsheets were used on the Biochemistry degree stream to gather peer review grades and comments on poster presentations presentations. The data was then collated into a gradebook using the autograding program, program and individual feedback was provided.

The only negative comment made was of ‘harshness’ (20% of respondents).

Collection C ll ti off d data t with ith validation lid ti Th autograding The t di process was used d to t collect ll t student choices for final yyear options p on the Biomedical Science degree g stream. The options available are very complex, and we wanted to ensure that students provided all required information and didn’t didn t select impossible combinations combinations. This required data validation which was achieved within a data-collection data collection spreadsheet spreadsheet. Ch i Choices were collated ll t d iinto t a single i l spreadsheet d h t using i th the autograding t di program. F ili it with Familiarity ith autograding t di makes k these th methods th d very easy for f students t d t to t use.

•

Lots of feedback – 60%

•

Quick turnaround – 60%

•

Transferable skills – 60%

REACTIONS OF STAFF Staff St ff appreciate i t that th t the th autograding t di process makes k marking ki quicker, i k simpler i l and d more objective. j Well-written general g feedback usuallyy covers most cases,, and individual clarification can be provided where students have further questions. questions The turnaround time can be rapid, p , which is useful when results of one p practical feed into a subsequent practical practical. It is important that this is not the only assessment method used. Writing up a traditional lab report is still a required skill which must be practised and assessed assessed.