This week we used spreadsheets and raw data from student tests to analyze trends in testing. I have to admit I have no background with spreadsheets or using the formulas to understand data, and I found this task incredibly daunting.
Ahhhh is it a mountain or a molehill???
Fortunately, we had some great tutorials to teach us about some of the basic calculations we could do in a spreadsheet, and the mountain was definitely surmountable. Not only did I become more comfortable with the idea of using spreadsheets (thank goodness! Especially because they seem essential when it comes to tracking student assignments and tests), but I am actually really looking forward to using them when I teach. AND we calculated students' averages, the percent of their growth, and created graphs--all of which are immensely helpful when it comes to analyzing student performance.
Here is a link to the spreadsheet. You will see there are four different "sheets" at the bottom. You can select the spreadsheet with all of my calculations; the graph of pre-test v post-test percentages; the graph of the percentages on Formatives 1 through 3; the graph of percent of growth; and a sheet with the raw data itself.
Our assignment required us to copy raw data from a table and insert it into our own spreadsheet. After that, I formatted my table (making some columns narrower and such). Then I calculated the pre-test percentage, the post-test percentage, and the percent of growth. The calculations themselves were quite simple, and the handy trick of selecting the little blue square in the bottom of the cell and dragging it to the remaining cells in the same column for which I wanted the same calculations (e.g. the pre-test percentage for each student) made completing the calculations much breezier than expected.
After calculating the three percentages mentioned above for each student, I used simple formulas in the spreadsheet program to calculate the class averages in each of those categories, as well as the median and the mode.
I then added columns so that I would have a place on my spreadsheet for the percentages the students scored on the Formatives. I then calculated the percentages for each student, as well as the average, median, and mode for the class.
Our assignment required us to copy raw data from a table and insert it into our own spreadsheet. After that, I formatted my table (making some columns narrower and such). Then I calculated the pre-test percentage, the post-test percentage, and the percent of growth. The calculations themselves were quite simple, and the handy trick of selecting the little blue square in the bottom of the cell and dragging it to the remaining cells in the same column for which I wanted the same calculations (e.g. the pre-test percentage for each student) made completing the calculations much breezier than expected.
After calculating the three percentages mentioned above for each student, I used simple formulas in the spreadsheet program to calculate the class averages in each of those categories, as well as the median and the mode.
I then added columns so that I would have a place on my spreadsheet for the percentages the students scored on the Formatives. I then calculated the percentages for each student, as well as the average, median, and mode for the class.
Here is what my final Spreadsheet looked like:
Finally, I used the data from the calculations in the spreadsheet to graph three different trends. This created a great visual that should be quite helpful when it comes to interpreting the data and student achievement.
The first graph represents the Students' Pre-Test Percentage versus the Post-Test Percentage.
The first graph represents the Students' Pre-Test Percentage versus the Post-Test Percentage.
As you can see in the above graph, 12 out of the 13 students showed improvement, some more than others. Five students even managed to get 100% on the post-test. Joel's percentage is the only one to decrease from the pre-test to the post-test, but not too drastically.
The next graph represents the students' percent of growth from the pre-test to the post-test.
The next graph represents the students' percent of growth from the pre-test to the post-test.
As you can see in the graph above, Victor's score increased drastically between the pre-test and post-test (1800%! From 1/26 raw score to a 19/26 raw score!). Again, 12 out of the 13 students' scores clearly increased, and Joel's decreased only slightly.
The final graph depicts the percentages the students scored on Formatives 1 through 3.
As you can see in the graph above, 7 of the 13 students had the same percentage on each of their individual Formatives. Additionally, Joel scored 70% on all three of his, with no student scoring above 80% on a single Formative.
So, what does all of this data mean???
As mentioned before, 12 out of the 13 students in this class improved their scores and had a percent of growth. This is fantastic. Joel is the one exception. Why? It's hard to tell. Perhaps his attendance had an effect on his score (he has the lowest attendance out of all the students with attendance 8 out of 10 days), but it's hard to know. He is also the only student in the set in 12th grade. Maybe he had senioritis?
Why do we care about this data?
Looking at this data and these scores, I would have to say results on the Formatives are not necessarily indicative of final post-test scores, but the repetition of the Formatives may be helpful. For instance, Rut's percent of growth was about 167%, but he actually did worse on Formatives 2 and 3 than he did on Formative 1. Additionally, Joel did fairly well on all three of his Formatives (70%), but his post-test percentage went down. Finally, the class average increased between Formative 1 and 2, but was the exact same between Formative 2 and 3. I would maybe only have my students take 2.
As a classroom instructor looking at these scores, I would encourage my students to stick with it and not be discouraged. Look at Victor! I would tell them to try their best, and to make all of their classes. The class as a whole had an average percent of growth of 239%, and that is inspiring.
As mentioned before, 12 out of the 13 students in this class improved their scores and had a percent of growth. This is fantastic. Joel is the one exception. Why? It's hard to tell. Perhaps his attendance had an effect on his score (he has the lowest attendance out of all the students with attendance 8 out of 10 days), but it's hard to know. He is also the only student in the set in 12th grade. Maybe he had senioritis?
Why do we care about this data?
Looking at this data and these scores, I would have to say results on the Formatives are not necessarily indicative of final post-test scores, but the repetition of the Formatives may be helpful. For instance, Rut's percent of growth was about 167%, but he actually did worse on Formatives 2 and 3 than he did on Formative 1. Additionally, Joel did fairly well on all three of his Formatives (70%), but his post-test percentage went down. Finally, the class average increased between Formative 1 and 2, but was the exact same between Formative 2 and 3. I would maybe only have my students take 2.
As a classroom instructor looking at these scores, I would encourage my students to stick with it and not be discouraged. Look at Victor! I would tell them to try their best, and to make all of their classes. The class as a whole had an average percent of growth of 239%, and that is inspiring.
Mountain or molehill. A mountain, and you made the summit! Congrats.
ReplyDeleteGlad the tutorials were helpful and you were able to gain some confidence and competence is manipulating, charting and analyzing a set of student data. Thanks for taking time to focus on the most important part --- that of analyzing the results. I'm certain you will find the use of these newly acquired skills beneficial in your work samples for Practicum and Student teaching.