Bits and Bytes
By BRIAN ANGLISS
Nothing beats a journalist’s intuition for something being “not quite right.” But sometimes you want more than just your gut feeling, especially when the feeling is coming from a new published paper, a study, or even just some data you've been collecting for a while. Here are some simple tools available in spreadsheet programs that can help you mathematically check if your gut feel is accurate or not.
The following assumes that you have access to MS Excel, 2003 or later. It’s also written from the perspective of a PC user, so adjustments will need to be made for those of you who use Macs instead. Open Office Calc should have similar functionality, but I haven’t verified it.
Before you can do any sanity checks on your data, you need to get it into your spreadsheet first. If your data isn’t already in Excel format, then it may be in .csv, .txt. or .dat format. Open Excel, click “Open,” and select “All Files” to see the non-.xls/.xlsx files. Don’t simply double-click the file, as it may bring up the file in a text editor instead of your spreadsheet program.
Vertical lines indicate where Excel will set column breaks.
Notice that additional breaks are required between the
"Mar" and "Apr" data columns.
While .csv files will often open without additional effort, .txt and .dat files will often bring up a box that defines your choices on how the file should be divided up into columns. Many .txt and .dat files have varying column widths and/or explanatory text at the start and end of the file. These confuse Excel’s ability to automatically open the file, so you'll need to select “Fixed Width” instead. Click “Next” and bring up the data preview, an example of which is shown.
Scroll down until you find the data and then click on the white spaces between numbers to define the columns with vertical lines. You can also move existing lines around if you need to.
Once you’ve broken up the data the way you want it, click “Finish.” This will result in chopped-up explanatory text. Once you've opened the data you’re interested in, create a new worksheet where you can do your calculations and generate your graphs. Much scientific data (especially climate data) is done one row per year, one column per month. This row-oriented format is a problem for Excel since it functions best in column format, especially when creating graphs. There are several ways to switch the row data into column data, but one of the easier ones is to use the Excel “transpose” option.
Transpose is at the bottom center of the box.
To transpose row data into a column, select all the data in the row you care about and copy it by hitting Control “C”. Then go to the calculation worksheet, select the starting cell, then select “Paste Special” in either the Edit tab or by right clicking. One of the options in the box that comes up is “Transpose.” Select it and click “OK.” This will paste the data as a column starting at the cell you select and filling down the column.
A more graceful way to do this is with Excel’s “VLOOKUP” function, but it takes some time to get familiar with exactly how the function works.
Once you’ve got your data into a column, you can graph it by selecting Insert: Chart.
Click on the corner squares to drag in additional data.
Click on the box edge to drag to different data.
Here’s a couple of hints for easier graphing. First, when you’re selecting a lot of data, instead of dragging your cursor over all the data, select the first cell of the data, scroll down to the bottom of the data and then Shift-click the last cell of the data. This will select all the data between the two selected cells as the data you want to graph. Second, if you want to add more than one or two series of data, start by graphing a single data series. Click on the graph and then look at the cells of data. You’ll notice that the data’s name is surrounded by a green box, the Y-axis data is highlighted by a blue box, and the X-axis labels are surrounded by a purple box. If you have multiple columns of data you want on the same chart, you can click the squa