I wrote about the start of my experiment at the beginning of this past school year. I think it went well. Having it as part of my Google Drive (we are a Google Apps school) meant it was always accessible, as well as mobile, I edited the sheet on my iOS app while walking around the room, grading assignments, and so froth.
Here's what I did...
Created a spreadsheet in Google Drive...
Made an individual sheet for each class...
Use the + icon on the bottom of your spreadsheet to create the new sheets within. Double slick on "Sheet 2" to rename it, as I did with class designations.
Created headers for each column...
- Student name - "last, first" format. I am able to download rosters as Excel files from a NYCDOE site and they come in that format. Makes it easier to create 20+ sheets if I can copy and paste as opposed to manually enter the data
- Student ID - included in the NYCDOE sheet and I need access to it for certain school-wide functions
- Seat - I fill it in when I assign seats, and as they change. IT's my seating chart but also helps me to learn all 600+ names of my students
- Column D+ - these are my daily attendence columns. An "A" goes in if a student is absent on a given day. Grades in range 0-4 for daily work (everyone starts with a 4, only lower value if student's aren't on task, violating rules, etc)
- Conduct - this is an average of all the daily grades. Done as =AVERAGE(D2:?2)
- subsequent columns - these are for individual assignment grades
- Work - =AVERAGE(cell range of given assignments) Depends on class how many there are
- Avg - this field averages the "Conduct" and "Work" cells for an overall average
- 1MPG/2MPG/3MPG - "marking period grade" and the corresponding term. This is the final, report card grade, for the given marking period (and after the 1st, gives a quick reference to the previous final grade)
No names or student IDs but data entered to show formula function. The 1MPG field is the final grade. The Avg field gives an average but on the report card we are only allowed to enter 1, 2, 3, or 4. The MPG
Here is a link to the actual file
With this setup I am able to keep track of attendence, seating arrangements, and assignmnets. In addition, by pre-loading the sheets with formulas I don't need to do any calculations, only grade entries.
The biggest set-up hurdles
The things that take the longest to get this set up are
- Creating a template sheet with formulas
- Downloading the roster files form the school site
- Copying and pasting the names and ID numbers
- Duplicating the sheets, giving them class names, and copying in related student info
I'd estimate it took 30-60 minutes in September to go from blank spreadsheet to fully populated with 22 classes, 600 students, and all formulas in place. For the 2nd marking period, it took less than 10 minutes to duplicated the file and clear the grade data to get it ready for the new marking period.
Come September I'll duplicate my "1MPG" file from last year, change the file name, and clear all the student data. Our classes should be unchanged, so I wont have to spend a lot of time on sheet names. I'm imaging 30 minutes to copy and paste the student information over, saving a lot of time not having to recreate the header formatting and formulas from scratch.
And with this I have an always accessible, mobile grade book and seating chart that auto-tabulates my grades. For the low, low price of free. Go Google...