Exam/Test/Quiz Result Analysis

This Excel document helps you provide directed feedback to all of your students quickly as well as help with report writing. After every summative assessment (quiz, exam or test) you will be able to enter the exam questions and marks for each of your students. At this point the document will provide you with 2 summaries.

Detailed breakdown

The first summary is a detailed analysis of every topic on the test and how well the student performed in that topic. There is also information of how well the student performed the 3 different skills: Factual Recall, Application, and Calculation. You can add as many topics as you would like (up to 99 different topics).

The second summary is more focused on report writing. This tab has 2 parts. The first section displays the strongest and weakest skill (Factual Recall, Application or Calculation) and the second displays the strongest and weakest topics. This allows you to quickly write a targeted report for each of your students.

How to use it

In general you should use a unique spreadsheet for each of your classes and each assessment they undertake. The spreadsheet is limited to 99 students. If you have multiple classes that have taken the same exam/quiz/test you can use the ExamConfig tab to copy the exam from one class to another.

This example uses the first two questions from the AQA Physics GCSE 2022 Foundation Paper 1 summer paper.

Adding topics

Firstly make sure that the Configuration tab has all of the topics that are on the test. I’ve added ‘Energy’ as a topic.

Define ExamConfig

Next, define the assessment the students undertook.

As you do this make sure you place the number 1 only in one column for Question. Use the sub Q for each individual part. This allows the sheet to add solid lines between questions.

You may want to be more granular with your topics or decide that questions are a different type. If this has been done correctly the questions will appear as headers on the RawData tab.

Entering student results

Right now the spreadsheet cannot handle students absent from exams. This will lower the average of your entire class and you will also get incorrect summary. Don’t place their names in the table and if you do, do not delete them since that will break other aspects. Once you have the student names entered start filling in their marks they got for each question.

Summaries and Reports

Once all the data is entered go to either the summary or summary report tab to get the output. This can be colour coded by using the configuration tab to set the bounds. You could use this data as an input into ChatGPT to generate accurate reports.