Friday, April 24, 2020

Reporting "completion" grades - use IF statements


Much like other districts around the U.S., mine has opted to forgo grades and simply report work as completed/not completed during this time of emergency remote learning. While I agree with this practice for now, it dawned on me that I would be looking at papers or digital submissions twice as many times as before since. Not only do I need to read and provide feedback to students (oftentimes grading it still), I also need to contemplate whether a submission counts as completed or not. I am not averse to doing the former, but the idea of then manually"transforming" that into complete/not complete in order to report it in my LMS, especially when students are all working at different paces, is a real pain. Trying to ease that pain I started thinking of all those skills I've gained from my gamified leaderboards and came up with a relatively easy workflow that for all intents and purposes semi-automates the process.

It all starts with reporting all feedback grading uniformly. I am using Google classroom as my feedback grade book for students, whether manually grading there or importing into it the feedback grading from GoFormative, EdPuzzle, etc., the trick is to make everything worth the same number of points. Once you are ready to report the C/NC grades you will need to download the complete grade book you want to transform as a CSV. In Google classroom, this means going into any of your assignments and clicking on the cog you see on the top right.


Once you have your grade spreadsheet, it is simply a matter of adding an IF statement that references the cell you'd like to change into C/NC. The formula is

=IF(E6>6,"C", IF(E6<=6, "NC"))

Where E6 is the cell that has the grade, and 6 is the points threshold that I decided on as the lower limit of "completeness". C and NC can be whatever you want it to report out.

Once you have that formula in place, it is simply a matter of dragging it down and across to copy it. Both Google sheets or Excell will automatically change the cell references.


Once that is done, your spreadsheet is ready to be uploaded to whichever LMS you are using. While you will have to do it again any time you update your grades, it will at least save you some time and keystrokes.

Have you found any other shortcuts to deal with this new normal? I'd love to hear about them.

No comments:

Post a Comment