Lab 2: Computing the Hoover Index of Concentration with Excel

The Hoover Index of Concentration measures how concentrated a population is by measuring what percentage of that population would have to move in order to redistribute the population evenly. It uses both a population measure and an area measure to determine the relative concentration of a population, with subunits giving the picture of the whole. We will be using counties as our subunits to understand the changes in population concentration of three states: New York, Massachusetts, and Georgia.

Open up Blackboard and go to Course Materials. There you will find an Excel file called “Lab 2 Area file.xls.” Open and save this file. In it you will find a list of the counties of each of these states and their areas. Now you need to download the population data from the census website.

1.      Open www.census.gov and select American Factfinder from the left bar. When you get to American Factfinder, you will be routed to a page that lets you select either the new or the “legacy” version. Select the legacy version for 1990 and 2000 datasets.

2.      Once in American Factfinder, select “Data Sets” from the left bar, and then Decennial Censuses

3.      The page defaults to a selection of SF1 for 2000. Select Detailed Tables.

4.      Your next step is to select your geographies. First select “Counties”, then New York State, then “All counties”, then “Add”, then “Next”.

Now you are ready to access your data. You need P1: Total Population. Select it and hit “Add” and “Show result”.

Your results will be a long row of numbers. You need to download them as a column. The download function on the site allows you to transpose your columns and rows. Select “Download.” Select the second option—Comma-delimited (.csv) Transpose columns and rows. For computer security, you may have to do this process twice.

Now you need to repeat the process for 1990. The website remembers the geographies you selected, so you do not need to select them again. (To change geographies, you may need to close the browser and reopen.) Look for the hotlinks across the top of the webpage. Select “Datasets with Detailed Tables” and scroll down until you find 1990 Summary Tape File 1. Select it and hit Next. It assumes you want the same geographies. Since you do, hit next. In 1990, total population was P001 Persons. Select it, Add, and hit Show Result. Download it the same way you did the 2000 data, transposing the columns and rows. We will compute the New York results, and you should be able to follow these steps to get the Massachusetts and Georgia data on your own.

 

Now open the area file and the two census files. The counties are listed in alphabetical order on the census website so you will be able to copy and paste your population data into Excel.

Now you are ready to compute the Hoover index for New York for 1990 and 2000. Make sure you save your data file!

The equation for the index is: 

              r

H = 50  Σ   | Pi – Ai |

             i=1

Where:

Pi = Population of Subunit i/ Total Population and
Ai = Area of Subunit i/ Total Area

Step 1: Computing Pi and Ai:

In order to compute Pi and Ai, you need to total these two columns. Highlight cell C65. Look at the menu bar at the top of your screen. Hit the sigma key (Σ). The following formula should appear in C65: =SUM(C2:C64). Press enter. Do the same in cell D65. These will be your denominators for computing Pi and Ai for New York.

Label Columns E, F, and G Ai, Pi 2000, and Pi 1990. The Hoover Index formula may look a bit “scary, but it is easy to translate it into “Excel-speak” if you follow a few basic rules.

  1. Formulas always start with =.
  2. Always start from the inside out. When in doubt, break the formula down into its component parts. Remember, Pi=Population of the county/Total State Population and Ai=Area of the County/Total State Area, so not so scary.
  3. Remember that computers do exactly what you tell them to do. Use parentheses to insure that you have the computer doing what you need it to do.

 In cell E3, enter the formula =b3/b$65. Hit enter. Then copy and paste the formula into all the NY Ai and Pi cells for both census years.

The next step is to compute the absolute value of the difference between Pi and Ai. Label Column H Pi-Ai 2000, and Column I Pi-Ai 1990. In cell H3, type the following formula: =ABS(F3-E3) and in cell I3, enter =ABS(G3-E3). Make sure you use parentheses so that the computer subtracts first and then computes the absolute value of the difference. Copy/paste this formula down the rest of the columns. We use absolute value so that negative differences do not cancel out other values. We want to include the values from each county.

The last step, moving from the inside out of the formula, is to add up all the county values and multiply by the constant, 50. Why 50? 50% odds of staying versus moving.

Highlight cell H65. Hit Σ, which will bring up the autosum formula, then add *50. Your formula should read =SUM(H3:H64)*50. Copy this formula into cell I65.

Now repeat this process on your own for Massachusetts and Georgia. You are computing a total of 6 numbers. These are the only 6 numbers that need to be in your results table.

Interpreting the Index: This is an index from 0 to 100 that gives a relative idea of how concentrated or evenly distributed a population is. It is not a percent, but it infers the percent of the population that would need to move to evenly distribute the population. The higher the index, the more concentrated the population, and vice versa. In your write-up, discuss the differences across the three states and the changes between the two census years for each state. What processes do you think are going on? Are the populations more or less concentrated? What does this suggest about where the growth is? Cities? Suburbs? Small towns?