Sunday, March 28, 2010

Exam point counter (Excel)

To help counting points during correction of the exams, I made some Excel VBS. When the exams are corrected, next to every error a -0,5 -1 or -1,5 is written, and these have to be substracted from the total points of every part. To make it possible to perform this little task one handed, I linked the 'h' key to -0,5, the 'j' key to -1 and the 'k' key to -1,5. Now the exams can be run through very fast and the result of every part is shown immediately. To keep track of the total points of every part for every student, I linked the 'x' key to save the result in a sheet with the totals. Finally, the 'c' key is linked to clear the contens of a column with all substractions of a part of the exam.


The exepected usage of the sheet:



  • fill in the totals of every part of the exam in the top most row of the first sheet (called 'Punten teller'/'Point counter').

  • (optional) if the maximum points in a part is different from the weight of that part in the complete exam, the second row in the first sheet can be used. The points will be recalculated to match the weight.

  • (optional) fill in the name of every part, for example: excercises, theory, vocabulary, etc.

  • select the cell in the fifth row of the first column an run through the exam. For each part of the exam, a next column should be used

  • while running through a part of the exam, use the keys 'h', 'j' and 'k' for every mistake in the exam, respectively substracting 0,5 1 or 1,5 points

  • when a part of the exam is finished, you can see the total points in the third row of that column

  • if you want to keep that result in the totals list, press the 'x' key, the statusbar will clearly show the changes applied in the totals list (so you don't need to switch the sheets every time to verify)

  • if you want to recount the points of that part again, press the 'c' key to clear the data of that column (exam part), without updating the totals list

  • if you want to clear the column (part of exam) and go to the next part, use the 'n' key

  • when every part is done, the points of each part will be saved in the 'Totaal'/'Total' sheet for every student, and the total is converted to a % point

  • (optional) for every student, a name can be added in the second column of the 'Totaal'/'Total' sheet

  • the total point of every part are rounded as for example 6.0; 6.1; 6.2 go to 6, 6.3,6.4,6.5,6.6 and 6.7 go to 6.5 and 6.8 and 6.9 go to 7

  • (optional) comments for the exam of every student can be added in the 'Totaal'/'Total' sheet (last column)

  • to get a nice overview of results of every student in a separate document (vakrapport/course report), a word template using Words build in MailMerge is created. It can be used in combination with a sheet based on this Excel template. The template can be updated, the result will be a separate page with points, median and comments for every individual student (a hidden sheet 'MailMerge' in the Exel template is used for this). To get best results using this template, it is advised to fill in the topics in this list marked as '(optional)'


Excel sheet template (english version, dutch 2003 version, english 2003 version) (when opening a new sheet will be created, so the template will always be kept intact)


29/03/2010: Updated template, english and 2003 versions added


04/04/2010: Updated template, added vakrapport template