Game Changer: Spreadsheet Filters

In our last post, Margaret and I wrote about how much we are loving the feedback we’ve been getting from our students via weekly reflections.   Of course, good things can always get better, and, in this case, we felt that reflections could be improved by a more efficient workflow on our end.

When we started, we made a separate reflection form for each of our classes, with each form sending its responses to a separate spreadsheet, which we then checked on a weekly basis. which sent its responses to a separate spreadsheet.   But, I thought,

Why can’t I have all of the responses from all of my classes go to different pages in ONE spreadsheet.

There’s nothing I love more than efficiency (except maybe a good math pun) and I have a lot of faith in Google Drive, so I assumed this could be done and set to work.  At first, it looked like this was going to be a cake because I saw the New Sheet in an Exisiting Spreadhsheet option in the Choose Response Destination dialog box on Google Forms.

Perfect.  Perfect, that is, until I tried to send the responses from one form to the response spreadsheet for another form and got the following error message:

This spreadsheet is already the response destination for a form.

Tell me something I don’t know, right?  Stumped, I decided it was time to phone a friend, and she told me that this option is meant to let you send form responses to a pre-existing spreadsheet, NOT to let you send two sets of form responses to the same spreadsheet.  Sad, but, luckily, my tail of woe has a happy ending.  She also had a workaround, which involves using a filter in the response spreadsheet.

Here’s how it works:

1)  Created a new tab in the response spreadsheet for each category by which you want to filter responses.  I have a new sheet for each class.

2) In each of the new sheets, type the formula below A1.  (Later, I added and froze the header row to match the one that is automatically generated in the main Form Responses sheet.)

 =filter(‘Form Responses’!range;’Form Responses’!filter column:filter column =”desired value”)

What you have to change:

  • range:  the columns in the Form Responses sheet that you want to copy to the new sheet
  • filter column:  the column in the Form Responses sheet whose value determines which new spreadsheet the entry should be filtered to
  • filter column = “desired value”:  tells the filter what value in the filter column should trigger it being filtered into this particular sheet

I used =filter(‘Form Responses’!A:G;’Form Responses’!B:B=”B”)  in my form because 1) I have responses from the form in columns A:G, 2) want to use column B to filter the responses because that is the column with the letter of the period in it, and 3) this is my period B spreadsheet, so I want to copy entries that have B as the value in column B.

My response spreadsheet with example filter

Result:  one form, one organized response destination.  I love it!

Now that I know how to do it, I am filtering the responses to all of my forms!  It makes using the data so much easier!

Try it.  You’ll like it.


Weekly Reflections

One of the nice things about teaching in a 1:1 environment is that we can ask our students to submit homework, surveys, quizzes, etc. online. We are using this to our advantage by asking our students (both in our PreCalc classes and Margaret’s Geometry class) to submit weekly reflections by Saturday at midnight.  We created a Google Form where each student enters his first name, last name, email address, date of the Friday of that week, and a reflection based on the following:

What did you struggle with this week? What did you find easy this week? What do you need more help with? What are you confused about? Are there any other situations going on in your life that I should know about? The content of your response will not be shared with other students.

The purpose of this ongoing assignment is two-fold.  First, it gives our students an opportunity to reflect on their progress made during the week and evaluate the way they need to approach the week ahead (should they seek extra help, ask more questions in class, etc.).  Second, it gives us the opportunity to hear how each student in our classes is doing at least once a week, and it informs our choices about how we begin our classes on Monday.

So far, Margaret has been able to email students who indicate that they are struggling with a concept and set up a time to provide extra help (before or after school).

Lia’s PreCalc students are mostly working on review so far, so their reflections have been mainly positive.  However, a few students have taken advantage of the opportunity to ask specific questions that Lia has been able to address with the students one-on-one, usually by reply e-mail.

Algebra I Boot Camp

At the same time that our school was preparing for a 1:1 initiative last year, our math department was also preparing to revamp our curriculum to be more closely aligned to the Common Core.  As part of this effort, we decided to get of our old textbooks and ultimately chose to switch to an Algebra 1, Geometry, and Algebra 2 series built for the iPad.

I teach a regular level, college prep Algebra 1 class, and the other teachers who teach this course with me decided that we needed to review some middle school skills (that used to part of our high school Algebra 1 curriculum) before we actually started the topics in the text.  I usually flip my Algebra classes anyway, but this is the first time that I have done it within the 1:1 environment.  I decided to use the review unit to try a couple of new things:  approximating the idea of a flipped mastery classroom and using some elements of game design.  I’ve heard that gamification is, to a certain extent, all about what you call things, so here’s a basic outline of Algebra 1 Boot Camp:

1.  I created a series of short videos on each topic to be covered and posted them as a playlist on my YouTube channel.

2.  I created a set of problems that corresponded to each video.

3.  I created quizzes on certain groups of topics and a test on the whole unit and set dates for each quiz and test to be taken.

4.  I gave students a Boot Camp Manual (check it out on the Raid Our Files page) that outlined the work that they needed to complete before each assessment date.  Each evening and day in class, I present certain videos that they should be watching and problem sets that they should be working on, but there is a certain extent to which they can work at their own pace.  If they don’t finish their problems in class, they can finish them at night before we go over them in class the next day.  If they finish their problems early, they can just start watching their next video in class (headphones were on my list of required materials this year!)

