Global Temperatures and Sea Level Trends Spreadsheet Project
by
Heather Sanderson
BS, University of Connecticut, 1999
A Spreadsheet Project Submitted in Partial Fulfillment of
The Requirements for
Teaching Math and Science with Technology (656)
Instructor: Dr. Hari P. Koirala
Department of Education
Eastern Connecticut State University
August 3, 2018
Introduction
I do not have any experience using spreadsheets in my seventh grade science classroom. I know the eighth grade science teachers at my school use Excel for graphing data in their physics classes. I do use PowerTeacher for my gradebook, which is a user friendly kind of spreadsheet. I have had some experience using Excel for graphing in previous science courses I have taken. Spreadsheets are beneficial for graphing in science class because they create a graph faster than one made on paper with a pencil, allowing students to quickly analyze the relationship between variables. Spreadsheets are also a valuable tool when importing and working with large sets of data.
Spreadsheets can certainly play a role in science and math classrooms. The article The effect of using Microsoft Excel in a high school algebra class shows a slight improvement in test scores for students who used Microsoft Excel versus those who did not, and the students who used Excel also had a positive opinion of using it to learn algebra (Neurath & Stephens, 2006). In the article Technology-Rich Mathematics Instruction the authors discuss that by “using electronic spreadsheets, students generate and pursue questions to understand how changing one variable affects other variables” (Thach & Norman, 2008, p. 153). I know the author is speaking about exploring math equations, but similarly in science class, students can manipulate the independent variable to see what happens to the dependent variable and quickly create graphs of their findings. This article goes on to discuss an engaging credit card activity where the students use spreadsheets to explore how interest and the amount paid monthly affect the overall payment. This credit card activity connects the use of spreadsheets with The National Council of Teachers of Mathematics teaching practices, noted as “use and connect mathematical representations” and “pose purposeful questions” (National Research Council, 2012). Spreadsheets are widely discussed in both the math and science curriculum standards. Specifically, use of spreadsheets is mentioned in the Common Core State Standards for Mathematics under practice number 5 “Use appropriate tools strategically” (CCSS, 2010, p.7). Spreadsheets are also mentioned throughout the National Science Framework. Providing students with modeling tools such as spreadsheet models is discussed in the National Science Framework as a way for students to “develop a level of facility in constructing and applying appropriate models” (National Research Council, 2012, p. 59). Spreadsheets are also mentioned as a way for students to organize data “especially large data sets” (National Research Council, 2012, p. 62). One of the goals for twelfth grade students in the Science Framework is to “use spreadsheets, databases, charts, graphs, statistics, mathematics, and information and computer technology to collate, summarize, and display data and to explore relationships between variables, especially those representing input and output” (National Research Council, 2012, p. 63). Finally, spreadsheets are mentioned in the National Science Framework as a way for students “to record data and then perform simple and recurring calculations from those data, such as the calculation of average speed from measurements of positions at multiple times” (National Research Council, 2012, p. 66).
Project
The purpose of my project is to use Excel spreadsheets to graph global temperature trends as well as sea level trends in my seventh grade earth science course. More specifically, my students will import large data sets into Excel spreadsheets from NASA’s website, insert a formula that will calculate the actual temperature from the temperature anomaly, and graph both global temperature and sea level trends from 1880 to the present, as well as make a determination as to why the trend they discover is happening. In the article Developing the Computational Thinker the authors suggest that spreadsheets should be used in the science classroom to import large sets of data as well as to “make calculations, graph and chart data, conduct analyses, and solve problems” (Smith and Madar, 2017, p.10). My project will help students become efficient in using spreadsheets to import large sets of data, graph the data, and analyze that graphed data to understand what is happening with our climate and sea level. This project was adapted from the NASA Jet Propulsion Laboratory activities for educators. The project covers NGSS Science Standard “ESS3.D: Global Climate Change” under “MS-ESS3 Earth and Human Activity” (NGSS, 2013, p. 84).
Project Activities
For my first activity the students will be graphing global temperature trends using Excel spreadsheet. In this activity students will be importing the temperature anomaly for every month from 1880-2015 from the NASA Website. Next they will use a formula to find the actual temperature for each month and create a graph to see the temperature trend since 1880.
Step # 1
We will discuss the differences between weather and climate, as well as what global warming and climate change are.
Step#2
The students will log on to the following link from NASA: https://www.jpl.nasa.gov/edu/teach/activity/graphing-global-temperature-trends/
The students will then click on the global monthly temperature data text file, highlight and copy the data, and then paste the data into an Excel spreadsheet. This data will show the month and year in the first column and the temperature anomaly in ⁰C in the second column.
Step #3
The students will have to create a third column with the formula for actual temperature. They will click on cell C5 and enter =13.9+. They then have to click on Cell B5 and hit enter. Next they have to click in Cell C5 and grab and fill the data into cells C6-C1648.
Step #4
The students will then graph their data by inserting a line chart and highlighting their data that needs to be inserted.
Step #5
The students will answer conclusion questions analyzing the data they have graphed. The students will printout the graphs they created on Excel and hand them in with their completed questions for grading. They will be assessed on whether their graph is correct and has properly labeled axes, as well as if their questions are completed with enough detail.
For the advanced student I would have them predict the global temperature for 2025, 2050, and 2100.
For modifications I will have the student work with a smaller data set for instance from 1950-present to determine trends.
My second activity builds off the first one. In this activity my students will graph sea level trends from 1880 to 2013 using Excel spreadsheets.
Step #1
We will discuss how melting ice sheets and glaciers, as well as thermal expansion can change sea level.
Step #2
Students will log onto the following NASA website:
https://www.jpl.nasa.gov/edu/teach/activity/graphing-sea-level-trends/
There are 20 sets of data going back to 1880 and each student will be assigned their own set to copy and paste into an Excel spreadsheet from the text file on the website.
Step #3
Students will create a line chart on the Excel spreadsheet program.
Step #4
Students will answer questions based on the data set they were given.
Step #5
Students will go back to the NASA website https://www.jpl.nasa.gov/edu/teach/activity/graphing-sea-level-trends/
This time they will select the full data set from the text files and copy and paste the data into an Excel spreadsheet. They will again highlight data and create a chart to analyze the trend.
Step #6
Students will analyze the trend in their first data set compared to the full data set. Students will also be asked to connect what was learned from the sea level data to the global temperature data from activity #1. The students will answer conclusion questions analyzing the data they have graphed. The students will printout the graphs they created on Excel and hand them in with their completed questions for grading. They will be assessed on whether their graphs are correct and properly labeled on the axes as well as if their questions are completed with enough detail.
For the advanced student I could have them predict the sea level change in 2050 and what changes we would specifically see in the United States if that sea level happens.
For a modification I could have a few students work in groups of 2 since I only have 20 data sets to assign and my class size is usually 24-26.
Final Thoughts
This past year was our pilot year of implementing the new NGSS Standards, but we were unable to get to every standard. The global climate change standard was one we were not able to cover, and I will be teaching it for the first time this year. This project is worthwhile because the students are working with real data from NASA and can make their own conclusions based on the actual data. There is so much political rhetoric around the subject of climate change, and students have to work with the actual data so they can form their own opinions. This project will also show students how easy it is to work in Excel with very large data sets. The students will be importing temperature and sea level data taken from every month all the way back to 1880. A concern that I have is that this is the first time my students will be using Excel, and I know they will have many questions I will have to address throughout the assignment. Overall I think this is a valuable lesson on a real world problem.
Worksheet-Activity #1
Name _____________________________________ Date _______________________ Class ________
Graphing Global Temperature Trends
(Adapted from NASA )
You will use Microsoft Excel to chart the change in global temperature from 1880-2015.
Background
Scientists have concluded that our climate is changing, that global temperatures are on the rise, and that there are serious consequences to these rising temperatures.
Weather and climate are two frequently confused terms that refer to events with broadly different special and time scales. Weather refers to atmospheric conditions that occur locally over short periods of time-from minutes to hours or days. Familiar examples include rain, snow, clouds, winds, floods, or thunderstorms. Weather is local and short term. Climate, on the other hand, refers to the long term regional or even global average of temperature, humidity, and rainfall patterns over seasons, years or decades. Climate is regional or global and long term; weather is local and short term.
Two other terms that are often incorrectly used interchangeably are “global warming” and “climate change”
Global warming refers to the upward temperature trend across the entire Earth since the early 20th century-and most notably since the late 1970’s. Though there are many different greenhouse gases, carbon dioxide (CO2) is the one that has been on the rise during the last century. Increasing the concentration of greenhouse gases causes the Earth greenhouse to overheat.
Climate change refers to a broad range of global phenomena created predominately by burning fossil fuels, which add heat-trapping gases to Earth’s atmosphere. These phenomena include the increased temperature trends described by global warming, but also encompass changes such as sea level rise: ice-mass loss in Greenland, Antarctica, the Arctic and mountain glaciers worldwide, shifts in flowers and plant blooming, and extreme weather events.
Climate change is driven by an increase in global temperature. But how do we know global temperatures are on the rise? We analyze temperature data, including daily temperature readings and monthly or annual average temperatures. The longest-running global record starts in 1880. Data is obtained from land stations and ships around the globe. More recently, satellites are used.
This activity allows you to examine real science data and draw your own conclusions about trends in global temperature.
What do you know about global temperature trends?
____________________________________________________________________________________________________________________________________________________________
You will be looking at data sets that show monthly measurements of the average temperature over approximately 136 years.
The data is contained in two columns. The first column contains date information. The first four digits represent the year and the last two digits represent the month.
188001=Jan 1880
188002=Feb 1880
The second column is the Global Temperature Anomaly in °C, compared with the average temperature, 13.9°C, from 1901 to 2000.
We will now look at the temperature data and import it into an excel spreadsheet.
Type in the following link
https://www.jpl.nasa.gov/edu/teach/activity/graphing-global-temperature-trends/
-Click on global monthly mean temperature data text file
-Highlight and copy the data
-Open an Excel spread sheet
-Paste data into spreadsheet
-Click on data and then choose text to columns to make sure your columns are separated properly.
-Click delimited>next>space>finish
-Your columns should be lined up properly.
-Delete year it will prevent graph trend line from appearing correctly.
-With data open click on third column and label it actual temperature.
-You will create a formula that will fill the third column with the actual temperature.
To fill third column:
-Click on cell C5 and enter =13.9+
-Then click on Cell B5 and click enter
-Click in Cell C5, grab and fill data into all the cells, C6-C1648
Now you will graph your data and evaluate the results.
To graph in Microsoft Excel:
-Click to select a blank cell
-From the insert menu click chart and select line
-In the blank chart area that appears, right click and choose Data
-Click in the Chart data range box and select all of the temperature data in Column C from Cell C5-Cell C1648
Your line chart will be displayed.
Print out graph and attach to questions.
Discussion Questions:
1. Explain the overall trend for global temperature
__________________________________________________________________________________________________________________________________________________________________________________________________________________________________________
2. Why does the graph zigzag so much?
____________________________________________________________________________________________________________________________________________________________
3. Using the data given, predict what the global temperatures will be in 2020 and how you came up with that number.
__________________________________________________________________________________________________________________________________________________________________________________________________________________________________________
4. What is the difference between weather and climate?
__________________________________________________________________________________________________________________________________________________________________________________________________________________________________________
5. How can we separate fact from fiction in science?
__________________________________________________________________________________________________________________________________________________________________________________________________________________________________________
6. Compare the graph you made with a historical timeline of events in the United States and the world. What associations do you see?
__________________________________________________________________________________________________________________________________________________________________________________________________________________________________________
7. What steps can you take to reduce your impact on global temperature rise?
__________________________________________________________________________________________________________________________________________________________________________________________________________________________________________
Worksheet-Activity #2
Name _______________________________ Date _________________________ Class __________
Graphing Sea Level Trends
(Adapted from NASA)
In this activity, you will use sea-level rise data to create models and compare short-term to long term trends. You will then determine whether sea-level is rising based on the data.
For this activity, 130 years of sea-level measurements have been divided into 20 data sets for examination. You will examine all 20 data sets to learn how scientists study data over short and long periods of time.
Background
Sea level is measured by monitoring stations on the shoreline and at sea. Satellites also collect data on the height of sea level. There are more than 120 sea-level monitoring stations in the US and 240 additional stations worldwide. By looking at data from these stations over periods of 30 years or more, trends can be identified at individual stations and compared to other stations. This gives scientists useful information about local conditions. The data can also be used to calculate the global average sea level and study it over time, giving scientists a picture of what’s happening to the ocean on a planet-wide scale. Sea level has been measured at some stations for more than a century, providing data going back to 1880.
It is important to note that when sea level rises, the total amount of water on Earth isn’t increasing. Instead, the volume of liquid that fills the ocean basin is growing, raising the elevation of the sea’s surface and spilling ocean water onto low-lying land. The extra volume of seawater comes from two places:
1. Melting ice sheets and glaciers on land add water to the sea
2. The second and less obvious cause for rising sea level is thermal expansion. Water expands as it warms, so the more heat energy the ocean absorbs, the more space water requires, so as atmospheric temperature rises, so does the ocean temperature.
What is the difference between sea level and tides?
__________________________________________________________________________________________________________________________________________________________________________________________________________________________________________Do you think sea level is rising, falling, or remaining steady? Explain why you chose your answer.
________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________
Type in the following link:
https://www.jpl.nasa.gov/edu/teach/activity/graphing-sea-level-trends/
There are 20 sets of data; I will assign each student/students one set of data to analyze.
-Click on text files and then choose your data set
-Copy your data set.
-Open a new Excel sheet and paste your data set.
-Delete Time from cell A1 otherwise set will not graph properly
-Highlight all data
-Click on insert and choose line
Graph should appear (print graph and attach to questions when packet is complete)
Answer the following questions:
What year and month does your data set begin? __________________________________
What year and month does your data set end? ___________________________________
What is the global mean sea level (GMSL) at the beginning of your data set? ________________
At the end? _______________
What is the difference? ______________________
What does the GMSL indicate is happening to sea level across the span of your date? ____________________________________________________________________________________________________________________________________________________________
Go back to
https://www.jpl.nasa.gov/edu/teach/activity/graphing-sea-level-trends/
-Go to text files and select full set of data.
-Copy and paste into excel like you did before.
- Highlight data and select insert line chart to graph your data.
Print graph and attach to packet when complete.
1. How does your data set compare to the complete 130-year data set? Does your data set show the same trend as the long term trend? If not, why do you think it is different?
____________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________
2. Now that you have seen the trends in both global temperature trends and sea level trends discuss in depth your thoughts on how these two activities connect with each other.
__________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________
Global Temperature and Sea Level Trends Spreadsheet Project Rubric
Proficient (6)
Developing (4)
Needs Improvement (2)
Score
Excel Spreadsheet Use
Great understanding of how to use Excel Spreadsheet
Good understanding of how to use Excel Spreadsheet
Improvement needed on using Excel Spreadsheet
Ability to identify trends
Trends are identified and explained clearly
Trends are identified but not explained clearly
Trends are not identified
Excel Graph
Excel graph is complete, correct, and all axes and title labeled
Excel graph is complete and correct, but some labels missing
Excel graph is incomplete
Questions
All questions are complete with well thought out answers and enough detail provided
All questions are complete, more detail needed
Some questions are not complete or are incorrect
Connection between two activities
Connection between activities is explained in great detail
Connections between activities is explained
Did not explain connections between activities
Total ____ /30
References
Common Core State Standards Initiative. (2010). Common core state standards for mathematics.
Retrieved from http://www.corestandards.org/assests/CCSSI_Math%20Standards.pdf
NASA Jet Propulsion Laboratory California Institute for Technology. (N.D.). Graphing Global Temperature Trends. Retrieved from https://www.jpl.nasa.gov/edu/teach/activity/graphing-global-temperature-trends/
NASA Jet Propulsion Laboratory California Institute for Technology. (N.D.). Graphing Sea- Level Trends. Retrieved from https://www.jpl.nasa.gov/edu/teach/activity/graphing-sea- level-trends/
National Research Council. (2012). A framework for K-12 science education: Practices,
Crosscutting concepts, and core ideas. Washington, DC: National Academy Press
Next Generation Science Standards. (2013). Next Generation Science Standards: For states by states. Washington, DC: Author. Retrieved from http://www.nextgenscience.org
Neurath, R.A., & Stephens, L.J. (2006). The effect of using Microsoft Excel in a high school algebra class. International Journal of Mathematics Education in Science and Technology, 37(6), 721-756.
Smith, B., & Mader, J. (2017). Developing the Computational Thinker. The Science Teacher, 84(2), 10. Retrieved from http://eds.a.ebscohost.com.ecsu.idm.oclc.org/ehost/pdfviewer/pdfviewer?vid=27&sid= 8788eaf7-745f-485b-ab74-d54ad5379565%40sessionmgr4009
Thach, K.J., & Norman, K.A. (2008). Technology-rich mathematics instruction. Teaching Children Mathematics, 15(3), 152-158