Percent change: Know the formula

Here’s a question I posed to some college students recently:

Let’s say you cover the Town of East Middleburgtown. The mayor announces that this year’s town budget comes in at $12.6 million. Last year’s budget was $11.4 million. What is the percent change? Better yet, what’s the formula for figuring it out?

If you don’t know the answer, or how to obtain it, you’re not alone. This kind of problem — which is in my son’s 7th grade math textbook — routinely stumps most journalists in most of the newsrooms across America.

I’ll avoid the temptation to moralize here. If you’re a journalist — if you have a pulse — you need to know this very basic operation. With it, you’ll have the power to analyze all kinds of data and even double-check the mayor’s math.

Here it is:

(the_new_number - the_original_number) / the_original_number

or, in the case of East Middletownburg:

(12.6-11.4) / 11.4

Remember (and you learned this in fifth grade) that operations in parentheses come first. That gives you this:

1.2 / 11.4 = .105 = 10.5%

So, the mayor’s new budget is a 10.5% increase over last year’s. Now you have something to write about!

Excel: Combine text and formulas in a cell

Whenever I analyze data in Excel, I format the spreadsheet to make it easier to read. A little attention to fonts, boxes and shading can help people understand the key data faster.

One way to give yourself some flexibility with formatting is to combine text and the results of a formula in a single cell. Just use the “&” operator to concatenate the text and the formula.

Consider this formula:

="Quantity: "&SUM(A1:A20)

Enter it into a cell, press enter and (assuming you have numeric values in cells A1 through A20) it will present this result in a single cell:

Quantity: 23

That kind of output’s pretty handy when you want to create a worksheet in your spreadsheet that aggregates data from other sheets while keeping the formatting simple.

Anthony

About me

I'm a journalist who works with words, code and data. I'm also a husband, father, musician, gardener and occasional poet. I love finding and telling great stories. I'm inspired by art, music and design that elevate. I pursue the truth. Data journalism's the focus here, but other topics will crop up. Thanks for reading.
LINKS & TRIVIA

microblog

places

LinkedIn
Twitter
Delicious
Tumblr
Spelling Bee blog
- Tales from our trips to the Scripps National Spelling Bee
360 Sports Jam
- My teen journalist's sports blog

data journalists

Brian Boyer
Aaron Bycoffe
Jack Gillum
Gregory Korte
Aron Pilhofer
Mark Schaver
Matt Waite
Ben Welsh
Derek Willis
Matt Wynn

from the home office

RSS TOP USA TODAY STORIES