How to reproduce our county affordability figures in Excel or Google Sheets

Two things trip people up, so they come first: the rent column is monthly and the income column is annual, and the home price figure is a ratio, not a savings timeline. Both are explained below.

What you need

• The County List with Demographics ($199, one-time): a UTF-8 CSV inside a .zip. It opens in Excel, Google Sheets, and any programming language. To check the columns before you buy, download the free sample.
• Excel or Google Sheets. Nothing else.
• Five columns, with these exact names. Type them exactly as shown:

Column	What it holds
name	The county or county-equivalent name
state_name	Full state or territory name
median_gross_rent	Median gross rent, in US dollars, per month
median_household_income	Median household income, in US dollars, per year
median_home_value	Median value of owner-occupied homes, in US dollars

Step by step

1. Unzip and open the CSV. In Excel, use Data → From Text/CSV so the file loads as UTF-8. In Google Sheets, use File → Import.
2. Keep the working columns. Copy name, state_name, median_gross_rent, median_household_income and median_home_value onto a new sheet, in that order. Leave the original sheet untouched so you can go back to it.
3. Filter to your state. Turn on filters (Data → Filter) and pick your state in the state_name column.
4. Remove counties with a blank. In median_gross_rent, median_household_income and median_home_value, filter out empty cells, or delete those rows. A county with a blank in either column of a formula has no figure for that formula, and a formula run on it returns a wrong number or an error. Rent coverage is effectively complete for counties (see the rent burden FAQ), so you will lose few rows.
5. Add the rent burden formula in column F. With the layout above, in row 2:

   =C2*12/D2

   Fill it down, then format the column as a percentage with one decimal place. Bronx County, NY comes out at 35.1%.

6. Add the home price to income formula in column G, row 2:

   =E2/D2

   Fill it down and format the column as a number with one decimal place. Teton County, WY comes out at 12.2 in the sheet. Our page reports it as "about 12", because the figure carries a margin of about ±2.0 years.

7. Sort. Sort column F or G from largest to smallest. Do this after step 4, so blank cells are out of the way.

If your columns sit in different positions, read the header row and swap the letters: the formulas only care about which column holds rent, income and home value.

Check your work against a published figure

You can confirm the sheet is right before you write anything. These three counties are in three different states, so run this check before step 3, or clear the state filter first:

County	Calculation	Result	Our page
Bronx County, NY	1,436 × 12 ÷ 49,036	35.1%	Highest rent burden shows 35.1
Oldham County, KY	1,142 × 12 ÷ 121,491	11.3%	Lowest rent burden shows 11.3
Teton County, WY	1,371,900 ÷ 112,681	about 12	Highest home price to income says "about 12 years"

If your numbers match, the sheet is working. For Teton, expect 12.2 in the cell and "about 12" on the page; the page drops the decimal because the margin is large.

Two things to get right before you publish

The × 12 is a unit match, not a tweak. median_gross_rent is a monthly figure and median_household_income is an annual one. Divide one by the other directly and the answer is 12 times too small. Multiplying rent by 12 puts both on a yearly basis.

"Years" is a reading of the number, not a unit. Home value divided by income is dollars over dollars, so the result has no unit. We label it years as a way to read it: how many years of a county's median household income equal its median home value. It does not say how long anyone takes to save for a home, because that would assume every dollar of income goes toward the house. Don't write it as a savings timeline.

What the figures can and can't say

• Both are ratios of two medians. Rent burden here is the median rent as a share of the median income. It is not the median of every household's own rent share.
• All three columns are Census survey estimates, so small gaps between two counties are within the noise. A gap of a few tenths of a point is not a reason to say one county beats another.
• Our ranking pages also apply their own screens for which counties qualify, described on each page. The formulas reproduce a county's figure; they will not necessarily reproduce a page's list.

Go deeper on each formula

• How to calculate rent burden
• How to calculate home price to income

Get the file

Download the free sample to see the columns, or get the County List with Demographics ($199) for every county.

If you publish figures built this way, please credit US City List and link to this guide so readers can check the method.