Climate Data Analysis with EXCEL

SO503 Spring 2006    


Weather data for BWI are available at: You want the local data records tab, and the Baltimore Daily Normals, Means, and Extremes

There are  monthly summaries, and we want to get them into Excel into an annual format.  Think about the easiest way to merge to monthly files, and look carefully at the format so that you do not have regrets about what you did while importing.  See if there is any missing data, if they change the format over the summer, or use special symbols that will affect numerical analysis.  Look carefully at the data format, and insure that you are getting the data into a usable form.

Questions on BWI weather:

  1. When were the 10 largest snowfalls at BWI?  Are there any caveats about this result?  (You can do a sort on the Data menu for this, perhaps on a copy of the data so that you can revert to it in correct order in Excel behaves incorrectly..)
  2. When were the 10 largest rainfalls at BWI?
  3. Get histograms for the records max temps, min temps, rainfall, and snowfalls, by decade--how many records occur in each decade.  (See the abbreviated directions below.)  Based on these results, do you think we are seeing global (or at least local) climate change?  Use a statistical test (chi squared) to compare the data to an alternative of no global change.
  4. Graph the record maxes and record min temperatures, and compare them to the average max and min.  Should you filter any of these data sets?  Why or why not?  Why do the records look so different from the averages?

Correctly put two tables (questions 1 and 2) and 4 histograms (question 3) and 1 graph (question 4) into your report.

Histogram Creation

  1. (Optional; you must do once if you don't see the menu choice in the next step.  This is probably the case on your own computer, but should not be the case in the lab.)  Go to Tools, Add-Ins, and insure that "Analysis ToolPak" is checked;  it might have to be installed.
  2. Go to Tools, Data Analysis, and select "Histogram"
  3. For help, look in the index for "Histogram analysis tool"
  4. If you want the graphical histogram, you have to check the box.  By default it is turned off.

 Chi-squared test

  1. Look up the CHITEST function in Excel. You have to create a hypothesis with a distribution, and compare that to the actual distribution, for the maximum and minimum temperature distributions.