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.

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

The process of calculating an average is not hard, but it can be tedious. So, in math class, the children are often given a set of made-up numbers that make the process easier. In this exercise, you don't get a break; we're going to use real data from the WeatherBug at P.S. 102 for December 1, 2009.  Here are the hourly temperatures:

Here are the exercises:

1. What was the low temperature (minimum)?
2. What was the high temperature (maximum)?
3. How much did the temperature change that day (range)?
4. What was the median temperature?
5. What was the mean temperature?

Solving question 1 requires that we scan through the list of temperatures to find the smallest value. The values at both 6:00 AM and 7:00 AM are the same and are the smallest. So the low temperature on December 1st was 36.2°F.

Solving questiong 2 is like question 1 but we are looking for the largest temperature. That occurred at 4:00 PM when the temperature got up to 46.1°F.

For question 3, the temperature change for the day is just the difference between the high and the low that we found in questions 1 and 2. It was almost 10°F, just a little less at 9.9°F.

For question 4, it's easiest if we reorder the temperatures from lowest to highest so we can find the value in the middle.

Here's a strategy note: if we had carefully read all five questions before starting, we would have noticed that doing question 3 first, which requires that we sort the temperatures to they are all in order (either highest to lowest or lowest to highest, whichever you prefer) would make solving questions 1 and 2 easier. The moral: read all the questions before you start.

For question 5, there is no shortcut. The mean is defined as the the sum of all the values divided by the count. We have 24 temperature measurements and we're just going to have to add them up. If you are doing this in class, I would recommend rounding the temperatures to the nearest degree before adding. That will give an answer which is close enough for our needs. And if you are doing this exercise as a homework assignment, calculators should be allowed. For an average, I get 41.6°F if I use the exact numbers in the columns. And if I round to the nearest degree, I get 41.7°F. In either case, if I round the end result to the nearest degree, I end up with 42°F as the mean temperature for December 1st.

Here's another tip: in math class we often go crazy with getting the exact answer. That's because for things like 1+1=2, there's only one answer and it is exact. But in the sciences, we are often only interested in an approximate answer. For most purposes, knowing the mean temperature to the nearest degree is enough. The difference between 41.6°F and 41.7°F isn't going to make you change your mind about how you dress for the day!

And here's another dirty little secret. While I am not sure about the WeatherBug temperature sensor, most digital temperature sensors have an accuracy of somewhere between 0.1°F and 0.9°F. So going crazy with keeping all those decimal places just doesn't make sense.