Eight Simple Ways to Let the Spreadsheet Do the Math So You Can Focus on the Story

In other instances, journalists would report that it was fair for workers to get the same percentage increase in wages, without realizing that a 3% increase for someone making $150,000 (it’s $4,500) is much greater than a 3% increase for someone making $30,000 (it’s $900). Journalists would also fail to use rates for putting raw numbers in perspective. One city would be called the murder capital of a country based on the total number of murders, despite having a much lower murder rate than other cities. A road intersection would be deemed the most perilous based on total number of collisions, rather than the rate of collisions compared to traffic.

But the public shaming of journalists who made mathematical errors left reporters, as the long-time journalist and top data journalism instructor Sarah Cohen wrote in her book, “Numbers in the Newsroom,” with “the impression we can’t use any numbers without fearing retribution.” (Cohen’s book is an invaluable guide on journalism and math.)

Yet it was in the late 1980s that a small band of journalists began to embrace the power of accurate numbers and calculations as they began to work with data. They also discovered the spreadsheet. And, inspired by Philip Meyer’s book, “Precision Journalism,” they came to see the power of math and numbers, rather than scorning or avoiding them.

Data journalism workshops given by Meyer at the University of North Carolina and by Investigative Reporters and Editors (IRE) with its companion organization, NICAR, drew hundreds of journalists eager to learn data analysis. At those workshops and then at NICAR conferences, they received training that included math — training that was seldom, if ever, offered in classrooms for journalists or newsrooms. In fact, journalism professors wanting to keep up with the profession attended those workshops and became the few including math and numbers in their classes.

In those workshops, I and my colleagues found the previous teaching of math had lacked the appropriate approach and perspective. The best approach demystifies “math” and focuses on the basics that allow journalists to apply math in a practical way — that is, to summarize numbers, put them in context and determine if the numbers are misleading or lies.

The result of the workshops — which spread globally — was an increased understanding of numbers and thus the ability to write more lucidly about those numbers. Numbers were not boring if they revealed shocking ethnic disparities, large numbers of failing bridges or alarming rates of murder.

It was clear that it was much easier to deal with numbers if the teaching led to that immediate illumination about a topic.

Another manifestation is the Philip Meyer Awards, international awards given by IRE, that recognize the best uses of social science in journalism. Year after year, since 2005, these awards show the progress that has been made in the field’s numeracy. For example, an investigation by Bayerischer Rundfunk and Der Spiegel, “No Place for Foreigners: Why Hanna Is Invited to View the Apartment and Ismail is Not,” revealed discrimination against foreigners in the German housing market through a large-scale survey of landlords. They found that potential renters with Arab and Turkish names were frequently ignored.

In the US, journalists in several newsrooms have shown widespread cheating on standardized tests by showing that test scores were way too high based on analysis of previous years’ scores. In a similarly math-based investigation, ProPublica uncovered a disturbing trend: Temporary workers are hurt up to six times the rate of permanent employees, and their injuries are more severe.

And there are many examples of smaller but effective stories using numbers. Years ago, a reporter, who had just received training in spreadsheets, found the city she covered had uniformly miscalculated percentage changes in its annual budget. Some reporters found political associates in governments receiving much larger salaries than regular employees. Others calculated serious cost overruns in government programs.

The Spreadsheet as the Basic, Starter Tool

With just a spreadsheet, a journalist can let the software do the counting and calculating, allowing them to concentrate on the purpose and result of their inquiry. It also opens the door to understanding more advanced statistics, and the use or misuse of statistics by governments and businesses.

The mathematical tools in a spreadsheet can be divided into two groups: data management and calculations.

Data management, in which the counting is automatically completed within the spreadsheet, includes:

• Filtering data based on a criteria
• Sorting to bring meaning to numbers by looking at them from high to low or low to high
• Summarizing by grouping topics into categories, and summing or counting the numbers associated with each category

Important basic calculations, some of which can be automatically executed and some which must be performed by the journalist, include:

• Summing up a column or row of numbers
• Determining the mean or median of a column
• Calculating percentage difference
• Calculating a rate
• Calculating a ratio

Data Management

The Pivot Table icon has been selected in the left hand corner.

The Pivot Table allows you to count the number of each business with licenses by choosing from a list in a selection screen.

This Pivot Table shows the number of dealerships licensed under a unique business name.

Sorting from high to low based on number of licenses, it’s possible to see that the corporation Walmart has the most licenses to sell guns in Missouri.

Calculations