In a previous article, we listed the technology skills that every teacher should possess. The 15 competencies ranged from using common productivity tools to produce learning documents, to the development of multimedia projects by students, and are based on standards composed by ISTE and other authoritative sources. They are also based on the realities of today's classrooms and students. They have been adopted by the faculty of the School of Education at Hunter College -- the largest producer of teachers for the New York City public schools. This week's article looks in more detail at the second competency on the list, why it's important, and how to master it. It's full of practical advice on how to do simple quantitative analysis with a spreadsheet, and prepare documents that you can use in teaching and learning.
Here's the competency:
Analyze quantitative data. This includes administrative work such as putting student test scores into a spreadsheet and analyzing them, as well as preparing curriculum materials with digital tables and graphs of curriculum content.
Why does a teacher need to be able to analyze quantitative data on a computer? First, because it's a practical and useful skill for keeping track of students, grades, borrowed books, addresses, phone numbers, and all the other details that make up the daily life of a classroom. Doing all of this on a spreadsheet saves time and energy (and paper.) Second, the curriculum is full of numbers: the diameters of the planets, the votes for mayor, the daily growth of the bean plant on the windowsill. Working with these data on a spreadsheet opens up many new ways of teaching and understanding a wide variety of subjects. Third, it's almost the only way to make sense of the data generated by the education reforms of the past few years: standardized test scores, aggregated and disaggregate results, state standards and so forth. Without spreadsheet skills, the teacher cannot participate fully in this environment.
Where do you stand?
Most of us can use a spreadsheet to create columns of numbers and labels. Where do your skills stand on this continuum from novice to expert in dealing with quantitative data?
| Novice|| Competent|| Expert|
You can produce a simple spreadsheet of several dozen data points, with computed totals and averages.
You can include formulas, construct charts, and sort data. And include them in academic assignments for students.
You can construct workbooks combining several sheets, and use statistical formulas to analyze complex data.
And what kinds of spreadsheets do we expect teachers to be able to produce? Here is an example of what a novice might produce, as a class handout for a geography lesson:
There is nothing inaccurate about the data in this table (except perhaps for the two letters at the end of New Hampshire that seem to have been squeezed out of the column). Click the sample for a larger view . It shows formulas being used to compute appropriate totals and averages. But it is not as easy to use as it might be. The headings over the columns for Area and Population, for instance, do not line up with the data they describe.
A teacher with more spreadsheet skills might produce a document that looks like this:
Here we see the addition of a graph to aid in understanding the relative areas of the states, labels formatted for easy reading, and colored to distinguish original values from computed ones. The result is a more readable and useful document for learning. Click the sample for a larger view.
How to do it
The teacher who prepared the more useful document followed these steps:
- Launch your spreadsheet program (Excel, OpenOffice, Numbers...)
- Enter labels (in this case the names of the states) in the leftmost column, beginning in row 2.
- Enter data for area and population in the next two columns. Just the numbers, no commas, no dollar signs.
- Enter the column headers for Area and Population in the first row.
- Enter the row headers for Average and Total in the first column.
- Enter the formula to compute the average area in the second column: =average(B2:B7)
- Enter the formula to compute the total area in the second column: =sum(B2:B7)
- Copy these two formulas, and paste them into the third column so that they compute the average and total for population.
- Adjust the width of the columns, and the formatting of the text and numbers, to be easy to read, as in the example above.
That's how to construct and display the basic information in the table. Next we'll construct the graph.
- Select the data you want to graph. In this case, it's the names of the states and their areas, so we select the range of cells from A2 to B7. And we re careful not to include any other cells, such as the averages and totals -- just the raw data themselves.
- From the menu bar, choose Insert --> Chart.
- Click on the type of graph you want. In this case, it's a pie graph.
- Move the graph on the page so that it does not cover up the table of data.
The last task is to add the title at the top.
- From the menubar, choose View --> Header and Footer.
- Into the header box enter the title of the document, and format it as necessary to math the other headings in the spreadsheet.
- Click OK.
Creating a useful and easy-to-use quantitative document takes a bit of time, but the results are well worth the effort.