Computational Thinking & Spreadsheets

I feel sorry for the way spreadsheets are used most of the time in school. They are usually used as nothing more than a waypoint on the way to a chart or graph, inevitably with one of its data sets labeled ‘Series 1’. The most powerful uses of spreadsheets come from how they provide ways to organize and calculate easily.

I’ve observed a couple things about the problem solving process among students in both math and science.

• Physics students see the step of writing out all of the information as an arbitrary requirement of physics teachers, not necessarily as part of the solution process. As a result, it is often one of the first steps to disappear.
• In math, students solving non-routine problems like Three Act problems often have calculations scrawled all over the place. Even they are written in an organized way, in the event that a calculation is made incorrectly, any sets of calculations that are made must be made again. This can be infuriating to students that might be marginally interested in finding an answer in the first place.
• Showing calculations in a hand written document is easy – doing so in a document that is to be shared electronically is more difficult. There are also different times when you want to see how the calculation was made, and other times that you want to see the results. These are often presented in different parts of a report (body vs. appendix) but in a digital document, this isn’t entirely necessary.

Here’s my model for how a spreadsheet can address some of these issues:

Why I like it:

• The student puts all of the given information at the top. This information may be important or used for subsequent calculations, or not. It minimally has all of the information used to solve a problem in one place.
• The coloring scheme makes clear what is given and what is being being calculated.
• The units column is a constant reminder that numbers usually have units. In my template, this column is left justified so that the units appear immediately to the right of the numerical column.
• Many students aren’t comfortable exploring a concept algebraically. By making calculations that might be useful easy to make and well organized, this sets students up for a more playful approach to figuring things out.
• Showing work is easy in a spreadsheet – look at the formulas. Depending on your own expectations, you might ask for more or less detail in the description column.

Some caveats:

• A hand calculation should be done by someone to confirm the numbers generated by the spreadsheet are what they should be. This could be a set of test data provided by the teacher, or part of the initial exploration of a concept. Confirming that a calculation is being done correctly is an important step of trusting (but verifying, to quote Reagan for some reason) the computer to make the calculations so that attention can be focused on figuring out what the numbers mean.
• It does take a bit of time to teach how to enter a formula into a spreadsheet. Don’t turn it into a lecture about absolute or relative addressing, or about rows and columns and which is which – this will come with practice. Show how numbers in scientific notation look, and demonstrate how to get a value placed in another cell. Get straight into making calculations happen among your students and in a way that is immediately relevant to what you are trying to do. Then change a given value, and watch the students nod when all of the values in the sheet change immediately.
• Building off of what I just said, don’t jump to a spreadsheet for a situation just to do it. The structure and order should justify itself. Big numbers, nasty numbers, lots of calculations, or lots of given information to keep track of are the minimum for establishing this from the start as a tool to help do other things, not an end in and of itself.
• Do not NOT
  

  NOT

  hand your students a spreadsheet that calculates everything for them. If a student wants to make a spreadsheet for a particular type of calculation, that’s great. That’s the student recognizing that such a tool would be useful, and making the effort to do this. If you hand them a calculator for one specific application, it perpetuates the idea among students that they have to wait for someone else that knows better than them to give them the tool to use. Students should have the ability to make their own utilities, and this is one way to do it.

Example from class yesterday:

We are exploring the way Newton’s Law of Gravitation is used. I asked students to calculate the force of gravity from different planets in the solar system pulling on a 65 kilogram person on Earth, with Wolfram Alpha as the source of data. Each of them used a scientific or graphing calculator to calculate their numbers, with the numbers they used written by hand (without units) on their papers with minimal consistency. They grumbled about the sizes of the numbers. When noticeable differences arose in magnitude between different students, they checked each other until they were satisfied.

I then showed them how to take the pieces of data they found and put them in the spreadsheet in the way I described above. In red, I highlighted the calculation for the magnitude of the force for an object on Earth, and then asked a student to give me her data. This was the value she calculated! I was quickly able to confirm the values that the other students also had made.

I then had them calculate the weight of an object on Earth’s surface using Newton’s law of gravitation. This sent them again on a search for data on Earth’s vital statistics. They were surprised to see that this value was really close to the accepted value for g = $latex 9.8;m/s^2$. I then asked them in their spreadsheet how they might figure out the acceleration due to gravity based on what they already knew. Most were able to figure out without prompting that dividing by the 65 kilogram mass got them there. I then had them use that idea and Newton’s Law of Gravitation to figure out how to obtain the acceleration due to gravity at a given distance from the mass center of a planet. I then had them use the spreadsheet model on their own to calculate the acceleration due to gravity on a couple of different planets, and it went really well.

The focus from that point on was on figuring out what those numbers meant relative to Earth. Often with these types of problems, students will calculate and be done with it. These left them a bit curious about each other’s answers (gravity on Jupiter compared to the Moon) and opened up the possibilities for subsequent lessons. I’ll write more about how I have grown to view spreadsheets as indispensable computing tools in the classroom in the future. A pure computational tool is the lowest level on the totem pole of applications of computers for learning mathematics or science, but it’s a great entry point for students to see what can be done with it.

Files:

Spreadsheet Calculation Template

Centripetal Acceleration of the Moon – a comparison we used two days ago to suggest how a 1/r^2 relationship might exist for gravity and the moon.