It has been a long time since I have written a post! It’s been an even longer time since I have written a post about what is going on in our teaching! It’s time to get back to what we are doing this year:
If you are a follower of this blog, you know that we teach in a vertical team across three buildings for 6-12th grade band. Over the past three years, that vertical team has put together a system of assessments that aligns to our power standards. We currently use Google Apps for Education to collect our data, but things have changed a bit for this year.
In the past, we had separate but identical Google Forms for each building. However, what we were finding was that teachers were making mistakes in regards to the form they were using, resulting in student data being collected “at the wrong building.” We did some digging into how we could use one form, but still separate the data by class period. This makes it easier to input grades and to find specific student data. Here are the different tabs in our Responses Sheet:
- Curve: sets the “weight” for each number from the rubric
- Form Responses 1: master dumping ground for all form responses
- Grade Period: specific tabs designed to pull specific data from the master responses tab
In the Form Responses 1 tab, I added extra columns that auto-populate with data based on the form responses. This is done using the arrayformula( function. This function replicates whatever is inside of it through multiple rows (and columns, if you like). Last year, we would copy and paste such formulas when it came time to grade. This year, the Google Sheet does it for us.
Inside the arrayformula( function is a vlookup( function. This compares the rubric score (1, 2, 3, 4, or N/A) from the form to the appropriate curve (73%, 83%, 93%, 100%, or N/A, respectively) and returns the correct percentage. For example, in cell Q2, I have the following formula:
=arrayformula( vlookup( F2:F, Curve!$A$2:$B$6 , 2) )
So what does each piece do?
- arrayformula( repeats the inside function(s) vertically through the entire sheet
- vlookup( compares the rubric score to the appropriate curve
- F2:F tells vlookup to look in column F beginning with cell F2 for the rubric score
- Curve!$A$2:$B$6 tells vlookup to look in the Curve sheet, specifically in cells A2 through B6. $ prevents the column letters or row numbers from increasing when pasting or repeating the formula
- 2 tells vlookup to return the corresponding percentage from the second column of the Curve sheet
Specific Band Spreadsheets
In the specific band period spreadsheets, I created the specific header information to correspond to the data I would pull from Form Responses 1. In the first column, I wanted the score that would be put in the grade book for each student. This is currently calculated by averaging all of their curved scores. Cell A2 looks like this:
=average( P2:X2 )
Columns P through X represent the different curved scores from their rubric. I had to copy and paste this formula all the way down the height of this spreadsheet. I haven’t yet been able to get the average( function to work within the arrayformula( function.
For the remaining columns of the spreadsheet, I want to pull specific information for each band. This took some digging. I adapted my work from this video from Google. Cell B2 looks like this:
=query( ‘Form Responses 1’!B2:Z , “Select * Where C = ‘4A: 6th A’ “)
The query( function runs specific code from Google based on your input.
- ‘Form Responses 1’!B2:Z looks across columns B through Z beginning in row 2
- “Select * Where C = ‘4A: 6th A’ “ selects (returns) all columns when the corresponding cell in column C = whatever is in the single quotation marks. In this case, it represents the specific class period and band the student is in.
I have created a test environment (free of our students’ data!) that you can use to experiment. You will only have viewing rights, but should be able to copy and paste formulas into your own Google Sheets.
Last Spring, I did some reflecting on our district’s philosophy for Best Practices Using Rubrics. Our vertical team decided to expand the rubrics, having more criteria to be assessed more accurately. We took our Wind Rubric and Percussion Rubric and expanded the options in the Google Form to the following:
|Wind Rubric||Percussion Rubric|
|Breath Support||Body Position|
|Tone Quality||Stick Motion|
We chose to keep the four levels of performance (exceeds standard, meets standards, making progress, and not making adequate progress), believing that the expanded criterion would allow for better delineation of achievement at specific skills, and thus, better data!
Today we began the first round of assessments, and other than few snafus getting the Form and Sheet up and running, things seem to be working really well! The snafus were expected: almost 600 students being assessed by 5 teachers across 3 buildings in 6 school days. It can be hairy, but it is awesome when it works!