Using Google Sheets to Track Student Progress

A teacher came to me last week looking for a way to quickly see the changes in her weekly student assessment data. She had been scoring each quiz, giving feedback to students, then calculating the percentage increase/decrease for each student by hand.

Clearly, the answer here is a spreadsheet. (Thanks, Alice Keeler, for burning that into my brain.)

The Google Sheet I created for her calculates the student's percent change from one week to the next, then highlights increases in green and decreases in red. It's a simple way to see how students are doing without having to bust out the calculator every Friday afternoon.

Enter scores, percent change automatically calculates

If you're interested in peeking under the hood at the functions and conditional formatting that make it run, I've listed them below the embedded Sheet.

Want to use it? Make your own copy and add it to your Drive by clicking here. Happy tracking!

Under the Hood

1. Finding the percent change
   In the Change column, the formula for finding the percentage is to take the current week minus the previous week, then divide it by the previous week. I used =sum(-C2,E2)/E2 to make that happen. I made the previous week's score negative so that if the second week's score is lower, it will return a negative value and show a decrease in the form of a negative percent change (as in a larger negative plus a smaller positive equals a negative). In retrospect, there's probably an easier way, but I'm still pretty new to spreadsheets and that's the integer rule that came to me in the moment.


2. Getting rid of "#DIV/0!"
   When there's no score for the previous week, the function above returns the ever-so-irritating "#DIV/0!" to remind you of what you've known since 2nd grade: you can't divide by zero. I didn't want that showing up in the change column for the weeks that hadn't happened yet, so I used the IFERROR function.
   
   IFERROR let's you decide what shows up in a cell if there is an error (clever, right?). To use it, surround the original function inside IFERROR parentheses, then after the comma in quotation marks, put what you want to show up instead of the error message. In this case, I wanted it to show up as "N/A". So the function in the Change column ends up looking like this: =IFERROR(sum(-C2,E2)/C2,"N/A"). In other words, if there's an error when the Sheet runs the sum function, just stick an "N/A" in that cell instead.


3. Conditional Formatting
   Finally, after the change in score is calculated, there needed to be a quick way to see if a student increased or decreased from the week before. Enter conditional formatting, which is located under Format > Conditional Formatting in the toolbar. Each cell in the change column has four conditional formats applied to it, in this order (conditional formatting is applied sequentially, so order matters):
   
   If the value is greater than or equal to 0, green background, black text
   Emphasizes, in green, an increase from the previous week to the current week.
   
   If the value is equal to -100%, white background, white text
   This hides an unfortunate byproduct of the function in the Change column which returns "-100%" if there isn't a value in the current week. The only way this formatting could be a problem is if a student gets a 100 one week and a 0 the next, the change won't show up. If you know another way around this, let me know.
   
   If the value is less than 0, red background, black text
   Emphasizes, in red, a decrease from the previous week to the current week.
   
   If the text contains "N/A", white background, grey text
   This makes the "N/A" from the IFERROR function less obtrusive when you're looking at the entire sheet and lets you focus more clearly on the data.