Analyzing Student Data in a Spreadsheet

I use spreadsheets pretty frequently at work and have been intrigued by them for years.  Though all the tasks required for this assignment were already familiar to me, I found I rather enjoyed the Google Doc spreadsheet as I have only used Excel.  I found the Google manifestation of the spreadsheet very nicely laid out and easy to use.  My only challenge was in how to publish and post the worksheet and graphs, but thanks to Barry's tutorials and his later revision instructions to publishing, I was able to complete the task.

But, even old dogs can learn new tricks and though I knew how to create formulas, there's always more than one way to skin a cat.  I normally choose the formula from the Sum button on the Excel tool bar, but in Barry's tutorial I learned that you can just start typing right in the cell "=Med..." and the Median formula pops up.  So, that was handy.  I will add one little trick I know which Barry didn't share (maybe the student has one up on the master?) and that is auto-formatting column width.  Barry showed us how to size all columns at once by selecting them all and then dragging the edge of one column to the size that you want.  However, and I found this works in Google Docs as it does in Excel, after you have all columns highlighted, you can double-click the edge of one and all columns will automatically size around the data inside them so that the widest cell in that column sets the bar, so to speak, for the column.  You'll notice in my spreadsheet that my columns are different widths depending on the data inside.  Of course, one can also format the cells in the column to "wrap text" so that even if the column is more narrow than the data inside, the cell will cut off the data at column width and stretch the cell vertically to allow the text to continue just below, in the same cell.


I found charting in Google docs to be much easier than Excel and Barry's tutorial really explained well the simplest way to create charts. When I do this task for work, which I don't do often, I usually have a couple of attempts before I get the chart I want. Google Docs makes it a simpler process by walking you through collecting just the data you want analyzed in the chart.

Chart A - Sometimes, having your bar graph run horizontally versus vertically shows off your data even better, especially when you have more data in the y column than the x.  This shows that virtually all students improved, as expected, from Pre-test to Post-test except Joel.  I would assume Joel felt he knew the material well since he performed so well on the pre-test, and maybe didn't pay attention well during the subsequent lessons.  Joel also had less than perfect attendance so he may have missed the days when material he was less familiar with was covered.

Chart B - Here we see the percent increase Post-test over Pre-test.  Victor obviously had the largest gains and seeing as the median score was well below a failing grade, the class had lots of room for improvement.  This chart is rather skewed because of Victors, well, victory!  But the rest of the class all improved as can be seen by the median and average scores of the Post-test.  With an A average for the class, I would assume victory myself with this lesson.  I would remind Joel, however, that even when we think we know the material already, we need to pay attention and maybe take a bigger role in teaching others.  This will help him engage further with the material.

Chart C - The students generally performed well on all the Formatives.  Interestingly, all students percentage-wise scored the same Formative 2 vs Formative 3.  For example, KayLynn and Jordan both scored 80% on each.  There were a couple, Rut and Lauren who did quite well on Formative 1 but substantially worse on Formatives 2 and 3.  I would focus a bit more attention on those two after seeing the results of the second formative to make sure the material was "gelling" with them.  Everyone else either did equally well on all 3 or showed increase in score from 1 to 3.