Web treasure hunt:
Answer for question 10

Answer: The 20 largest school districts vary widely in poverty, from about half the students in Detroit to less than 10 percent in three districts surrounding Washington, D.C. The list: Detroit, 48%; Houston, 41%; NYC, 39%; Los Angeles, 39%; Philadelphia, 36%; Chicago, 35%; Dallas, 34%, Dade County (Miami), Fla., 32%; Memphis, 32%; San Diego, 27%; Hillsborough County, Fla., 22%; Duval County, Fla., 20%; Broward County, Fla., 17%; Orange County, Fla., 17%; Palm Beach County, Fla., 17%; Clark County, Nev., 13%; Hawaii Dept. of Ed., 12%; Prince George's County, Md., 9%; Montgomery County, Md., 7%; Fairfax County, Va., 6%.

Clearly, these school districts aren't all comparable, which is why you would want to sort your state's schools, and even schools in your state of similar size to yours. That's the point of this exercise: to compare items within a peer group.

How do I find that? To import the Web page into Microsoft Excel, you'll first use the Web browser to save the text from the Web page on your hard drive, then use Excel to open the file from your hard drive. This will run Excel's text import wizard, which will help you make sure the information is in nice columns and rows.

This may sound difficult, but it just takes a few minutes to learn, and a couple of minutes to do from then on.

Here are the steps:

1. Note: I'm working here with the 1995 file, though the Census has a 2000 file available now at http://www.census.gov/hhes/www/saipe/school/sd00ftpdoc.html.
2. First, go to the Web page for the poverty file, at http://PowerReporting.com/files/. Scroll down to "poverty" and choose the second file, "poverty.txt." It should open in your Web browser. Be very patient; this is a huge file, with thousands of schools.
3. Check to make sure that you have all the schools from Albertville, AL, to Weston County, WY. If not, reload the page.
4. In your browser, choose File/Save As. (These are the commands in Netscape and Microsoft Internet Explorer.) Choose a destination (you'll need to remember it!), a file name (say, poverty.txt), and make sure that the type of file is set to "text file." Just calling it poverty.txt doesn't ensure that you have a text file, as opposed to a Web document with fonts and coding and such. You have to choose text from the "save as type" list. Choose "Save" or "OK" to save the file.
5. Now, start Microsoft Excel. File/Open, and find poverty.txt to open it. You'll need to look in the same destination. Excel won't see it there at first, because it will look only for "Microsoft Excel files." Change "files of type" to "all files" or "text files." Once you can see poverty.txt, select it and "Open."
6. Excel will show you the "Text Import Wizard," which will help put the information in clean columns and rows. You're on step 1 of 3 of the text wizard. It will guess that this information is fixed width, which it is: no matter how long Albertville's name is, and how short the next name is, the second column always starts at the same place. Click on "Next."
7. In step 2 of the wizard, you make sure that it has the column breaks where they need to be. Scroll to the right. Make sure that there is a break between each column. It probably will do just fine at this; you're just supervising. You don't need a break after the last column. If you need to add, move, or delete a break, follow the directions. Choose "Next" to move to step three.
8. Step 3 of the wizard isn't important in this example, but you may wonder what it's for. Note that here you could choose to "skip" column two, an ID number that we don't need because we have the district names. You also could choose to set column two to "text" instead of "general," because otherwise Excel will presume that those codes are numbers, and will throw away the leading zeros. Here, you don't care. But if you had ZIP Codes, and some were in Connecticut and started with zero, you would care; you would select the column and choose "text." Now, having done nothing here (except refreshed your understanding of data types), click on "Finish."

Now you have your data in a spreadsheet. You may want to widen the columns so they look prettier. To do this, select the entire sheet (the box just above the 1 and to the left of the A), then double click on the column border between the A and the B. That sets every column to the width of the widest item down the column.

In the first blank column to the right, which is probably column G, put in a headline: "Pct. Poor."

In the first cell below the headline, probably cell G2, put in the formula for the percentage of children in that school who are poor. That would be =F2/E2. Hit the enter or return key to put that formula in the cell. You should see .18208 for Albertville.

Copy that formula down for all schools. To do this, first, select the cell with the formula in it. Then you'll see a little black pimple on the lower right corner of that cell. (OK, it's called the fill handle.) Double click that.

You probably want to display the column as a percentage (18 percent, instead of .18208). To do this, you don't have to multiply by 100. First, select the entire column, by clicking on the column letter G. Then click on the % button. If you don't see that button, you can choose Format/Cells and choose percentage and set the number of decimal places you want to show.

Now, you're ready to sort. Sorting could be a longer lesson here, but in brief:

To compare the largest school districts, you'll want to sort all the schools by size (select one school's number of kids, then use the ZtoA button). Then eyeball the top 20, or select those rows and use Data/Sort to sort them by your new Column G descending, from poorest to not so poor.

Similarly, to compare schools in your state, you would sort all the rows by state, then select and Data/Sort only your state's schools by Column G.

The key concept in sorting is that you are sorting rows by the value in a certain column, not sorting a column. The AtoZ and ZtoA buttons sort the entire sheet by one value; you pick ONE CELL in that column, and use those buttons. The Data/Sort function, however, is the only way to sort only certain rows by a value; you pick all the rows you want to sort, using the ROW NUMBERS, so you get all of the cells in those rows; then you use Data/Sort to tell it what column should be used to determine the order.

Comment: Honest, do this several times, lock it into muscle memory, and you can do it on any set of information in under 10 minutes.

How do I know this is right? This particular set of information, on poverty in schools, is very squishy. A disclaimer and discussion is here.

How do I attribute this? Emphasize that this is an estimate. Don't make anything of small differences between schools. And attribute it to federal estimates released by the U.S. Census Bureau.

Go on to the bonus question

A primer on Web searching is available at http://PowerReporting.com/altavista.html.

You can reach Bill Dedman by e-mail at Bill@PowerReporting.com.