I have an excel question and am not sure how to put it to google (now realising GCSE IT would have been more use than GCSE Music) so thanks in advance to any STWer that helps.

I have a pile of exam papers to mark and part of the paper is multiple choice, I want a quickish / reliable way to catch those that copied their neighbour.

I plan to use a column for each question with a 1 or 0 depending on whether the answer is correct or incorrect.

My competence (none) in the program at the moment doesn’t exceed the use of autofilters and very basic calculations.

I need to look at incorrect answers given and then whether the matching combos were given by people sitting near to each other (I have seating plans). To make it trickier a couple questions can be answered correctly by selecting more than one answer.

Cheers
Rob

nice problem.

I would make a second lookup table that allows you to define student proximity (i.e. give student seating locations a number and then allocate say 1 or 2 numbers of the other proximate students – row neighbours) then you can use conditional formatting to highlight when answers are coincidental with proximity. Id also augment that analysis with a correlation coefficiency between locations and proximate locations to see if there’s a trend to the same answers across the whole exam..

But thats all a bit more complex than your confessed ability will cope with Im afraid.

I wouldnt use excel. If yu know your students then you know who’s likely to do well and who isnt. If one of the crapper students do better than expected then compare their answers to their neigbours. Quicker, easier and more accurate I think.

Wot Stoner sez, I fink.

simplest way i can think of, create a grid question numbers across top and then as Stoner suggests assign a number according to the seating plan, but use these as row headers, If you then limit each cell within this grid to the answer.

When you say multichoice do you mean select 1 of 4 answers? if so set each cell to either 1,2,3 or 4, would be more reliable than a simple right/wrong if there were more than 2 answers.

I could bodge something if you want, need something to do to fill time whilst job hunting.

A 1/0 flag is a bit of a blunt instrument in that it will only identify where correct answers have possibly been copied.

For example, say you have students Alice and Bob who sit together (and assuming you have a formula to define “proximity”). Question 1 has a correct answer of “C” (assuming “A” to “D” to be possible answers). If they both get this one correct, do you assume copying? Probably not.

If they both score 20/20, do you assume copying? Possibly, though it depends on your suspicions.

If they both get 14/20? And they get the same 6 incorrect? Hmmm – looks fishy. However, you have no way of your spreadsheet telling you if they made the SAME incorrect guesses for each of the 6 questions just by using a correct/incorrect flag.

The possibility of more than one correct answer for some questions makes it even more complex.

Interesting one…

Wot Mackem sed coz the otherz hav lost me.

Yeah – wot mackem said.

My post sounded right in my head but I can’t understand it myself after only 5 minutes.

Wot stuartie c sed. Now Im reely confussed.

won’t you be looking for the same answers whether they are right or wrong. If they are copying is a they should be the same either way?
As Mackem said past history is probably a better indicator. Any significant improvement and same answers in blocks or next desks may give the answer.
Blooming hard to prove though

Hmm, you could allocate a value (1-4) to each of the possible answers (a-D)

then put each persons responses in a column.

then create a 2 way table on a different sheet and calculate the correlation for each pairing, this will give you places where 2 people put down the same results, of course, this will also include correct answers and so 2 bright kids will give you high correlation.

To compare any 2 individuals, put the correct answers in column A, Person 1’s answers in column B, Person 2’s answers in C.

then put this in column D =IF(AND(B1=A1,B1=C1),0,1)

and drag down

This means every time they agree on a wrong answer you get a 1.

Total that column and divide by the number of wrong answers

just had a little play in Excel, what i did was create two grids, first is as above a grid comprising answer number and student position. then in the second grid i have the formula if(or(b2=c2,b2=a2),”match”,””) this is then dragged down and across. If two neighbouring cells have the same value it is flagged. You can then have a final column counting the number of matches.

for future tests, why not mix up the order of question on the papers – have maybe 4or5 versions in use ?

(and no, I don’t know how to do it in Excel 😳 )

Assuming you know who was sat next/ was capable of copying who, and that you’ve got to manually enter each students answers anyway…

Set up a table od students answers… students across the top in order as they were sat, questions down the side.

Then just look at it… inspect it manually, like this 😯 , you will quickly see any pattern / correlation in their answers.
Use some conditional formattin to colour code cells based on answer if you must.

Woah! Thanks, that’s more replies than I was expecting; STW’s great.

Stoner – your answer makes my bum do 5p50p far more than any downhill track I’ve had the pleasure of riding! However, you gave me some terms that will help with the Google search. I have a week to do this before I go travelling so hopefully time is on my side.

Mackem – Sadly I only know about 10% (mostly the confident ones) of my students by name and nonsense beyond my control in this first semester at a new college means I have probably only seen each class 12 times. Class sizes are around 50 and while absolutely trying to avoid sweeping generalisations, as a westerner I have trouble distinguishing between students given that of my 350ish there are only three guys.

don simon – thanks

mrmo – Thanks for your kind offer, I’ll pass at the moment as I’m not looking to burden anyone with my woes and if possible would like to learn how to do it myself. For the multichoice two of my fourteen questions were “choose all that apply” and the rest were “choose one” all questions had four answers to choose from. Good luck with the job hunt.

stuartie_c – the 1/0 thing is pretty flawed I agree. The same correct and incorrect choices are the only safe measure I think and it’s probably the case I’ve bitten off more than I can chew.

ian-r – correct, looking at combinations of right or wrong answers and perhaps even the oddball wrong answers if they occurred repeatedly in a row of students, I noticed this a couple times while mincing the class during the exam. Past history is non existent, I have asked for grade sheets submitted by their previous teachers and the college can’t help, my own shortcomings and limited contact with the students doesn’t help. Don’t worry about proof chap, I’m in a country in which a past leader was responsible for 70m deaths but is considered a saint.

CharlieMungus – I’ll do a 2 way table Google search now, I won’t be going solely on what excel throws up but need it to narrow things down. Thanks for your tips and formula; it certainly gives me something to work with.

mrmo – your playing sounds promising, thanks a lot.

scaredypants – I thought about that but it’s predominantly an oral / aural class so I chose to read an article for them to answer the questions on and mixed papers would have made it too hard for them.

The Southern Yeti – I know who had the potential to copy who and will be manually entering data. Your slightly lower tech approach is something I’d certainly thought about and may be what I end up doing and a bit of colour to spice things up might help with wood from trees.

don simon – thanks

One tries one’s best.