Climate Data Analysis with EXCEL

SO503 Spring 2006    

THE TOP 10 SNOW STORM FOR BALTIMORE ARE AS FOLLOWS... 

• 1ST FEBRUARY 15-18 2003......28.2 INCHES 
• 2ND JANUARY 27-29 1922......26.5 INCHES 
• 3RD FEBRUARY 11-12 1983......22.8 INCHES 
• 4TH JANUARY 7-8 1996......22.5 INCHES 
• 5TH MARCH 29-30 1942......22.0 INCHES 
• 6TH FEBRUARY 11-14 1899......21.4 INCHES 
• 7TH FEBRUARY 18-19 1979......20.0 INCHES 
• 8TH MARCH 15-18 1892......16.0 INCHES 
• 9TH FEBRUARY 15-16 1958......15.5 INCHES 
• 10TH JANUARY 25 2000......14.9 INCHES

Weather data for BWI are available at:   http://www.weather.gov/climate/index.php?wfo=lwx 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.

• If you have to adjust columns, be very careful about whether you use tabs or spaces.  You should not mix those in a single document that has data for numerical analysis.  You should also be using Courier New as your font.

• You can replace tabs in Word.  Search on "^t", which is how Word looks for tabs.  ("^p" lets you look for paragraph markers)

• To see the spaces and tabs and paragraph markers in Word, Use "Tools", "Options", "View", and then check Formatting Marks "All".  While this can be a pain in normal word processing if you are not used to it, it can be vital in working with data files.

• You cannot do a search and replace on "*" in Excel.  You must do that in Word or Notepad.

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.