Using Excel for grading and checking for cheaters...

I'm teaching a few courses as an adjunct and I use excel to grade my papers. A test, for example, may have 50 multiple-choice questions. So, I take the 50 answers and enter them as a string into a cell, like so:

BCBCBAAAABBBCADCDBAABCABDADBCBACDABCBDDDDCACCDABDC

I then use the MID() function to parse out single characters into a single row and 50 columns. In this example, row 3 has the numbers 1-50 in it so that I can use those values for the position I am trying to capture in that particular column, without having to type in the number for each individual column's formula.

=MID($BI4,C$3,1)

Then, I use an array formula to count the correct answers against the key (entered in row 1, same columns) and tally the score at the end of the row for each student. For example:

{=SUM(IF($C10:$AZ10=$C$1:$AZ$1,1,0))}

This works pretty good. It allows me to enter the answers very quickly, gives me the chance to do charting of the grades through a VLOOKUP, and some metrics on questions missed, etc. HOWEVER... what I'd reeeeeeallllly like to do is be able to evaluate the entries for similarity. For example, I had two guys sitting together whose answers were identical... correct AND missed. Well, you each get the same 38 right and miss the other 12 with identical wrong answers... uuhhhh, I think I know what's going on here.

Is there any way of doing analysis that would count how many of the characters were identical for any two students (all combinations, really)? So, let's say I have 30 students... I picture having a 30x30 grid with numbers indicating how many of the characters were identical... students names going down the left and across the top forming the grid, with the numbers in the matrix. ANY HELP IS GREATLY APPRECIATED.

Hi

See if the attached will help. I built some dummy data to replicate your situation (I hope), then just selected one of the entries to be the results and put them in row 1. So there will be at least 1 entry that is 100% right. I then used your formulas to get the results. I then sorted the data according to the results, so that all the lines that have the same result will appear together. Bit of a stretch, but got to start somewhere. That is the situation I've used as the base point in the attached file.

What I did then was take one of the results (B5) and copied it to B9. This way there is an entry that is 100% copy.

Run the macro bbb, and the results will appear in column BC. What this is doing is putting in formulas that compare the row to the row(s) below, where the result is the same, to see how many of the answers are a match. So when you run the macro, you will see that BF5 has the result of 50 - 100% match with this row and the row 4 down. Bit too good to be true.

I wouldn't say this is in any way definitive, but it may give you a pointer for further examination. I'd say a high correllation count with a reasonably low result would be, to me, suspicious. High correllation with high result would be expected.



rylo

rylo:

Thanks... I will take a look at that. You are right about the correlation. What I really need to look at are the incorrect answers, in two ways. First, is the pattern of missed answers the same (do they miss the same ones)? Second, are wrong answers individually the same (if a problem is missed by two people, did they enter the same wrong answer)?