Anthony DeBarros

Sorting a data set helps answer a basic question journalists like to ask: “Which ____ has the highest (or lowest) ______?”

Excel (and other spreadsheets such as the open source Calc) make sorting data easy. In fact, I often make sorting my first step when “interviewing” data because it quickly reveals high and low values and often highlights some that may seem questionable.

Let’s work through a simple sort in Excel. I’ll be using Excel 2007, but older versions have similar functions. Start by downloading the file “sorting.xls” and saving it to your computer. Open it and follow along:

1. We have a table of Census data from the 2006-2008 American Community Survey. It shows the median age of the population for each of 79 school districts in Virginia plus the state itself.

We want to know which district has the oldest and youngest populations. Let’s sort it!

2. Click once on one cell anywhere in the table. This will help Excel auto-discover your table in the next step.

(more…)

Filed under: Analysis, Excel by Anthony on May 12, 2010
3 Comments »

A common way to summarize a group of numbers — one most of us learned in grade school — is to find its mean, commonly called the average. But it’s not always the best measure.

Let’s say six kids go on a field trip, ages 10, 11, 10, 9, 13 and 12. It’s easy to add the ages and divide by six to get the group’s average age:

(10 + 11 + 10 + 9 + 13 + 12) / 6 = 10.8

Because all the ages are close, the average of 10.8 gives us a good picture of the group as a whole. But averages are less helpful when the values are skewed toward one end or if they include outliers.

For example, what if we add a much older chaperone to our field trip? With ages of 10, 11, 10, 9, 13, 12 and 46, the average age of the group rises considerably:

(10 + 11 + 10 + 9 + 13 + 12 + 46) / 7 = 15.9

Now the mean is not an accurate representation. The outlier skews the average, and no journalist should feel comfortable reporting it.

This is where calculating a median is handy. The median is the midpoint in an ordered list of values — the point at which half the values are higher and half lower. If the median household income in East Middletownburg is $50,000, then half the households earn more and half less.

(more…)

Filed under: Analysis, Excel by Anthony on Dec 27, 2009
2 Comments »

When Daniel Craig hit theaters last year in Quantum of Solace, the 22nd film in the James Bond spy series, his ability to dispatch bad guys (and charming good looks, no doubt) helped it earn $168.4 million. That was enough to rank Solace among the top 10 grossing films of 2008.

But how did Solace fare against the rest of the Bond canon, which stretches back to 1963′s Dr. No? The answer depends on whether you adjust for inflation.

We all know that the price of a loaf of bread isn’t what it used to be. The cost of consumer goods tends to rise each year, except during downturns or various calamities. So, taking inflation (or deflation) into account is the only way to  meaningfully compare dollar amounts over time.

There are plenty of apps just for this. The Bureau of Labor Statistics offers one basic calculator, and there’s another at this site. They’re fine for a quick check, but I’d rather do my own calculations. A web app might not have the latest data. And if you’re adjusting more than a couple of amounts, using a spreadsheet will save time. Here’s an exercise from Bond-land:

(more…)

Filed under: Analysis, Census, Excel by Anthony on Nov 1, 2009
No Comments »