US Historical Climate: Excel Statistical
This material is replicated on a number of sites as part of the SERC Pedagogic Service Project
Summary
Learning Goals
Use Excel To:
- Import US Historical Climate Network data
- Calculate mean, variance, standard deviation, maximum, minimum, and trends estimates for temperature data at a station near your town.
- Estimate statistical error in trend estimates
- Calculate temperature anomalies
- Use a running mean filter to smooth data
- Graph temperature data, anomalies, and trend estimates.
Context for Use
Teaching Materials
- A PDF file of Using Excel and USHCN Data for Introductory Statistical Analysis (Acrobat (PDF) PRIVATE FILE 150kB Apr2 04)
- An Excel Answers Document to the Activity (Excel PRIVATE FILE 104kB Apr2 04). This Excel document can be used for either:
- Introducing concepts and ideas in an interactive lecture;
- Making transparencies or Power Point slides;
- Checking student results.
- Sample images of what's in the Excel Answer Document and what students are expected to accomplish.
Figure 1. Monthly mean temperatures for Maximum (January) and Minimum (July) variability months for Vancouver, WA example. (click image to enlarge)
Figure 2. Annual mean temperature, least squares trend estimate, and smoothed data for Vancouver, WA example. (click image to enlarge)
Teaching Notes and Tips
This activity will take students about two hours to complete. Students unfamiliar with Excel should complete an introductory Excel activity (see resources) before working through this activity. Students should be able to work through this activity at home or in a computer lab will no supervision. Students more familiar with Excel can work through this activity more quickly.
Although this activity focuses on temperature data, precipitation data is also available at this USHCN site as a possible extension to this activity.
Assessment
Grading the completed activity sheet provided can be used as an assessment of student understanding. The future success of students on other Excel related activities is also another useful assessment tool.
A link to an Excel Answers Document is provided above with several sheets representing different levels of completion for this activity. This can help instructors: assess student success for this activity; answer student questions that come up; and quickly familiarize themselves with the activity.
References and Resources
The World Population Activity I or Sea Floor Spreading Activity I are good Excel tutorial activities with geoscience content for students with no Excel experience. Other geoscience examples that use Excel are also available.
Global temperature data 1856-2000 (more info) from P. D. Jones,D. E. Parker,T. J. Osborn, and K. R. Briffa.
Global temperature data 1880-2003 (more info) NASA GISS surface temperature analysis.