Using Excel to match data?

[watkinsam]

I am a teacher and I am trying to find a way to organize data. Each Monday the students answer 5 multiple choice questions and I collect this data in a spreadsheet. Each question matches to a different "topic" we cover in class. With 5 questions it is very easy to see which student needs additional help in which topic but after 50 questions it is too difficult to track manually. My ultimate goal would be to populate a list for students of their weak topics after we have had answered over 50 multiple choice questions. Is there a way to use Excel to read their results and populate a list of which topics they need to study?

For example, Joe misses question 1, 9, 22, 49 and 50 -- Question 1 and 9 are about Cells and questions 22, 49, and 50 are about DNA. Can Excel read this and say Joe needs to review cells and DNA? I can see myself needing to assign topics to the columns where the question answers are.

Alternately, would it be easier to populate a list of students. Ex. Joe, Sarah and Tom need to review cells.

Before getting too much into how to do this, I was curious if it is something that can even be done.

[modytrane]

Can you attach a sample [without any personal information] of the spreadsheet you are using to collect data?
Then you can also create a tab to show how you would want to summarize this data.
It seems like perfect application for Excel. I am sure you'll get plenty of help once you post a spreadsheet.

[watkinsam]

Edit: I attached a small sample. I have been using Google Forms to collect the data and then copying it into Excel. I manually went through and marked which answers are incorrect. I added a second sheet that has the topics that I want to link the questions to. You'll notice right now I have two x's in S23 on the second sheet. This student missed the two Ecology questions. It would work better if I could get a count in this box.

[JeteMc]

If I understand what you are attempting, to find the subjects where the students need extra study, let me suggest that instead of marking using fill color, that you make a range that could be filled with some type of symbol (x) to indicate that a particular question has been answered incorrectly. Counting cells based on fill color will require VBA, whereas counting cells based on a symbol could be done using COUNTA. In the attached copy of your file a COUNTA function is used to count incorrect answers, then a percentage is calculated. Perhaps this will give you some ideas as to how you would like to proceed.
Let us know if you have any questions.

[watkinsam]

It would be easy to switch things over to an x instead of a color. I made some changes to my sheet and reattached. I need to be able to track individual progress once I move out of 5 questions and up to 50. I don't need the overall data as much. It doesn't matter so much that a student has gotten 70% of all the multiple choice questions correct. What I am interested to know is how many of the 30% they missed were questions on Ecology and how many of those questions were on DNA etc.

I imagine I am going to need to somehow link each of the questions to a topic.

[JeteMc]

If I am understanding correctly, this may be of some help. On the 'Topics' sheet the count of the number of questions missed is populated using the formula:

Please Login or Register  to view this content.

The zeros are hidden using a conditional formatting rule.
Let us know if you have any questions.

[watkinsam]

This is it . What I am trying to see now is how you tied the correct questions together. The next set of questions have the topics: Scientific Method, Chemistry of Life Water, Acids, and Bases, Chemistry of Life and Ecology etc. If I can understand how you linked them I will be able to continue on for all 50 questions.

[watkinsam]

Okay I think I have it. If I change the F to the last column I am using it looks like it works. Thanks!

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.