Data analysis of the 10 test questions shows that half of the class scored 60% or lower, with two students scoring 30%, while the other half of the students scored in the 70-80% and one at 90%. It is possible that the lowest tier are students with disabilities. The weakest area for the class as a whole (based on one question) is in computing fractions and decimals, although the student who scored 90% also got this one wrong, so it might have been a badly worded question. The other 2 lowest scoring questions are in computing area and perimeter of polygons and identifying patterns.
The Google spreadsheet does allow for highlighting areas based on certain parameters, using the conditional formatting. I had used Excel quite a bit in the past and never noticed that this feature exists. I did have a learning curve on using Google spreadsheets as I had not used it before. I had to try entering formulas in Excel and then translate them into Google at first, but then got the hang of Google spreadsheets after a bit, but it is quite different. I used -1 to delineate incorrect answers, as I knew that Excel would display negative numbers as red, when formatted, but Google left the negatives in, which is okay. I did think the charting function was easier to use in Google, as it doesn't have all of the options Excel has so it is more straightforward.
I wanted to try Zoho, but didn't have time to learn how to use it (hmm...) and figure out how to compute the data, but would like to go back and try it later.
I have used Excel to compute grades and credit hours and could also use Google spreadsheets. I have used it with students to create graphs.
Your data organization looks great. With your If() formula, You could also tell the spreadsheet to spit back text. For example on your spreadsheet has this formula =if(B3=B$2, 0, -1), where you said -1 is referencing a wrong answer. You could substitute the 0 and the -1 with a "correct" or "incorrect", or simply use a "yes" or "no". It could look like this =If(B3=B$2,"yes","no").
ReplyDeleteI also like the idea of using that formula to sort students who got questions wrong. You could use the countif() formula to grab the text and count it to calculate a percentage for those who answered right or wrong. For example you could say =(12+(countif(b19:b30,"no"))/12. That would spit out your percentage of students who got the question wrong, along with the individual names pulled with your formula.
Yes, but there are so many variables you could use, Correct or Incorrect, Right or Wrong, etc. I chose to use the negative number because, as I said, I knew Excel would allow you to format negative #s in red and even record it as just a 1. I must have been thinking in binary that day :). But the conditional formatting worked well for that purpose, to highlight the incorrect responses.
ReplyDeleteI didn't go into detail here on how I sorted the data, as I thought people would look at the spreadsheet, but I did show the numbers of students per question in a chart. I know a bar graph is usually used for this type of data, but I liked the way the line graph showed the ups and downs of the questions, especially the one almost all of the students got wrong. I also did list the students that got each question wrong, below the listing of scores, using the formula =if(Bx=-1,$Ax,"") x being the line # and A being the column with the student name, so if they got it wrong, their name was added to the list, and if not it wasn't.
I really like the way you organized your data! This was all new to me and I've never really used Excel either, so I had a hard time figuring out how I wanted to show the data. I like the way you created the graph as a visual representation because it's easy to follow and would give teachers a good idea of their students progress quickly. I guess I need to play around some more with the different features because what you did was awesome! :)
ReplyDeleteCindy, I knew there were ways to graph (chart) the data, and I had a feeling you'd figure it out - I assumed that you'd have to convert the letters to numbers to do so, I just didn't have time once I got the hang of it to go that next step. I found it interesting that you found so much difference between Excel and Google - I hadn't realized that. It probably has something to do with the fact that I've only used the actual spreadsheet component of Excel, I've only gotten into the formulas and importing from spreadsheet to spreadsheet or even sheet to sheet in Google. What's changed that you find you don't use spreadsheets as much now?
ReplyDeleteGreat post,
Megan
Megan, I used to use Excel in my previous career, working in the technology industry as a materials planner and commodity manager. I did use an Excel spreadsheet to calculate grades and credit hours when I was teaching economics. Google spreadsheets is similar, but doesn't do everything Excel does, and I noticed a difference in how some formulas are worded, although they are similar. But I see how it can be overwhelming to learn to use another tool, when you are concerned about getting a task done. (Empathizing with the classroom teachers here :)
DeleteCindy - I really like how you used the Data Collection process and turned the student's results into a graph. As an educator, I often find looking at graphs to be beneficial and allow me to quickly analyze my students needs. Graphs do a great job providing a quick snapshot of information, and I think that how you utilized the graph did a great job. When looking at your graph I am able to see the areas that students need to improve on, and the area that students have mastered. How would you use the graph to create small groups?
ReplyDeleteThe graph doesn't really help with grouping students, but if you look at the data, below the student scores is a list of students by standard (in the standard columns) which shows which ones got the question wrong. It's pretty raw data, calculated from the scores, I could have color coded it to make it more apparent. Maybe I will!
DeleteVery through representation of the data. I agree, Excel has so much more functionality vs. google spreadsheets, but looks like you were able to make it work pretty well for the purpose of the exercise. I really like the chart by standard showing how the standards were met by the class as a whole. That is important data to know how you as a teacher are performing when teaching each standard. This is something that is important in a professional learning community where the teachers then work together to figure out who is best teaching each standard. They then offer support to the teachers who need assistance or they may coteach where a teacher is stronger in one area vs. another.
ReplyDelete