Chapter 4 Median

The median is the value in the middle – it’s where half the values are higher, and half are lower. Averages are sensitive to extremes, as we’ve discussed. So things like salaries, home values and numbers that don’t have some kind of constraint are vulnerable to the average’s sensitivity to extremes. Medians, however, are not.

4.1 How to calculate a median

Unfortunately, there’s not an easy way to calculate a median using basic math. You really just have to sort them into order and look for the point where half are above and half are below. If you have tens of thousands of records, this is a giant waste of time.

4.2 How to calculate a median in a spreadsheet

Fortunately, spreadsheets make medians easy. And again, it’s the same regardless of your choice of platform.

=MEDIAN(FirstCellInTheRange:LastCellInTheRange)

So, with our college cost spreadsheet still open, let’s compare the Average to the Median. Column C is the In State Cost and Column D is the Out of State Cost. Immediately underneath the last record, in row 16 and in column C, let’s calculate the Average In State Cost. It’s =AVERAGE(C2:C15) and you get $27652.86. Under that, in Row 17 Column C, let’s calculate the Median In State Cost. To do that, it’s =MEDIAN(C2:C15) and you get $24473.50. See that? The median is about $3,000 cheaper than the average. You can do the same with Out of State costs, but the difference is much less significant.

4.3 How to turn a median into a story

In 2004, at the dawn of what would become a economy-killing real estate bubble, I was working at a newspaper in Florida, which was one of the key states in the whole mess. But at the time, people were just astonished at how fast homes were selling and for how much. So I set out to quantify it. And what we found was that neighborhood after neighborhood, street by street, the entire area was being transformed by skyrocketing home prices. The entire story was built on medians. I used geographic information systems – maps – to calculate the median sale prices of a home in a neighborhood in a given year and then I calculated the same median home price in the current year, and did a percent change calculation.