Score a territory's demand: households, income and growth in one number

The idea the page rests on: a score doesn't measure demand. It writes down what you believe drives your demand, and applies that belief the same way to every county. That is its value. When someone in the planning meeting asks why Fort Bend is ahead of Harris, the answer is four numbers in four cells, not a feeling. The score is still useful, because this page says so up front.

What you need

The County List with Demographics, in Excel or Google Sheets, and seven columns with these exact names:

Column	What it holds
name	County or county-equivalent name
state_code	Two-letter state code
county_fips	5-character county code, as text
households	Occupied homes, five-year average
median_household_income	Typical yearly household income, five-year average, in dollars
home_ownership_rate	Share of homes that are owner-occupied, in percent
population_change_5yr_pct	Percent change in population over five years

These are 7 of the file's 122 columns. Any number column can stand in for one of the four (see "What this can and can't tell you").

The free and $29 files carry none of the four columns this page scores, so it can't be done on them.

Step by step

1. Import the file; don't double-click it. Excel: Data → From Text/CSV, and in the preview set county_fips to Text, then Load. Google Sheets: File → Import, and turn off "Convert text to numbers, dates, and formulas." For the full version, see Open a US City List CSV in Excel or Google Sheets without losing the leading zeros.
2. Keep only your states, on a new sheet. Filter state_code, then copy the visible rows of these seven columns onto a new sheet, in this order: A name, B state_code, C county_fips, D households, E median_household_income, F home_ownership_rate, G population_change_5yr_pct.
3. Check the columns before you trust them. Select columns D to G one at a time and read Count and Numerical Count in the status bar (Excel: right-click the status bar to show them). If Numerical Count is lower, some cells arrived as text. Re-import as in step 1. Column G goes negative, and that is correct: a shrinking county is a signal, not an error. In Texas, 125 of the 253 scored counties shrank over five years.
4. Know what is covered, then delete blank rows. Every county in the 50 states has all four figures, except two that have no income figure: Kenedy County, TX and Esmeralda County, NV. Puerto Rico's 78 municipios carry none of the four. Delete any row with a blank in D to G, then note how many rows are left.
5. Write your weights in four cells, not in the formula. In N2:N5 type the labels households, income, ownership, growth. In O2:O5 type the weights. This page's example is 0.4, 0.3, 0.1, 0.2. In O6 put =SUM(O2:O5), which must read 1. Why: anyone reading the sheet can see what the score assumes, and changing one cell re-scores every county.
6. Turn each column into a 0–100 rank. In column H (header hh_idx), row 2, and fill down:

   =100*PERCENTRANK.INC(D$2:D$254,D2)

   Do the same for income in I (inc_idx, on column E), ownership in J (own_idx, on F) and growth in K (grw_idx, on G). Change 254 to your own last row. A 75 means the county is ahead of three-quarters of the counties on your sheet for that column. The rank is against your states, so adding a state changes every score.

7. Compute the score. In column L (header score), row 2, and fill down:

   =$O$2*H2+$O$3*I2+$O$4*J2+$O$5*K2

   Sort the sheet by L, largest to smallest.

8. Read the top 20, and check their grades. Each row of the file has a completeness_grade. It measures how complete a row is, not how accurate it is. If a county in your top 20 looks wrong, check its grade before you trust it. (In Texas, all 20 are grade A.)
9. Drill down: list the cities inside a top county. Import the City List with Demographics onto its own sheet named Cities, with step 1's settings, and set county_fips and fips_place_code to Text. Put a county code from your top 20 in B1 of a new sheet, as text, for example 48157. Then:

   Google Sheets

   =FILTER({Cities!C2:C,Cities!G2:G,Cities!AL2:AL,Cities!AH2:AH},Cities!I2:I=$B$1,Cities!AL2:AL<>"",Cities!AH2:AH<>"")

   Excel 365

   =FILTER(HSTACK(Cities!C2:C49000,Cities!G2:G49000,Cities!AL2:AL49000,Cities!AH2:AH49000),(Cities!I2:I49000=$B$1)*(Cities!AL2:AL49000<>"")*(Cities!AH2:AH49000<>""))

   Either one returns each place's name, type, households and median household income, keeping only places that carry both figures. The column letters belong to the file as shipped: C name, G type, AL households, AH median_household_income, I county_fips.

