Test results analysis

[Flightnurse]

I am in charge of reviewing multiple tests from around the country and
evaluating each question, each testing site, etc. I need to find an easy way with excel if possible to input the "answer key" in collum A then
each of the tests that I receive from the testing locations in collums B, C, D,..etc.
Then i need to evaluate how many attempts for the test, number of people who chose A, number who chose B, etc. This will help identify the
"bad" questions or the questions that need to be looked at.
This is how I currently have it started but not sure how to proceed.

Question# Key Student1 student2 student3
1 A A C B
2 B C B B
3 D D D D
4 A A A C


Any ideas or suggestions?

Thanks,
Doc

 

[Joe4]

If you cross-post across forums on this board, please be sure to provide links to the other post so that others know it may be being worked on in two different places.
http://www.mrexcel.com/forum/showthread.php?p=1603370#post1603370

 

[Smitty]

Welcome to the Board!

How about the % of correct answers:

Book1.xls
	A	B	C	D	E	F
1	Question#	Key	Student1	student2	student3	% Correct
2	1	A	A	C	B	33%
3	2	B	C	B	B	67%
4	3	D	D	D	D	100%
5	4	A	A	A	A	100%
Sheet1

Hope that helps,

 

[Flightnurse]

Smitty,
Thanks for the prompt reply. I am actually using the following cells,
Key is H6..H56
answers are in I6-AB6 through I56-AB56

I attempted to change your formula but I messed something up and I and it doesnt want to work.
I also need to add in the formula or the next cell over the number or % that choose A, B, C, D, etc.

I will be posting another question soon that relates to the people who took the test and their education/job.
Thanks again,
Doc

 

[Smitty]

So you'd just adjust the ranges to suit:

=COUNTIF(I6:AB6,H6)/COUNTA(I6:AB6)

And copy down.

 

[Flightnurse]

So you'd just adjust the ranges to suit:

=COUNTIF(I6:AB6,H6)/COUNTA(I6:AB6)

And copy down.

I think I have it now. Any ideas on showing the percentage who answered A or B or C when the answer was D?

 

[Smitty]

Appending a bit to my first example, you can look for each letter grade individually:

Book1.xls
	G	H	I	J
1	A	B	C	D
2	33.3%	33.3%	33.3%
3		66.7%	33.3%
4				100.0%
5	100.0%
Sheet1

 

[Flightnurse]

I am also trying to get the averages of G6:AD6 of the number of students who answered A,B,C or D. This need to be displayed in AD7,AE7,AF7, and AG7.

 

[Smitty]

Note: solved via PM with formulas above adjusted to the correct ranges.