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. The value of the index runs from 0 to 100, where the higher the number, the more concentrated the population (more people would have to move to evenly redistribute the populations) , and the lower the number the more evenly distributed the population (fewer people would have to move). By comparing three census years, we can get a sense of whether the population is getting more or less concentrated, giving an indication of whether the growth is occurring in urban counties (increasing index) or suburban and rural counties (decreasing index).
I have created a county population and area file for New York, Massachusetts, and Georgia. It is attached below. Open up “Lab 3 Population and area file.xlsx” and save this file. In it you will find a list of the counties of each of these states and their areas and populations. Here you will find the 2020, 2010, and 2000 population data for each of the counties. These are all from their respective decennial censuses. You will also find the areas of each of the counties.
Now you are ready to compute the Hoover index for 2000, 2010, and 2020. 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
Basic rules for reading a formula:
1. You compute a formula from the inside out.
2. Follow the order of operations:
Please Excuse My Dear Aunt Sally Parentheses=>Exponents=>Multiplication=>Division=>Addition=>Subtraction
Step 1: Computing Pi and Ai:
I have given you Population and Area totals. We will learn to compute totals later in this lab.
Label Columns F, G, H, and I Pi 2020, Pi 2010, Pi 2000, and Ai. 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.
Formulas always start with =.
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.
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.
Any time you want to use the same cell in a formula, you use a $. If you want to use the same column, but scroll through rows, put the $ before the column (i.e., $b3). If you want to use the same row, put the $ before the row number (i.e., b$3). If you want to use the same cell for every computation, you put a $ before the column letter and row number (i.e., $b$3).
The formula for Pi is County Population/State Total, so for Appling County, GA, it would translate into Excel-Speak as =b4/b$3. If you type it this way, you can just copy this formula and paste it down all the Georgia counties. Now copy and paste the same formula from that cell into Pi 2010, Pi 2000, and Ai for all the Georgia counties. Both Pi and Ai have the same formula, so using Excel allows you to compute the four numbers for each county by entering the formula only once.
Now we have all the Ai and Pi values for Georgia. Now we move to the next step. All formulas move from the inside out. The next step is to compute the absolute value of the difference between Ai and Pi. Absolute value refers to the value of the difference without concern for whether it is positive or negative. Excel-Speak for the absolute value of Ai-Pi for 2020 in this case would be =abs(f4-i4), for 2010 =abs(g4-14), and for 2000 =abs(h4-i4). There are certain operations in Excel that use text and absolute value is one of them. When using a text command, you follow immediately (no spaces) with parentheses stating what the text command will be computing. Input these formulas for Appling County and then copy and paste them down the other Georgia counties. Now you have all the parts inside the equation. You can now move out.
Σ, the Greek letter Sigma, means in math the sum of everything that comes after. The next step involves adding up what you just computes for each row, and then multiplying your result by 50. 50 is a constant–50% odds of moving or not moving. If you look across the top of the Excel command bar, you will see a button for Σ. This is a short cut. Scroll down to the open cell below the 2020 computations in column J for Worth County. It should be J163. Hit the Σ button on the menu bar. The default selects all the numbers above this cell. In this case, you want the default. Now go up to the command bar.there you will see the actual formula =SUM(J4:J162). Next to this, type *50. * means times. Now your formula should read =SUM(J4:J162)*50. Hit enter. You now have the Hoover Index for Georgia for 2010. Copy and paste the formula into K163 and hit enter for the Hoover Index for 2010, and into L163 for 2000.
Look at the three results.
Are they high or low, relative to 100? Did they increase or decrease? What does this tell you about the population distribution of Georgia? Where did the change occur?
Now repeat the steps for New York and Massachusetts. Be careful to note what row the Total Population and Total Area are in for these two states. Now create a table with your 9 results following the steps I demostrate in the following video on how to create a results table. Do not include all the computations–just the results. Round your results off to 2 decimal places.
You can do that by selecting the cells, Right-clicking, selecting Format cells=>Number=>Decimal Places 2.
Once your table is created, you are ready to write up your lab.
Interpreting the Index:
Again, 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 three 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? Make sure you follow the lab write-up format in Course Documents. Please make sure you do not discuss whether people are entering or leaving the state. This lab does not tell us that. Focus on what the data do say.
