|
|
|
|
Spreadsheets and the environment - basic level
Basic Excel exercise
Prepared for MSU’s Knight Center for Environmental Journalism’s 2006 Boot Camp by Jeff Porter, data library director of the Investigative Reporters and Editors and the National Institute for Computer-Assisted Reporting
Download a printable version of this tipsheet
This exercise is based on the Toxics Release Inventory database for 2003, released by the Environmental Protection Agency. It’s a slice of just one facility – the one that had 18 different reported chemicals for that year for air or water emissions.
First, download and open this xls file. A quick review of its content: company name and location, the reported chemical, and two sets of numbers: total air emissions (which EPA converts to pounds) and total water emissions (also converted). The EPA database also records how much of each chemical is placed in landfills and the like, but for the purpose of this exercise, we’ll concentrate on just air and water.
Basic calculations
First, we’re going to make some additional totals of emissions. Mouse around and click once on cell G1 and type in the word TOTALS. That’s our label for a column we’re going to fill in.
To fill in the column, hit ENTER after typing. That will take you to cell G2, and you can start your first formula. Always start with an = sign, then instead of telling to do math on particular number, tell it to use cell addresses:
=E2+F2 (Side note: Excel isn’t picky about case; you can use lower or upper case letters.)
Those two addresses reference the first row of numbers for air and water emissions. Column G, then, or TOTALS, is going to add them together. So after typing in the formula, hit ENTER. Your first number should appear:
Mouse back to highlight cell G2. Notice that above the D column, you can see the formula, not the number. That’s a good thing to remember: one way to check your work later.
That’s one calculation done. Mouse over to the highlighted G2 cell and notice that it has a small little box in the lower right corner. If you put your cursor directly over it, it’ll become a skinny plus sign:
Carefully click and drag down until you get to row 19 – now you’ve copied your formula all the way down Column G, calculating totals of air and water for all chemicals.
Another question: What’s the total of all those chemicals? To come up with totals, let’s create a new row. Mouse down to cell A21 – intentionally leaving a blank row. We’ll get to that later. Type in TOTALS in that cell. Now, mouse over to cell E21 – two rows down from the last chemical value for air emissions. Now, we’ll learn a magic word, one of many that Excel possesses: SUM. Instead of using a lot of plus signs, instead, apply this formula:
=SUM(E2:E19)
To parse that formula: The = sign, of course, is required for all Excel calculations. The word SUM is a function: in this case, totaling numbers. Every function in Excel needs parentheses. Inside those parentheses, there is a range of cells – starting with E2 and ending with E19 – the SUM function will work on.
Now, find the skinny plus sign again in cell E21 but instead of dragging it down, drag it across to the right, to include cell G21 – now, all the E-row numbers are filled in for you! Now you know: a total of 4,583,395.91 pounds air, 57,839.16 water and 4,641,235.07 total.
Sorting it out
A simple eyeball scan can show you which numbers are high, which ones are low – but Excel can handle much larger files and eyeballing data can be a problem with bigger sets of numbers. So we’ll do a sort to identify the top chemical, by pounds, of emission.
Back to Spreadsheets and the environment
