How can I count deviations between two rows?

  • Thread starter Thread starter J. Gary Ellison
  • Start date Start date
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.
 
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.
 
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)
Click to expand...

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
 
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
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.
Click to expand...

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.
 
Back
Top