Google-sheets – How to add up the correct answers from the check boxes question

google sheetsgoogle-forms

What syntax do I use when adding up the number of correct answers that have boxes checked?

My question is;

In this example the correct answers are Cotton and Calico.
I award the students a total of 2 marks ( 1 mark each) in selected. Therefore if a student selects Denim, Satin or Silk, they do not get a mark.

The current Syntax I have been using is something along the lines of;

=if(D2="Cotton.", "1", "0", "Calico.", "1", "0", "Demin.", "0", "0", "Satin.", "0", "0", "Silk.", "0", "0")

Best Answer

Two more to try:

=SUM(ArrayFormula(--regexmatch(split(D2,","), "Cotton|Calico")))

=SUM(ArrayFormula(countif(D2, {"*Cotton*", "*Calico*"})))

Related Solutions

Google-sheets – How to report which checkboxes were left unchecked on a Google Forms submission

Still don't have a solution for how to directly get values of unchecked checkboxes, but I was at least able to write a function that compares the correct answer to the submission.

This splits the correct answer into an array then compares each item to the submission:

function checkAnswer(answer, key) {
var kArray = key.split(', ');
var nArray = [];
for (i=0; i<kArray.length; i++) {
    if (answer.indexOf(kArray[i]) == -1) {
        nArray.push(kArray[i]);
    }
}
return nArray.join(", ");
}

Then I could call it every time I needed to report the boxes left unchecked. For example, with the doors question:

if (doors != correctDoors) {
  var doorsMessage = "Unlocked: " + checkAnswer(doors, correctDoors);
  report.push(doorsMessage);
}

At the end, I'll compile the report with:

report.join("\n");

And voila! The best I could do...

Ultimately, I'm still looking for how to directly get the unchecked boxes if anyone has a solution.

Google-sheets – Grading a Google Spreadsheet by answers given in a Google Form

With the folowing piece of code you can calculate the grades. @Tom, thanks for reviewing the question.

Code

function myGrades(range) {
  var output = [];

  // header
  output.push(["Name", "Score"]);

  // set up the proper matrix
  for(var j=2, jLen=range.length; j<jLen; j++) {
    var name = range[j][0], score = 0;
    for(var l=1, lLen=range[0].length; l<lLen; l++) {
      if(range[j][l] == range[1][l]) {
        score++;
      }
    }    
    // calculate the grade
    var grade = Math.round(score/((range[0].length-1))*100);
    output.push([name, grade + "%"]);    
  }
  return output;        
}

Usage

=myGrades(DATA!A1:E5)

Add the code under Tools > Script editor from the spreadsheet menu. In the script editor, paste the code and press the save button.

Example

I've created an example file for you: Grading Answers