View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bernard Liengme Bernard Liengme is offline
external usenet poster
 
Posts: 4,393
Default Grading Multiple Choice Exam

Use =SUMPRODUCT(--($B$1:$K$1=B2:K2))
The $s allow you to copy down the column.
The double negation converts Boolean (True/False) to numbers (1/0) so
summing can occur.
best wishes (I'm glad I'm retired and exam marking is history for me!)
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme



"Jay Windley" <Jay wrote in message
...
I am setting up a spreadsheet for my students' answers to a multiple choice
test. One of the things I want to do is see how many students got each
question correct, and that is easy enough to do using COUNTIF. However, I
am
having a hard time creating a function that will tell me how many
questions a
student got correct.

Suppose the key is in row 1, columns B through K (so B1:K1), and below
that
I have a row for each student (with student names in column A). So the
first
student has his name in A2, and his answers in B2:K2. So how do I count
the
number of cells in B2:K2 which match the corresponding cell in B1:K1? I'm
aware that I could do something hideous like
=IF(B2=B1,1)+IF(C2=C1,1)+...+IF(K2=K1,1)
but there has got to be a more elegant way! I know that I could also
create
another column for each student that compares their responses to the key
row,
but it seems like there should be a function to do this.

Thanks!