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

Picture this:

They had been studying the planets. The styrofoam models hung from the ceiling, the debate on whether or not Pluto was truly a planet had just ended, and the varied moons in the solar system decorated the walls. Mr. B posed the question,

He divided the class into small groups, and assigned each to research the question and come back with evidence to support their conclusion. Two groups went off to the library. Another pored through the textbooks in the classroom. One group found rulers and tape measures and began to take readings from the styrofoam models. Two groups went online to gather data from the web. Each group came back with a collection of numbers.

The purpose of this week's article is to examine how a spreadsheet can help students work with these kinds of numbers to arrive at an answer to Mr. B's question.

Their numbers arrived in many different forms: pages copied out of reference books; figures penciled onto 3x5 cards; numbers scrawled on scraps of paper; matrices maintained in lined notebooks. The non-web groups got to work entering the diameters, distances and masses from the keyboard into the spreadsheet.

By this time, the groups who got their information from the Internet had already completed their tables in Excel. When they found a trustworthy site that listed the data they needed, they simply dragged and dropped the numbers into the spreadsheet. No typing, no copying and pasting, no mistakes. To do this, they opened Excel in one window and their web page in another. They resized the windows so both could fit on the screen at once. Then they selected the number they needed from the web page, dragged it with the mouse to the spreadsheet, and let go. (For instructions on how to use multiple windows in this way, see the article in this series, Doing Windows.)

No matter how the numbers got there, the spreadsheets of the students at this point looked something like this:

By looking at the numbers, it's not immediately clear whether size and distance are related. So the students began playing with the data. One group sorted the data according to the planet's distance to the sun. To do this, the selected the cells containing data, and then chose Data --> Sort from the menubar. This produced a sorting window, like this:

Then they chose to sort by distance from sun, and clicked OK. The result listed the planets in order of their distance from the sun (Mercury, Venus, Earth, and so forth). You can see the results in the illustration below. Sorting is a powerful feature of the Excel spreadsheet program, and can be a great help in organizing information and finding meaning in it. Sorting involves three steps:

1. Select the data you want to sort.
2. Choose Data --> Sort from the menubar.
3. Choose the sorting criteria from the Sort window.

To analyze this further, they created a graph of the planet's diameters, displayed in order of distance from the sun. It looked like this:

A quick glance at this graph shows that the four biggest planets are indeed the farthest from the sun...but that the relationship is not direct: the smallest planet (Mars) is adjacent to the largest (Jupiter), and both are right in the middle. Students at this point might sort the data again in other ways, develop more graphs, and test their ideas about the relationship between diameter and distance.

Older students might use another tool for analysis offered by Excel: statistical functions. The concept of correlation is an important component of the mathematics and science curricula at most high schools and colleges, and can be very helpful in exploring the kinds of questions posed by Mr. B. What is the correlation, Mr B might ask, between a planet's diameter and its distance from the sun?

With Excel, the answer to his question emerges quickly. Correlation is one of Excel's built-in math functions. Here's how we might compute the coefficient of correlation between diameter and distance.

1. Select a blank cell in the spreadsheet.
2. Enter from the keyboard the correlation formula: =CORREL(B3:B10,C3:C10). The cells B3 to B10 represent the diameters of the planets; C3 to C10 represent their distance from the sun.
3. Press the return key.
4. Watch the coefficient of correlation appear in the cell.

The results of this computation tell us that a positive correlation of .25 exists between size and distance. (A correlation coefficient of 1 would mean that the data were perfectly ordered: the smallest planet closest to the sun, the largest furthest away, and the others in the same order. A correlation of -1 would mean just the opposite was true.)

An astute student looking at the data table in this example might wonder about the correlation between diameter and mass: are the biggest planets always the heaviest? By using Excel to compute a coefficient of correlation of .85 for these two data sets, he or she would learn that in most (but not all) cases bigger means heavier.

When our daughter Molly was in fifth grade, the weekly assignment was to write the spelling words in alphabetical order three times. Molly asked if she could turn hers in as a computer printout. This was in 1993, when computers had not reached the ubiquity they enjoy today. Molly (the world's worst speller at that time) came home, entered the week's words into the spreadsheet, ran the spell checker, and then sorted the list alphabetically. She printed three copies, and turned them in faithfully each week. The teacher soon caught on to Molly's purpose-defeating expedience and ended this charade, but both she and her student have forever recognized the informational power of the spreadsheet.