Google Sheets: Functions for grading!

It is that time of the year when entering grades becomes the all-consuming activity for teachers everywhere. Since I am crazy about learning how to better use Google products to make life easier I thought I would share a few functions that have made grading easier for me. Excel provides similar functionality, but you will have to check the docs to figure out the syntactical differences.

Before I jump into the formulas, let me make sure everyone is aware of how to select data on a spreadsheet.

A1:D1 This will select all of the data in cells A1, B1, C1, and D1 and allow you to manipulate that data.

A very basic example of that would be: =AVERAGE(A1:D1) will provide you the average of the data in the selected range.

To take that a step further, we could select all the data in a column: =AVERAGE(A:A)

Or maybe we want a set of data from a different worksheet inside the file: =AVERAGE('Another worksheet'!A1:D1)

These functions can then be nested inside each other: =ROUNDUP(AVERAGE(A:A))

The functions purpose is usually pretty explicit from the name. Obviously, AVERAGE provides the average of a data range. ROUNDUP takes a number and rounds it up to the nearest integer.

That is a very basic explanation of how to select data in Google Sheets. Now onto the functions that I found helpful in my gradebook.

I want to take the average of the top 4 grades…

=ROUNDUP(AVERAGE(LARGE(A1:F1, 1), LARGE(A1:F1, 2), LARGE(A1:F1, 3), LARGE(A1:F1, 4)))

Looking at this formula from the inside out, first you see the LARGE(range, position) function. This takes the 1st, 2nd, 3rd, 4th values respectively from the range A1:F1. (Replace that range with whatever you want)

Then that is wrapped in the AVERAGE function, which is in turn wrapped in the ROUNDUP function. This will get an average, and roundup that average to the nearest integer. Make sense?

I want to count text fields as numerical values…

Sometimes I like to mark in my gradebook “Complete”, “Incomplete”, “Missing”, or “Absent”. When you use a function like AVERAGE or ROUNDUP then it completely ignores these text fields. But let’s say I want “Complete” to be equal to 100, “Incomplete” to be a 50, “Missing” to be a 0, and “Absent” to be ignored. For this we will need to create a custom function. Along the toolbar select ‘Tools’ > ‘Script editor’ and create a blank project.

Copy and past the code above into the blank ‘Code.gs’, save your work, and return to your spreadsheet. You now have access to a =AVGCOMPLETES(range) function that will perform the required tasks.

Hopefully this has given you an idea or two about how to better manage student data in Google Sheets. Post a comment if you have any questions.