Current Article All Articles About the Author Q&A Podcasts Teachnology Blog

The mechanical engineering class was outdoors firing tennis balls from an air cannon. They varied the air pressure in the machine they had designed and built, shot the ball, and then measured how far it went before hitting the ground. The higher the air pressure, the longer the distance. At 20 pounds per square inch the ball shot over the top of the building. In an Excel spreadsheet on a laptop, the students entered the pressure in column A, and the distance in column B. They recorded the results from several dozen trials, then went back to the classroom to analyze the results.

The sixth-graders let the ball roll down the ramp and off the edge of the table. The steeper the ramp, the faster the ball rolled, and the farther it traveled before it hit the floor. With the ramp perfectly flat, the ball did not roll at all, so its distance was recorded (again in an Excel spreadsheet) as 0. When the students raised the back end of the ramp up one inch, the ball rolled slowly down the ramp, and hit the floor six inches from the edge of the table. As they raised the ramp further, the ball went farther. They recorded the results from seven trails, then proceeded to study the data they had gathered.

Both these groups of students were using Microsoft Excel as a tool for recording data and later analyzing it. At the college where the first example took place, the math department offers an elective course called Excel for Engineers, that prepares students to use this ubiquitous application for a variety of data analysis and problem-solving purposes. At the middle- and high-school levels, students and teachers are discovering many ways to use Excel as an everyday tool for learning. This week's article starts from the simple experiment conducted by the sixth graders, and shows how Excel can work with the data to lead to some very interesting educational understandings.

Before the lesson, the teacher acquired a metal ball (from a bocce ball set), a two-foot piece of grooved moulding for the ramp, some small blocks of wood to raise the ramp, and a styrofoam board. She tried the experiment herself, recorded the data, and worked the analysis through with Excel. She introduced the lesson by showing the students the apparatus, and asking them to predict the results: How far will the ball travel before hitting the floor? As we raise the ramp, what will happen to the distance the ball travels? Why? Each student recorded his or her prediction in a notebook.

To record the results of their experiment, the sixth graders set up a simple spreadsheet with two columns.

In column A they recorded the height of the back of the ramp, and in column B recorded the distance the ball flew before hitting the floor. (To figure out exactly where the ball struck, they laid down the piece of styrofoam on the floor up against the leg of the table. When the heavy metal ball hit, it left a clear round dent in the foam. The number they recorded was the distance from the edge of the foam to the center of the circle-dent.)

Back in the classroom, the teacher displayed the table of data on the projection screen for all to see. She asked what conclusion might be drawn from these results. The students quickly agreed that the higher the ramp, the longer the distance.But from the pattern of dents in the styrofoam they realized that the variation was not uniform -- that although they had varied the height of the ramp in regular intervals, the distance did not increase in proportion: the dents got closer together as they moved away from the edge of the table. How does this result compare with your predictions?, asked the teacher. In their notebooks, under their predictions, the students wrote their answers to this question.

In the spreadsheet, the teacher created in column C a formula that calculated the difference from one distance to the next -- in effect, a measure of the distance between the dents. Into cell C3 she entered the formula =B3-B2, then filled this formula down to cell C8 with the corresponding references. The spreadsheet now showed a clearly diminishing distance in column C.

Why is this happening? asked the teacher. As they looked at the columns in the spreadsheet, the students offered several possible explanations, based on what they had been learning in their science curriculum. Air resistance, said one student. The force of gravity, cried another. The weight of the ball -- a lighter ball would go farther, explained a third.

Excel has helped this teacher create in the classroom a teachable moment: a point where the students' minds are focused on a concept important to their understanding, their curiosity is piqued, and they are searching for an explanation.

Remember the college students? Suppose they recorded similar data from their tennis-ball cannon. How might their Excel-based analysis lead to a teachable moment?

In their Excel for Engineers course, they had learned how to plot data with Excel, and so the students working in small groups inserted a chart into their spreadsheet. (You can learn how to do this by reading the article in this series, How to make Graphs with Excel.) One group plotted an x-y chart.

What is the nature of the relationship of these data?, asked the professor. It's definitely not linear, responded the group with the x-y plot. It could be hyperbolic, said another, while a third group was trying to figure out how to enter a logarithmic formula into their Excel spreadsheet. The students, as well as the teacher were at this point playing with the data in Excel -- making informal hypotheses, trying them out, testing various ideas, looking for relationships. Their knowledge of math, the results of the experiment, and the concepts of physics all could be combined in the manipulation of the numbers in Excel.

The professor, faced with a teachable moment, displayed the students' x-y plot on the big screen so that the entire class could see. Let me show you how Excel can help you figure this out...

Select your chart, he told the students.

They all saw the Add trendline dialog window.

We agree it's not linear, said the teacher. Try second-order polynomial.

The students agreed that the trend line generated by Excel (shown in black) was a pretty good fit for the data that had collected.

One of the students asked, Second-order polynomial: that means squared, right?

The professor responded by clicking the Options tab in the Add trendline dialog window and checking the boxes to display the equation and the R-squared correlation of the data and the trendline (the closer the R-squared value is to 1, the better the fit between the data and the trendline). Excel did its work and added the equation and coefficient of correlation to the chart.

From this teachable moment, the professor could work with the class in several directions: toward the underlying physics concepts, or mathematical principles, or statistical realities of measurement in a field experiment. Excel did not do the teaching, but instead served as a valuable tool in the hands of both teacher and students that enabled a deeper analysis and firmer understanding of the way the world works.

(I am indebted to Professor David Post of the Benjamin Franklin Institute of Technology in Boston for loaning me a copy of Spreadsheet Tools for Engineers, and for showing me how to do trendline analysis with Excel.)