Teaching Integer Rules with Google Sheets - Addition

Integer operations confound middle schoolers everywhere. And understandably so. After all, imagine that everything you thought you knew about addition was suddenly turned on its head: numbers start moving the "wrong" way on the number line, adding two negatives gives you a smaller sum, and combining two integers with different signs can look a lot like what your teacher use to call subtraction.

A lot of the issue stems from students being given rules and memorizing them instead of discovering and making sense of them. Since math is the study of patterns, students need space to explore the patterns that happen when you combine positive and negative numbers.

Manipulatives like two-color counters and number lines are a pre-requisite to working with naked numbers. So after those experiences, I think Google Sheets provides some interesting possibilities.

I'm still working through the best way to do this, but a spreadsheet with pre-formatted functions seems like a good space for students to experiment with plugging in numbers, seeing the outcome, and deriving the patterns. It's sort of like a science lab for math: students input, observe, tweak, generalize, and then make meaning of their discoveries. In other words, they think like mathematicians.

Take a look at the Sheet below and let me know how it could be changed to help students understand integer addition. And if you'd like, make a copy for your Drive and mess around with it on your own.

How To Use It

1. If you're using Google Classroom, attach the Sheet to an assignment and set to "Every student gets a copy."


2. Students enter positive or negative integers in row 2, columns A and C. Anything positive will format green, negative as red to emphasize patterns. The sum will calculate automatically.
   
   

   

   Enter integers

   
   
3. Next they'll use the dropdowns in columns G-J to answer the questions about the signs of the addends. This emphasizes two things: first, if addends have the same sign, the sum will have the same sign as well. Secondly, if addends have different signs, the sum will have the sign of the number with the greater absolute value.
   
   

   

   Answer questions about equation

   
   
4. If the addends have different signs, columns K-M will be populated with the absolute value of the addends and the sum. Students need to think about how the absolute values of the addends are related to the absolute value of the sum, then select their answer from column N.





Absolute value relationships



5. Repeat steps 2-4 and fill in the remaining equations.


6. When students have completed the Sheet, have them discuss the patterns they observe with a partner (try these Accountable Talk posters from Education to the Core to get the conversation started).


7. Finally, have students go to the "Questions" sheet and explain their discoveries.





Reflection questions

Again, this is totally a work in progress and feedback would be great. I just think there's great potential in Sheets to help students experiment with math concepts and start thinking like mathematicians. It's definitely worth exploring.

Using Google Sheets to Track Student Progress

A teacher came to me last week looking for a way to quickly see the changes in her weekly student assessment data. She had been scoring each quiz, giving feedback to students, then calculating the percentage increase/decrease for each student by hand.

Clearly, the answer here is a spreadsheet. (Thanks, Alice Keeler, for burning that into my brain.)

The Google Sheet I created for her calculates the student's percent change from one week to the next, then highlights increases in green and decreases in red. It's a simple way to see how students are doing without having to bust out the calculator every Friday afternoon.

Enter scores, percent change automatically calculates

If you're interested in peeking under the hood at the functions and conditional formatting that make it run, I've listed them below the embedded Sheet.

Want to use it? Make your own copy and add it to your Drive by clicking here. Happy tracking!

Under the Hood

1. Finding the percent change
   In the Change column, the formula for finding the percentage is to take the current week minus the previous week, then divide it by the previous week. I used =sum(-C2,E2)/E2 to make that happen. I made the previous week's score negative so that if the second week's score is lower, it will return a negative value and show a decrease in the form of a negative percent change (as in a larger negative plus a smaller positive equals a negative). In retrospect, there's probably an easier way, but I'm still pretty new to spreadsheets and that's the integer rule that came to me in the moment.


2. Getting rid of "#DIV/0!"
   When there's no score for the previous week, the function above returns the ever-so-irritating "#DIV/0!" to remind you of what you've known since 2nd grade: you can't divide by zero. I didn't want that showing up in the change column for the weeks that hadn't happened yet, so I used the IFERROR function.
   
   IFERROR let's you decide what shows up in a cell if there is an error (clever, right?). To use it, surround the original function inside IFERROR parentheses, then after the comma in quotation marks, put what you want to show up instead of the error message. In this case, I wanted it to show up as "N/A". So the function in the Change column ends up looking like this: =IFERROR(sum(-C2,E2)/C2,"N/A"). In other words, if there's an error when the Sheet runs the sum function, just stick an "N/A" in that cell instead.


3. Conditional Formatting
   Finally, after the change in score is calculated, there needed to be a quick way to see if a student increased or decreased from the week before. Enter conditional formatting, which is located under Format > Conditional Formatting in the toolbar. Each cell in the change column has four conditional formats applied to it, in this order (conditional formatting is applied sequentially, so order matters):
   
   If the value is greater than or equal to 0, green background, black text
   Emphasizes, in green, an increase from the previous week to the current week.
   
   If the value is equal to -100%, white background, white text
   This hides an unfortunate byproduct of the function in the Change column which returns "-100%" if there isn't a value in the current week. The only way this formatting could be a problem is if a student gets a 100 one week and a 0 the next, the change won't show up. If you know another way around this, let me know.
   
   If the value is less than 0, red background, black text
   Emphasizes, in red, a decrease from the previous week to the current week.
   
   If the text contains "N/A", white background, grey text
   This makes the "N/A" from the IFERROR function less obtrusive when you're looking at the entire sheet and lets you focus more clearly on the data.