Help me Grade My Students

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


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


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.

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.

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.

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.

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

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.