Grading Test Auomatically


Could someone please provide me some guidance on how to create an SS that, if the form is completed, the SS can grade an exam automatically? I work with General Residents that are going to be future surgeons. They have to complete a 5 hour lecture every week. Our departments faculty is obligated to join them due to having to obtain CC Credit. They have to be certified in order to keep teaching and participating in surgeries with their residents. This is why they have to maintain their CC credit. Anyways, each week our faculty have to take a multiple choice test that has 5 question. I then have to grade this test and see which faculty members qualify for CC credit. I then have to grade over 100 faculty member answers and then enter each one of their names manually into another form. I was seeing if there was anyway to have this test graded automatically? Grading the tests takes so much time.

By the way, every week

the test changes week by week. I do have to update the questions and answers every week within my SS form.

The future belongs to those who believe in the beauty oftheir dreams. Eleanor Roosevelt



  • brianschmidt
    brianschmidt ✭✭✭✭✭✭
    edited 01/31/24

    Here's how I would probably accomplish this, though there may be multiple ways to do so.

    Step 1: Create a summary data field for each question (access and add summary data in the panel to the right). You could label each one something like "Question 1 Correct Answer", "Question 2 Correct Answer", etc. You'll then put in the correct answer exactly as it's written in the dropdown menu for that corresponding question column.

    Step 2: Create corresponding columns for each question (not to appear in the test form) with column formulas that mark each question right or wrong by populating a 1 or 0 based on their response. In each of these, you'll reference the summary data where the correct answers are. For example, here's what the formula would look like for question 1:

    =IF([Question 1]@row=[Question 1 Correct Answer]#, 1, 0)

    Step 3: Create a percentage scoring column. Be sure to format it as a % column and add a column formula that totals the scores shown in your helper scoring columns divided by the total number of questions. The formula may look something like this:

    =([Question 1 Helper]@row+[Question 2 Helper]@row+[Question 3 Helper]@row+[Question 4 Helper]@row+[Question 5 Helper]@row)/5

    Step 4: Create a row report that pulls student name (or whatever info you want) and their score. That way, you can see names and scores at a glance.

    Hope this helps!:)

  • KPH
    KPH ✭✭✭✭✭✭

    I second @brianschmidt 's plan with one additional suggestion. In step 1, rather than using a summary data field, I would use another sheet (I'll call this answer sheet) and in that sheet I would have a column for the test number and then columns for each correct answer. These columns would be populated with the value from the drop down, as Brian said. This allows you to add a new row of correct answers each week.

    On the form you would include a hidden field containing the test number.

    Then in step 2 rather than referencing the sheet summary correct answer you would reference the correct row (based on test number) and column (based on question number) in the answer sheet.

    I think that would be easier to update each week.

    Steps 3 and 4 as Brian described.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!