A window into a mind through a spreadsheet

Anne Egger

published Jun 12, 2020 8:12pm

Last year, back when I taught structural geology in a classroom with lots of stuff in it, including students, I tried something new. My goal was to give students a better sense for what the elastic modulus and Poisson's ratio actually mean, and to give them the opportunity to become comfortable with making simple calculations and assessing their results to see if they "make sense."

NOTE: For those of you who need a little reminder—or never knew—what these two things are, the elastic modulus tells you how stiff a material is: how hard do you need to push on it to make it compress? Poisson's ratio tells you how much a material expands in the direction perpendicular to the direction you are pushing. The details aren't important. The fact that both values are easy to calculate from properties you can measure is.

One needs special equipment to obtain these values for rocks, and it is difficult to actually see the deformation. So, for several years, I brought out a wide variety of substances—sponges, super balls, foam, cork, clay—and we talked about their properties broadly and comparatively, not quantitatively. This was not particularly effective. But 2019 was the first year I taught structure after moving into a new building. We had lots of packing material leftover from new equipment we had ordered, and I thought, "Hey! Foam might be a pretty good analogue for rocks." There are many different types of foam: sofa cushion foam, Pelican case foam, pool noodle foam, packing foam, Nerf ball foam. And each foam has quite different properties that relate to their uses. So I asked students to take the measurements they needed in order to calculate the elastic modulus and Poisson's ratio for each of the foams, and we compiled the data. I did not do this myself first, but I had a hunch it would be interesting, and—fortunately—I was not wrong.

Took a while to get there, though. The students struggled mightily with units. They forgot how to go from a mass to a force to a stress (a 10 kg mass has to be multiplied by the acceleration of gravity (9.8 m/s2) to get to a force, and divided by the area over which it is applied to get a stress). They started measuring in inches rather than metric units. Once we cleared all of that up, however, the results were pretty much as I had hoped: the range of values for different foam types is similar to the range seen for rocks, though the absolute values of the elastic modulus are much, much smaller (translation: it's easier to compress the foam). So that part worked. And the students could really see the difference between a foam with a relatively high elastic modulus (packing foam, pool noodle) and one with a low modulus (sponge), and were able to describe why you would manufacture sofa cushion foam to be somewhere in between. Also a good outcome. But the calculations were pretty much a mess, people scribbling numbers down and using phones as calculators, scribbling more numbers down, not able to reconstruct how they got from one scribbled number to the next.

In anticipation of this year, I ordered a bunch of different foam types in regular sizes, planning to refine the activity. When delivery switched to online, I had the brilliant idea of making a direct measurement video so students could still measure the foam blocks themselves. I did make videos, definitely not quite as slick as the direct measurement videos. I just put a ruler up next to the blocks, added the mass, and kept the ruler there so they could see the change. I told students to create an Excel spreadsheet to make their calculations. They had a series of questions to answer, and also had to upload their spreadsheets so I could see what they did to get their responses. Several students came to my office hours and used screen sharing to show me their spreadsheets, too.

And, wow. Thirteen spreadsheets, thirteen minds manifest. The students who came to office hours mostly had made mistakes in writing their formulas in Excel, which were relatively easy to correct. One student had all of the measurements and values correct, but did not use any formulas. Had he done the calculations by hand, or on a calculator and typed them in? One put the foam types as column headers and the calculated values as rows—it was so counterintuitive to me, it took me a good ten seconds of starting at it to figure out what was wrong. One had several sections of measurements and calculations, each with a new column of foam types. One went through the entirely unnecessary step of making a pivot table. Some still had errors in their formulas, and did not move through calculations stepwise and ended up with crazy unrealistic numbers. Many had no units on their values, no idea what units they ended up with.

It's not that I am a huge fan of Excel, but this activity made me realize a role it plays other than just making calculations easier. There are many ways to use it poorly—as a static table, for example—and using it (or any spreadsheet program) well involves organization and logic. I realized that incorporating more activities like this one could help students build those logical thinking skills that might help them in any sort of data analysis. So this activity will continue to evolve, with a bit more scaffolding around working with data in a spreadsheet.

Comment? Start the discussion about A window into a mind through a spreadsheet