Climate Data Analysis with EXCEL
SO503 Spring 2006
THE TOP 10 SNOW STORM FOR BALTIMORE ARE AS
- 1ST FEBRUARY 15-18 2003......28.2
- 2ND JANUARY 27-29 1922......26.5
- 3RD FEBRUARY 11-12 1983......22.8
- 4TH JANUARY 7-8 1996......22.5
- 5TH MARCH 29-30 1942......22.0
- 6TH FEBRUARY 11-14 1899......21.4
- 7TH FEBRUARY 18-19 1979......20.0
- 8TH MARCH 15-18 1892......16.0
- 9TH FEBRUARY 15-16 1958......15.5
- 10TH JANUARY 25 2000......14.9
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:
- 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
- When were
the 10 largest rainfalls at BWI?
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
- 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
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
- Go to
Tools, Data Analysis, and select "Histogram"
- For help,
look in the index for "Histogram analysis tool"
- If you want the
graphical histogram, you have to check the box. By default it is
- 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