10. Before you trust the city numbers:
    • Coverage. Only about 28,000 of the 48,490 places carry an income figure, and the file's methodology.md says the same. The filter in step 9 drops the rest, so compare how many rows came back with how many places the county has.
    • The cities don't add up to the county. Places cover only part of a county, and a city that spans several counties is listed under one. Houston reaches into Fort Bend but is listed under Harris (the file's methodology.md uses Houston as its example). Never add city households to get a county total: use the county file for that.

All formulas except step 9 work unchanged in Excel and Google Sheets.

Check your work against our numbers

Texas, with the example weights 0.4 / 0.3 / 0.1 / 0.2:

Check	Expected
Counties after step 4	253 of 254 (Kenedy has no income figure)
Counties that shrank (column G below zero)	125
Top row after sorting by score	Fort Bend, 48157: hh_idx 96.8, inc_idx 99.2, own_idx 69.4, grw_idx 93.6, score 94.1
2nd and 3rd	Rockwall 48397, 93.0; Parker 48367, 92.5
Harris, 48201 (most households in Texas)	hh_idx 100, own_idx 2.7, score 79.6, 27th
Step 9 for Fort Bend	25 places, all with both figures. Largest: Sugar Land (37,830 households, $137,511), Missouri City (26,740, $96,746), Rosenberg (14,870, $64,897)
Step 9 for Rockwall	5 of its 6 places come back. Mobile City has no income figure
Fort Bend's 25 places added up	149,611 households, against 281,259 for the county (step 10)

A score may differ from these by 0.1 because of rounding in PERCENTRANK.INC. If your numbers match, the sheet is working.

Two things that break this

Weights typed inside the formula. If the weights live inside the score formula, nobody can see what the score assumes, including you in three months, and a plan review will not accept it. Keep them in O2:O5 and print them next to the ranking.

Scaling by the biggest county instead of by rank. The obvious way to put a column on a 0–100 scale is to divide by the range from smallest to largest. Don't. In Texas, Harris County holds 1,728,103 households, so on that scale 231 of the 253 counties score under 5 for households, and Harris tops the list on size alone. Growth breaks the same way: Loving County went from 169 people to 48 (−71.6%) and sets the bottom of the scale for every other county. Ranking (step 6) isn't pulled by one extreme county. Ranked, Harris drops to 27th, because its home-ownership rate ranks near the bottom of the state.

What this can and can't tell you

This procedure is correct only if the four columns you weight actually move with demand for what you sell. The score does not discover that; it assumes it. If your demand follows age or commute rather than income, swap in median_age or avg_commute_minutes, and say on the same page as the score which columns and weights you used.

Also worth knowing:

• The columns are not from the same years. Income, households and ownership are five-year survey averages. Population change compares the latest yearly estimate with one from five years earlier. That is why the score ranks counties against each other and is not a rate of anything. The file's methodology.md explains this under "About dates and vintages".
• A rank says "ahead of", not "how far ahead". Two counties ranked 95 and 96 may be far apart or nearly equal. For the size of the gap, read the original column.
• The download includes column-definition.md and methodology.md. Read those for what each column means.

Get the files

County List with Demographics — $199 one-time. 3,221 counties and county-equivalents (the 50 states and Puerto Rico), 122 columns. This is the file that does the scoring and covers every county in the 50 states. See the list, or start with the free 100-row sample.

City List with Demographics — $199 one-time. 48,490 places, 58 columns. This one shows where inside a county the households are. See the list, or start with the free 100-row sample.