WeatherBug Exercise 1 (Computer): Temperature Statistics: Max, Min, Range, Median, Mean

This exercise is a repeat of the previous one, but this time we are going to do the problem using a spreadsheet program.  Many people use spreadsheets as simple tables, formatting textual information neatly so it looks good for display. While you can certainly use a spreadsheet for that purpose, its overkill.  Your word processor program should be able to format tables very nicely and handle things like header rows which repeat when the table is split across pages and more.  Spreadsheets were made to do calculations on tabular data.

You’ll notice I keep using the generic “spreadsheet” instead of naming a particular one.  There’s a simple reason for that.  The exercise I’m about to do should work with just about any spreadsheet program.  I use OpenOffice and our exercise will show me using that.  If you use Gnumeric, or Microsoft Excel, or something else, you can do the same calculation and the names of the functions we will use may even be the same.  I’ve been using OpenOffice since before it became OpenOffice, back when it was still StarOffice and the vendor was a German company.  OpenOffice runs on various versions of Microsoft Windows, Mac OS X, and Linux.  It is free to download and use, so if your spreadsheet program won’t do what I’m showing, just download and install OpenOffice and you can follow this exercise exactly.

The first part of our exercise will be to cut-and-past the WeatherBug data into our spreadsheet.  This is about as hard as it sounds, which is to say, not hard at all.  Navigate in your browser to the WeatherBug site and the daily data table.  Also, open a new spreadsheet.  Then simply highlight/select the data table, copy it, then paste it into OpenOffice.

There is one annoyance in the formatting of the WeatherBug data.  You will notice that the column which shows the time of the measurement doesn’t really format quite right.  It turns out that there is an extra space between the time and the “AM” or “PM” string and that inhibits OpenOffice from treating the string as a time.  For our purposes it won’t matter, but it will come back to haunt us in future examples.

Okay, so what is an average?  There are three things which can legitimately be called an average: the mean, the median, and the mode.  Most people equate “average” with “mean” but we’re simply going to avoid the term “average” as too vague.  So let’s calculate the mean of our temperature data.

The mean is simply the sum of all the values divided by how many values there are.  There are multiple ways to do this in a spreadsheet and in the true spirit of forcing you to learn the hard way first, we’re going to take the hard way first…. Let’s start by deleting all the columns we don’t need from our spreadsheet to reduce the clutter.

Next, we’re going to show how to calculate a sum the hard way, but adding up things incrementally.  In the column to the right of the temperatures, we’re going to create a running total.  In cell C3, enter the formula ‘=C2+B3’.  This takes the contents of cell C2 and adds the contents of cell B3 and displays them in the current cell (C3).  Hit enter and you’re done.

Okay, now we’re going to repeat for cell C4.  Except we’ll take a short-cut. Click on cell C3 again, then copy the contents.  This will be Alt+C on Linux and Windows, or Command+C on Mac OS X.  Click on cell C4 and paste (Alt+V or Command+V).  The spreadsheet will not only copy the formula but adjust it for the relative movement.  That is, you moved down one row, so it automatically changes the formula entries by one row so it reads ‘=C3+B4’.  Repeat this all the way down the column.