Compound Time

Any instructor that spends any amount of time fighting with a grade-book can vouch for the truth that any method to save time when dealing with simple repetitive tasks is worth its weight in gold. Even something that costs you as little as 5 seconds per student per assignment can add up quickly. For example, if you take a course like the one I’m currently working on at ASU. A course of 48 students with 8 major assignments that require a rubric-like report. Copying and pasting may be a quick solution when moving back and forth between the LMS (Learning Management System) and a spreadsheet, but at a transaction cost of 5 seconds that’s 96 minutes over the course of the semester.

What then can a person do to save some time? This particular solution lies in the ability of spreadsheet applications like Google Spreadsheets and Excel to combine cell values using the “&” command and the carriage return character when writing formulas.


To make this work I started by looking at an existing grade-book and rubric for some established assignments. In this case I’m looking to write a formula that will take the values entered in the cells of this rubric and generate a report for each student that looks something like:

Graded by
Instructor name here

States Interests and Goals – points awarded
Identified Audience – points awarded
Offered Feedback – points awarded
Offered Feedback – points awarded

Total – points awarded / points possible

Any written feedback that’s given to the student here

In the example above, any italicized letters are values that are specific to each individual student and each assignment. In other words, I want the formula to package all of the relevant information into a single cell that I can easily copy and paste into a text window in the LMS.

To do this I need to know a few things about Excel / Google Spreadsheets. In these applications there are two methods of referencing cells that are important to keep in mind: relative references and absolute references.

Screenshot_082513_112147_PMRelative References are values that are expected to change. When you you copy and paste a formula that contains a relative reference, the reference changes in relation to where it’s pasted. For example let’s say that you write a simple formula like this:

This tells the spreadsheet application that you want to display the contents of cell A1 in your target cell. If you were to copy this formula to the cell directly below your original target cell, the formula would change to:

This is because your spreadsheet assumes that you want to maintain vertical continuity with the data in your cells.

Screenshot_082513_112249_PMAbsolute References are values that you want to freeze at a particular location. When you write an absolute reference you’re telling your spreadsheet that no matter where the formula is moved to, it should always reference the same cell. This can be especially handy if there are values that you know you want to point to in a formula that need to stay fixed. When creating absolute formulas you use the “$” symbol to specify which value you want to freeze (you can freeze just the row, the column, or both). In my case this looks like:

The other operator that we need to think about using when using our spreadsheet application is the “&” operator. This will allow us to pull in values from multiple cells and place them in the order we specify.

I started my formula the following way:

=”Graded by ” &J55&CHAR(133)CHAR(133)

This formula generates the line “Graded by Matt” followed by two carriage returns (CHAR(133) inserts a return in a cell). The next portion of the formula looks like:

&$D$2&” – “&D4&CHAR(133)

This line generates the line “States Interests and Goals – points awarded” followed by a carriage return.

If you take this same principle and repeat it for each column you end up with something like this:

=”Graded by “&J55&CHAR(133)&CHAR(133)&$D$2&” – “&D55&CHAR(133)&$E$2&” – “&E55&CHAR(133)&$F$2&” – “&F55&CHAR(133)&$G$2&” – “&G55&CHAR(133)&CHAR(133)&$H$2&” – “&H55&” / “&$H$3&CHAR(133)&CHAR(133)&”Comments”&CHAR(133)&K55

Which generates the following lines of text:

Graded by

States Interests and Goals –
Identified Audience –
Offered Feedback –
Offered Feedback –

Total – / 25


When you start to use the formula the magic really begins to happen, as you start to see things like this:

If we look at just the formulas we see this instead:

With a little bit of practice you can have a grade-book that generates reports as well as it keeps track of student progress.


Wait wait wait… how did you come up with 96 minutes per semester?
5 seconds x 48 students = 240 seconds per assignment
240x 12 assignments = 2880 seconds
2880 seconds / 60 seconds in a minute = 48 minutes
48 minutes per session x 2 sessions in the semester = 96 minutes per semester

1 comment

Comments are closed.

%d bloggers like this: