1

I'm currently working in a school and students answered a 50 question form on ICT Ability. The results of this form have been put into a spreadsheet and I have to order the names in terms of ability.

I know how to do each row to check if the answer was correct but I'm wondering if I put in a row with the correct answers, is there a way to rank each student in a percentage with how many answered the same as the row?

Sample data:

Name    Q1      Q2      Q3      Q4 
Answer  No      True    False   Portait  
John    No      True    False   Portait  
Jenny   Yes     True    True    Landscape 
April   Yes     False   True    Landscape

Sample result:

Name    %
John    100
April   25
Jenny   0
  • The best way is to share a spreadsheet with us and show us the expected outcome. – Jacob Jan Tuinstra Sep 11 '13 at 8:47
  • Name Q1 Q2 Q3 Q4 John No True False Portait Jenny Yes True True Landscape April Yes False True Landscape So basically id like it to go through johns answers, run through each row and compare them to johns, counting how many are the same giving me a small table like this: John 100% Jenny 30% April 70% Sorry I'm not allowed to share the database as its private – Stuart Thompson Sep 11 '13 at 9:11
  • Baking powder? I don't get it. Are you able to share? – Jacob Jan Tuinstra Sep 11 '13 at 9:14
  • Output of April should be 25%..... – Jacob Jan Tuinstra Oct 2 '13 at 11:54
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.