Day 7: Working with data over time

In the previous lesson, we compared student performance across two tests. Often, we’ll have access to data that lets us see how students are trending over time. That leads us to looking at data “over a long time period,” often called “longitudinal” data.

Today, we’ll discuss how to interpret and graph longitudinal data. For example, our fictional student Jimi Hendrix from Lesson 1 will take state reading exams and benchmarks over a number of months and years. Longitudinal data help us understand whether Little Jimi is getting better, worse, or stuck on the treadmill each year.

Here are Jimi Hendrix’s reading test scores for the past five years:

First, let’s plot the data on a graph:

Now let’s follow our PEZ process from the last lesson (Parts, Eyes, Zoom—remember?) to interpret this data.

Parts:

  • The title confirms we’re looking at Jimi’s test scores. Okay, we knew that.
  • The x-axis is showing us how data changes every year between 2008 and 2012.
  • The y-axis shows us the score itself—but only within the range 480 to 520. It’s important to note that this axis doesn’t start at 0! Starting a graph at a number other than 0 exaggerates changes, so always be aware!

We know these particular scores are for Jimi Hendrix’s reading assessments. The scores are listed from oldest (2008) to most recent (2012). We can see trends in the data: Jimi’s performance decreases by seven points, increases by eleven, increases by eleven again, then falls by seven. At first glance, it appears like Jimi’s best score came in 2011 and his worst score came in 2009.

Digging deeper, we could say that Jimi’s greatest gains are between 2009-2010 and 2010-2011 while his biggest decreases were from 2008-2009 and 2011-2012. However, there is a better way to quantify this change over time. We’ll call this “percent change.”

Why is percent change important?

Let’s consider a trip to the grocery store. You have two items on your list: milk and lobster (who says dairy and seafood don’t mix?!). At the store, you find milk costs $3.29 per gallon while lobster is $18 per pound. You buy your customary gallon of milk and pound of lobster, happily pay the cashier, and go home to enjoy your creamy crustacean.

You return the next week to find milk has increased to $4.29 per gallon and lobster similarly increased to $19 per pound. “Jeepers,” you think to yourself, “how can I enjoy my seafood breakfast with these prices going through the roof?” After calming down a bit, you notice each item has increased by the same amount: $1. However, intuitively you understand that the milk price hike is a bigger deal. Milk started at a lower price, so that $1 is a more dramatic increase for milk than it is for lobster.

The same concept can be applied to test scores. If a student initially earns a 60% on a test and improves to a 70%, we as teachers would be over the moon with that type of improvement. If another student initially earns a 90% and improves to a 100%, we would also be over the moon. However, the first student has a more impressive percent gain because he or she started at a lower level.

We can calculate percent change in test scores using this formula:

percent change = (new score – original score) / original score

Exercise: Calculating percent change

Let’s calculate the percent change for each of Jimi Hendrix’s test scores. Download this CSV and open it in your spreadsheet software.

You can use a calculator for this exercise, or we can use the power of the spreadsheet to do it for us. Each cell in a spreadsheet is like your very own calculator!

Whenever you want the spreadsheet to calculate a formula, you need to double click an empty cell and first type the equal sign “=”. This allows the spreadsheet to understand that you are ready to flex your mathematical muscles.

The following formula will automatically calculate the percent change between 2008-2009:

=(E4-D4)/D4

But that displays a plain old integer, not a percent. Select the cell you just created and click the “%” button to display the calculation as a percent. You should see Jimi’s percent change from 2008 to 2009 displayed as “-1.41%”.

Complete the percent change for each of the other years (2009-2010, 2010-2011, and 2011-2012). Remember to please excuse your dear Aunt Sally by putting parentheses around the cells you are subtracting. Your table should look like this when finished:

I bolded the percent changes and colored the decreases with red font to make it look fancier. Small details like this will make your data jump right off the page and sing to you!

Although the increase from 2009-2010 and 2010-2011 were the same 11 points, 2009-2010 had a slightly higher percent increase proving that Jimi’s performance was more impressive that year. Equally 2008-2009 and 2011-2012 had scores that fell by the same 7 points, but 2008-2009 was a more dramatic decrease.

Finally, let’s practice graphing this data again. Remember, practice makes us perfect people (and hopefully better at graphing)!

#Takeaways

  • Longitudinal data is amazing for showing student growth over time.
  • Percent change is the best way to describe change in performance.
  • Unleash the calculators within a spreadsheet. Start the cell with the “=” sign!

Geeky Extensions

Instead of retyping the percent increase or decrease formula into each individual cell, you can tell the spreadsheet to read your mind. Place your mouse icon on the bottom right corner of the cell until a black cross appears. Next, drag the mouse to the adjacent cells. The spreadsheet will automatically copy your formulas. It’s like a digital Tarot Card reader!

Leave a Reply

Your email address will not be published. Required fields are marked *