5.  Each time students pass one of the intermediate quizzes, they get promoted to a new level – Arithmetic Master, Variable Whiz, etc.

6.  I posted progress charts on the back wall of my classroom.  Each time my students finish a numbered section of work in their Boot Camp manual and/or move to a new level, they are able to put a start on the chart.  So far, they love it.  The stars are little thing they help them to see the fruits of their accomplishments.

Progress charts – Do you have enough stars to level up?

For laughs:  The funniest result of Boot Camp so far is that my juniors, who are taking an honors pre-calculus class and do not have star charts, wanted to know if we could come up with some charts for their class, too.

It would be so much more satisfying to do well if we got stars for it!

iPads as Notebooks — Part 2

The Tools

In the first part of this post, I talked about why I decided to use iPad as notebooks in some of my math classes.  Now, how exactly does that work in practice?

My school required all students to purchase the note-taking app Notability along with their iPads this year, which I was thrilled about because it is probably one of my favorite and most used apps. Over the course of the first few days of school, I intentionally included activities in my lesson plans that gave me the chance to show my students how to do the following:

1. Organize their notes for different classes and chapters using the Divider and Subject features. Because Notability supports typing, handwriting, and importing documents from the web or cloud storage accounts, it is flexible enough to work for any subject.

2. Be smart about naming “notes” (using section numbers, etc) for each organization and searching.

3.  Use the ZoomBox and wrist guard features to write math neatly.

The Zoom Box makes it so easy to write intricate things neatly!

The Zoom Box makes it so easy to write intricate things neatly!

4. Import PDFs that I post on our class page on Edline, my school’s learning management system. These can be:

  • Templates for notes. I typically create lesson skeletons in SMART notebook, print them as PDFs, and post them for students. I think this helps them to pay attention more in class because they have to spend less time copying things like diagrams and problems down. It’s easy for me to do and a huge time saver for the kiddos.
  • Problem sets for class work and homework.  When I don’t want to assign problems from the textbook, I can easily distribute digital problem sets to students. Some benefits of completing them in Notability rather than as paper worksheets are the availability of colors, the ability to erase really easily, and the fact that I can leave lots of space for them to work in between problems without worrying about fitting everything on one sheet or, in general, using too much paper.

5. Access solutions to problem sets and attach them to original work. Notability makes it very easy to merge documents by using the “Add to Note” feature when you import a file.  Prior to this year, Margaret and I were both in the habit of writing out solutions (using Notability, of course), exporting them to DropBox or Google Drive, and them posting them on Edline.  Margaret had the brilliant idea (I wish I could take credit for this one!) of having students add our solutions to their work so that they can easily scroll back and forth to check their work.  We’re getting lots of positive feedback from the students about being able to do this!

With two weeks under our belts, I can truthfully say:  so far, so good for Notability!  The only hiccup we have experienced is when we are planning on having students import PDFs as part of the day’s activities and, suddenly, the wireless network has been down.  If I can get organized enough down the road, I think it would be a good idea to have students import the PDFs they need for class the next day as part of the previous night’s homework.

Google It!

How many times a day do you turn to Google Search for an answer, clarification, or more information?  I do it all the time and even had my whole Algebra 1 class hunting for information on the Hope Diamond today!  It all started when I gave my students this information and asked them to come up with the question:

In 1673, the Hope Diamond decreased in size by about 45 carats, resulting in a 67 carat diamond.  

I thought we were solving a simple word problem but I quickly realized that they didn’t even know what the word problem was about.  While some of the students asked what the original weight of the diamond was, many of them were asking what happened in 1673?!  Others were asking what the Hope Diamond is.  One student asked what a carat meant.  With 15 minutes left in the class, we quickly solved for the original weight of the diamond, and then turned to Google to find the answers to all of their other questions.

I created a Google Document, shared it with the class, and had each student editing the document from his/her iPad.  They were reading articles from the Smithsonian and PBS (in math class!).  They were finding pictures and posting links in our Google Doc.  Some students could hardly contain their excitement to learn about the curse that haunts those who touch the Hope Diamond!

I surprised myself today with how quickly I was able to turn to our 1:1 program as a way to engage my students with something I had not planned ahead.  But really, what’s the point in solving a problem about the Hope Diamond if you don’t get to learn about the Hope Diamond? 

Google Drive + Screenshots = Faster Feedback

When my students do their math homework and struggle with a problem, they often wait until the next day to ask me about it.  Occasionally they will email me with a jumbled message in an attempt to type algebra.  While these are not terrible options, Google Drive provides an even better (and faster) way for students to get help with a problem.  Now that my students are doing their homework on their iPads, this process is even easier!

When a student has a question about his work, he can take a screenshot on his iPad of his attempt at the problem in Notabililty, save the image to his Google Drive, and share the image with me (and preferably add a message asking a question about the problem).

When I get an email notification that a student has shared an image with me, I can give him feedback quickly by opening the image in Google Drive and inserting text comments on different portions of the image.  He will see these comments immediately.

For Student Directions and Teacher Directions, check out our Raid Our Files page.

Using Apps in Class

If you need your students to download an app, have them do it at home the night before class. Having a whole class download something from the App Store at the same time that the other 1000+ people in the building are accessing the Internet at the beginning of a given class period just doesn’t work (unless you have a really awesome wireless network).