## MS Excel for Exam Results and Class Records

Learn how to use a spreadsheet to work with student marks - to calculate final marks for students with different assessment components, work with different weightings, calculate class averages etc.

• Download the workshop handout, the Excel exercise file and solution [zip of all files] (right-click to download, choose 'Save target as').
• Screencasts showing how to do each exercise are below.
• Here is a short explanation of how to calculate weighted averages [2 mins]
• If you are not very familiar with MS Excel, you may wish to view some demos from Microsoft

Attendance Worksheet

• Demo - Sort students into alphabetical order, work out the total number of students who attended each class (SUM), work out the average attendance for each student (AVERAGE) and apply conditional formatting [5 mins 40 secs]

Final results Worksheet

• Demo 1 - Calculate the final mark using weighted averages [2 mins 8 secs]
• Demo 2 - Use built-in functions to calculate various statistics (AVERAGE, MEDIAN, STDEV, MIN and MAX) and count the number of passes, fails and absences (COUNTIF) [5 mins 25 secs]
• Demo 3 - Apply conditional formatting to highlight students who have failed [1 min 3 secs]

CA equal weights Worksheet

• Demo - Calculate the average CA mark for each student (AVERAGE) and calculate the 'best 3 out of 4' CA mark (SUM, MIN) [2 mins 15 secs]

CA equal weights2 Worksheet

• Demo - Calculate the average CA mark for each student (AVERAGE) and calculate the 'best 8 out of 10' CA mark (SUM, SMALL) [3 mins 13 secs]

CA diff weights Worksheet

• Demo 1 - Create an appropriate formula to calculate the final mark [3 mins 31 secs]
• Demo 2 - Calculate the 'best 75%' CA mark (download spreadsheet template) [4 mins 8 secs]

Need to pass Worksheet

• Demo - Use weighted averages to calculate the final mark [2 mins 22 secs]

This is a work-in-progress and I would value any feedback you may have. There are a couple of glitches in the screencasts that I will fix - any feedback or comments that you can send me in the meantime will be incorporated in future revisions. Note that all names and data are fictional, and that no particular assessment strategy is endorsed.
Back to homepage

If you have any suggestions or questions, comments on the above videos or a request for a new video on a specific topic,
please contact Damien Raftery, eLearning Development Officer