Results 1 to 6 of 6

Thread: Help me Grade My Students

  1. #1

    Unhappy Help me Grade My Students



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello there!

    Before anything I'd like to say that english is not my main language... So I apologize in advance for any mistakes

    And now to the problem:

    I have a Excel spreadsheet with the following information:
    In the first column I store the student's name, and in every other column I stored the answer he chose in a multiple-choice exam, such that the Nth column contains the answer to the (N-1)th question.

    The answer is always a string: A, B, C, D or E. In some cases it's empty "" (when he didn't chose a answer) and sometimes its a combination like "AB" when he chose two answers.

    In the last row I stored the correct answers to each question.

    I need a formula that will compare the student answer to the correct answer. And for each 'match' he'll recevei 1 point.

    I tried using functions like SUMIF and COUNTIF but to no avail. Clearly I need a more robust function. Can you guys please help me?

    Here's an example of what I have right now
    John A B A
    Sara C CB A
    CorrectAnswer A E A


    Here's an example of what I'd like to have after I apply the formula

    John A B A 2
    Sara C CB A 1
    CorrectAnswer A E A 3

    The table I have right now has 31 columns (30 questions and 1 name). Is it possible to give 'weights' to my questions? In this very case I'd like to give 2 points for each question after the 10th and only 1 point to each question before that.

  2. #2
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,821
    Articles
    0
    Excel Version
    O365
    Try

    =SUMPRODUCT(--(ISNUMBER(FIND($B$3:$D$3,$B1:$D1))))


    You will need to adjust $B$3:$D$3 to the actual range of correct scores, and the $D1 to the correct last column.

  3. #3
    Dear lord! It worked! It worked!

    But I'd like to understand what you just did, instead of just using it. Can you please explain me how the functions
    "ISNUMBER" and "SUMPRODUCT" work? I think I understand how FIND works.

  4. #4
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,821
    Articles
    0
    Excel Version
    O365
    I am comparing all of the values in the correct scores range against all of the values in the student's scores range. I use FIND to check if that correct score is in the student score (FIND because the student may have more than one, so I can't just compare). FIND returns the position if it finds a match, an error if no match. We don't care about where it is, so we use ISNUMBER against the FIND result to return us TRUE (found it, student got that result) or FALSE (no match, messed up on that one). I use the -- to coerce all TRUE and FALSE values to 1's and 0's, then SUMPRODUCT sums all the 1's and 0's.

  5. #5
    Really great! Thank you!

    I never saw the "--" function. I'll check it out and come back to you

    I used your function twice: 1 to grade the questions from 1 to 10 and 1 to grade the 11 to 30. Then getting the grades with weights was trivial.

    Thank you again

  6. #6
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,821
    Articles
    0
    Excel Version
    O365
    See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for an explanation of the --. It covers a lot more than just that, but that is also explained.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •