How can I count deviations between two rows?
 

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.

How can I count deviations between two rows?
 

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

Using your setup.

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)
AD = IF($D3=$D$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.

How can I count deviations between two rows?
 

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)
please suit the delimiter to your system


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

How can I count deviations between two rows?
 

"J. Gary Ellison" wrote:
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.

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.

-------------------------

=IF(LOOKUP(ROW(A1),$A$2:$A$16,$B$2:$B$16)=C2,"Corr ect","Incorrect")

Assuming:
Question number is mentioned in A2:a16
Correct answer is mentioned in b2:b16
Student 1 answer is mentioned in D2:d16
Then,
Paste this formula in cell e2