In Google Classroom you have the ability to give feedback, manage and grade student work all in one place. Teachers are able to give students a number grade and the total grade can be customised to your marking system. There is also the ability to copy all grades into a Google Sheet, allowing teachers to work with student data in powerful ways.
But what if you work off a grading system that is not number based? New Zealand teachers for example use curriculum levels (3b, 3a) or the NCEA based grades of N, A, M, E. The number based grading system in Classroom, therefore, becomes of no use. However, with a small amount of set-up in Google Sheets, you can quickly convert a number based grading system into your own letter based grades.Harness the power of the #Google Classroom grade book #GoogleClassroom #GoogleSheets Click To Tweet
Number grade alignment
Firstly, come up with a simple number code for your new system. For example, if you were using NCEA language you can allocate some plain numbers to each of the grades you use:
When you set up the grade total in Classroom, make it a total of 5. You might have more grade boundaries that you want to set up, in which case you would have a greater range of numbers.
Google Sheets setup
Now there is one more problem which makes this setup not as easy as it could be. In Classroom you can ‘copy a grade book’ however this makes a new Spreadsheet each time as opposed to syncing all grades to the same sheet. What would be great is a sheet that always updates with new grades. There is a Sheets add-on that can do this, but it is a paid subscription. To work around this you need to copy all new grades to a spreadsheet and then copy these to a master sheet. This master sheet can be on the first grade book copy you make.
Now for the slightly geeky part which I promise will be worth your while. Paste the following formula onto the second column in the master sheet (to the right of your first lot of grades).
In the formula change the numbers and characters to suit your newly setup grade system. Use the image below to help you with your changes.
Once this is done, drag the formula down the column to all the other students.
You can also copy it to every second column that way when your drop in new number grades it will quickly translate the grades for you.
Easily convert number grades to letter grades in Classroom #GoogleClassroom #GoogleSheets Click To Tweet
Now that you have your grade book setup you could also apply conditional formatting to help you with interpreting your data. If you are not yet using Google Classroom but would like to explore other ways of making Grade books then also check out this blog blog post Adrian Francis.