How can I count deviations between two rows?

J

J. Gary Ellison

Here's what I'm trying to do:

1 Test Question 1 2 3 4 5 6 ...
2 Answers Key A C A B D B ... # of Right Answers
3 Student 1 A D A B D C ... 4 (Formula would go here to compare current row)
4 Student 2 A C A B D D ... 5 (with Answer Key row & count the right answers)
....

How do count the right answers by comparing the answers from each student with the Answer Key in row 2. I want to be able to score the tests by entering in the answers.

L

Living the Dream

You could employ helper cells located somewhere else on your sheet and
do a sum of those.

Column A = Student Name:
Columns B - G = Test Answers 1 - 6

Lets use AB as your 1st Helper-Cell:

AB = IF(\$B3=\$B\$2,1,0)
AC = IF(\$C3=\$C\$2,1,0)
AE = IF(\$E3=\$E\$2,1,0)
AF = IF(\$F3=\$F\$2,1,0)
AG = IF(\$G3=\$G\$2,1,0)
.....
....
...
..

Copy formula's down as required.

And finally, to total each row of Helper-Cells:

Again, lets assume it Column H.

H3 = SUM(\$AB3:\$AG3)
H4 = SUM(\$AB4:\$AG4)
.....
....
...
..

Copy formula's down as required.

HTH
Mick.

C

Claus Busch

Hi Gary,

Am Sun, 30 Dec 2012 03:12:21 -0800 (PST) schrieb J. Gary Ellison:
1 Test Question 1 2 3 4 5 6 ...
2 Answers Key A C A B D B ... # of Right Answers
3 Student 1 A D A B D C ... 4 (Formula would go here to compare current row)
4 Student 2 A C A B D D ... 5 (with Answer Key row & count the right answers)

in H3 try:
=SUMPRODUCT(ISNUMBER(FIND({"A","C","A","B","D","B"},B3:G3))*1)

Regards
Claus Busch

J

joeu2004

J. Gary Ellison said:
1 Test Question 1 2 3 4 5 6 ...
2 Answers Key A C A B D B ... # of Right Answers
3 Student 1 A D A B D C ... 4 (Formula would go here to compare
current row)
4 Student 2 A C A B D D ... 5 (with Answer Key row & count the right
How do count the right answers by comparing the answers from each
student with the Answer Key in row 2. I want to be able to score
the tests by entering in the answers.

If the Answer Key is in C2:H2 and the Student1 answers are in C3:H3, then:

=SUMPRODUCT(--(C3:H3=\$C\$2:\$H\$2))

You can copy that formula down the column.