Hello all! I am a teacher and I am trying to calculate what each student
scored on a test. Here's my setup:

Column A Column B C D
E
Question1 Question2 Question 3
Question 25

Correct Answer: A D A
C

John Bratty C D A
B
Willy Wonka D A D
C
Jenny Penny A B A
C

I used COUNTIF to determine what the class scored on each question, but how
can I tell what each student scored on the test? please help. I am using
Excel 2007.

On Feb 24, 10:26 am, Jenai wrote:
Hello all! I am a teacher and I am trying to calculate what each student
scored on a test. Here's my setup:

Column A Column B C D
E
Question1 Question2 Question 3
Question 25

Correct Answer: A D A
C

John Bratty C D A
B
Willy Wonka D A D
C
Jenny Penny A B A
C

I used COUNTIF to determine what the class scored on each question, but how
can I tell what each student scored on the test? please help. I am using
Excel 2007.

=SUMPRODUCT(--(B2:Z2=$B$1:$Z$1))

assuming John Bratty's answers are in row 2 and the correct answers
are in row 1 and that there are 25 question.
Fill down to return other student's marks.

Ken Johnson

Ken, Thank you so very, very much. i am totally elated right now. I can
finish my spreadsheets in a jiffy. Merci beaucoup!
--
I am pregnant with possibilities, and so are you!


"Ken Johnson" wrote:

On Feb 24, 10:26 am, Jenai wrote:
Hello all! I am a teacher and I am trying to calculate what each student
scored on a test. Here's my setup:

Column A Column B C D
E
Question1 Question2 Question 3
Question 25

Correct Answer: A D A
C

John Bratty C D A
B
Willy Wonka D A D
C
Jenny Penny A B A
C

I used COUNTIF to determine what the class scored on each question, but how
can I tell what each student scored on the test? please help. I am using
Excel 2007.

=SUMPRODUCT(--(B2:Z2=$B$1:$Z$1))

assuming John Bratty's answers are in row 2 and the correct answers
are in row 1 and that there are 25 question.
Fill down to return other student's marks.

Ken Johnson

Hi Jenai,

You're welcome.
If you email me I can send you a workbook that is set up especially
for getting students' responses (A,B,C,D or N for non-attempt) quickly
onto the sheet. It's set up so that you don't have to use the widely
separated A,B,C or D keys, also, you don't press the Enter key between
answers. When entering a student's answers you press I for A, O for
B, P for C, [ for D or ] for N. The I,O,P,[, and ] keys are all in a
line, so you simply keep your four fingers resting on the I,O,P &
[ keys, pressing on the appropriate key as you read down the student's
answer sheet. When a student leaves a question unanswered you just
move you pinkie across to the ] key.
The formula is easily changed if you prefer to use a different
combination of adjacent keys (F,G,H,J & K would be good since F and J
keys both have a little bump on them).
I use the sheet every time I mark multiple choice questions, even when
I don't really need to analyse students' answers. After marking
manually using an overlay mask, I then enter the answers onto the
worksheet as a double check. It's amazing how many mistakes some
teachers, including myself, make when using the overlay mask. It also
gives me a permanent record of student answers so that students can't
change their answers after they have been marked, then accuse the
marker of making a mistake.
The formula can also be adjusted to accept more than one correct
answer to accommodate poorly written questions.

If you are interested in a copy just get my gmail account name from my
profile and let me know so that I can reply with the workbook as an
attachment.

Ken Johnson

I sent email with subject: Excel workbook. Thanks.
--
I am pregnant with possibilities, and so are you!


"Ken Johnson" wrote:

Hi Jenai,

You're welcome.
If you email me I can send you a workbook that is set up especially
for getting students' responses (A,B,C,D or N for non-attempt) quickly
onto the sheet. It's set up so that you don't have to use the widely
separated A,B,C or D keys, also, you don't press the Enter key between
answers. When entering a student's answers you press I for A, O for
B, P for C, [ for D or ] for N. The I,O,P,[, and ] keys are all in a
line, so you simply keep your four fingers resting on the I,O,P &
[ keys, pressing on the appropriate key as you read down the student's
answer sheet. When a student leaves a question unanswered you just
move you pinkie across to the ] key.
The formula is easily changed if you prefer to use a different
combination of adjacent keys (F,G,H,J & K would be good since F and J
keys both have a little bump on them).
I use the sheet every time I mark multiple choice questions, even when
I don't really need to analyse students' answers. After marking
manually using an overlay mask, I then enter the answers onto the
worksheet as a double check. It's amazing how many mistakes some
teachers, including myself, make when using the overlay mask. It also
gives me a permanent record of student answers so that students can't
change their answers after they have been marked, then accuse the
marker of making a mistake.
The formula can also be adjusted to accept more than one correct
answer to accommodate poorly written questions.

If you are interested in a copy just get my gmail account name from my
profile and let me know so that I can reply with the workbook as an
attachment.

Ken Johnson