Looking at Trends Over Time – Dungeness Project
Student Name:_______________
Each student will be responsible for one month. Your Month: _________________
We have access to daily Port Angeles weather data (including maximum temperature, minimum temperature
and rainfall) and Dungeness River flow data since 1938!!!
Your job (this assignment) is to analyze data trends for your month since 1938.
First, though, answer the following questions related to your predictions for what your data will show:
Average Daily Predictions for Your Month since 1938:
What do you think your month‟s average daily maximum temperature is since 1938? ________
What do you think your month‟s average daily minimum temperature is since 1938? ________
What do you think your month‟s average daily rainfall is since 1938? _______
What do you think your month‟s average daily flow is since 1938? _______
Average Monthly Predictions for Your Month since 1938:
How do you think the monthly average for your month has changed over the years from 1938 to 2007?
Add data ranges to the y-axes, label, and then draw your predictions on these four graphs:
Average Daily Predictions WITHIN your month for each day (e.g. Sep. 1, 2, 3, etc.) since 1938:
How do you think the daily average for your month changes from the first to the last day of the month?
Add data ranges to the y-axes, label, and then draw your predictions on these four graphs:
Instructions to find the actual results
In order to isolate your month’s data (and not get confused by all the other meddling months):
1. Open the Microsoft Excel file titled “PAWeatherData.xls” (located in „Student Share‟ / Lieberman /
Dungeness Project / Weather Data)
2. Click on Row 5 to select the entire row (this is the row with the column headers, just above the row with
actual data in it). With the row selected, hold „Shift‟ + „Control‟ and press „End‟ (this will select all the
rows that have data – this is around 25,000 rows).
3. From the menu at the top of Excel, click „Data‟ then „Sort.‟ From the box that appears, sort by „Month‟
(Column B) THEN BY „Year‟ (Column A) – this will put your month all together.
4. Select all rows that are not your data and delete them, leaving only your month‟s data.
5. Select all the cells by clicking on the upper-left grey cell or by going to „Edit‟ then „Select All.‟ Copy,
then, one at a time, click on the next three worksheets at the bottom of the document (titled Avg. Daily,
Avg. Monthly, and Avg. Within Month) and paste all this data (this is the full data set for your month,
which you will then need to sort in different ways in each worksheet to calculate your results).
In order to calculate Average Daily Results for your month:
6. Click on the tab at the bottom of the document titled „Avg. Daily,‟ then push „Control‟ + „End‟ and take
mental note of the row number where the final data points are (this should be somewhere between 2,000
and 3,000).
7. In cell D4, type „=Average(D6:D3000)‟ (insert the final row number in place of the 3000) and then hit
enter. (This is the formula Excel uses to calculate averages – in this case it will take the average of all
the data in column D – Maximum Temperature.) The number that appears is the average daily result for
maximum temperature since 1938!!!
8. Click on D4 and copy it. Then select cells E4, F4, and H4 and paste what you‟ve copied. This pastes
the formula, not the value, and it should modify the formula to include the data in the different rows,
showing you average daily results for minimum temperature, rainfall, and flow since 1938!!! You can
put these numbers into the Final Results section of this document. Save your work now by doing „Save
As‟ and saving your file in „Student Share‟/ Lieberman / Dungeness Project / Monthly Spreadsheets /
“YourName+Month”
In order to graph Average Monthly trends since 1938:
9. Click on the tab at the bottom of the document titled „Avg. Monthly.‟ (This worksheet should already
have the data for your month that you put there in step 5).
10. In cells I5, K5, M5, and O5 type „Month Start,‟ in J5, L5, N5, and P5 type „Month End,‟ in Q5 type
„Year‟ and in cells R5 through U5 type „Avg. Max. Temp., Avg. Min. Temp., Avg. Rainfall, and Avg.
Flow‟
11. If your month has 31 days, in I6, type D6, in J6, type D36, in I7, type D37, and in J7, type D67. If your
month has 30 days, in I6, type D6, in J6, type D35, in I7, type D36, and in J7, type D65. If your month
is February, talk with your teacher.
12. If your month has 31 days, in K6, type E6, in L6, type E36, in K7, type E37, and in L7, type E67. If
your month has 30 days, in K6, type E6, in L6, type E35, in K7, type E36, and in L7, type E65. If your
month is February, talk with your teacher.
13. If your month has 31 days, in M6, type F6, in N6, type F36, in M7, type F37, and in N7, type F67. If
your month has 30 days, in M6, type F6, in N6, type F35, in M7, type F36, and in M7, type F65. If your
month is February, talk with your teacher.
14. If your month has 31 days, in O6, type H6, in P6, type H36, in O7, type H37, and in P7, type H67. If
your month has 30 days, in O6, type H6, in P6, type H35, in O7, type H36, and in P7, type H65. If your
month is February, talk with your teacher.
15. Select cells I6, I7, J6, and J7. With the cells selected, move your cursor over the bottom right corner of
what‟s selected until you see the all-black bold plus-symbol. When you see the plus symbol, click the
mouse, and drag down until row 76 (cells I76 and J76) is also selected. Push enter. (This will repeat the
pattern you started, which will allow us to calculated averages for each year from 1938 to 2007).
16. Repeat step 15 with columns K+L, M+N, and O+P.
17. In cell Q6, type 1938 and in cell Q7 type 1939. Select these two cells, and follow the same basic
procedure described in step 15 above for column Q (this make a series of years from 1938 to 2007.
18. In cell R6, type “=AVERAGE(INDIRECT(I6):INDIRECT(J6)).” In cell S6, type
“=AVERAGE(INDIRECT(K6):INDIRECT(L6)).” In cell T6, type
“=AVERAGE(INDIRECT(M6):INDIRECT(N6)).” In cell U6, type
“=AVERAGE(INDIRECT(O6):INDIRECT(P6)).”
19. Copy the contents of R6, select R7 through R76 and paste. (This will put the averages for your month
for each year into the cell corresponding to the correct year.)
20. Follow step 19 using S6, T6, and U6.
21. Follow these instructions to make the graphs.
22. Highlight the data in cells Q5 through Q76 (Year). Hold „Control‟ and highlight the data in cells R5
through R76 (Avg. Max. Temp). With these selected, click on the Chart Wizard icon (see picture below
for details on where to find the Chart Wizard icon).
23. Choose „X-Y Scatter plot,‟ then „Scatter with data points connected by smooth lines,‟ then click Finish.
(Or, walk through the Wizard to customize axis titles, etc. as desired.)
24. Save the graph and be ready to work with it again in the „Trendlines‟ assignment.
25. Follow instructions 22 – 24 three more times, each time using data from row Q as the first column, and
the other times using rows S, T, and U. (This will give graphs of Average Monthly trends for all
parameters)
26. Again, save your overall spreadsheet.
In order to graph Average Daily trends WITHIN your month (e.g. Sep. 1, 2, 3, etc.) since 1938:
27. Click on the tab at the bottom of the document titled „Avg. Within Month.‟
28. Click row 5 to select the entire row. With this selected, push „Control‟ + „Shift‟ + „End‟ to select all the
data.
29. Go to the „Data‟ tab at the top of the page, then „Sort.‟ Within the dialogue box, do sort by „Month‟ then
by „Day‟ then by „Year‟ to get your data arranged for calculating average daily values within your
month.
30. In cells I5, K5, M5, and O5 type „Day Start,‟ in J5, L5, N5, and P5 type „Day End,‟ in Q5 type „Day‟
and in cells R5 through U5 type „Avg. Max. Temp., Avg. Min. Temp., Avg. Rainfall, and Avg. Flow‟
31. In I6, type D6, in J6, type D75, in I7, type D76, and in J7, type D145.
32. In K6, type E6, in L6, type E75, in K7, type E76, and in L7, type E145.
33. In M6, type F6, in N6, type F75, in M7, type F76, and in N7, type F145.
34. In O6, type H6, in P6, type H75, in O7, type H76, and in P7, type H145.
35. Select cells I6, I7, J6, and J7. With the cells selected, move your cursor over the bottom right corner of
what‟s selected until you see the all-black bold plus-symbol. When you see the plus symbol, click the
mouse, and drag down until row 37 (cells I37 and J37) is also selected. Push enter. (This will repeat the
pattern you started, which will allow us to calculated averages for each day of your month).
36. Repeat step 35 with columns K+L, M+N, and O+P.
37. In cell Q6, type 1 and in cell Q7 type 2. Select these two cells, and follow the same basic procedure
described in step 15 above for column Q (this make a series of days from 1 to 31).
38. In cell R6, type “=AVERAGE(INDIRECT(I6):INDIRECT(J6)).” )).” In cell S6, type
“=AVERAGE(INDIRECT(K6):INDIRECT(L6)).” In cell T6, type
“=AVERAGE(INDIRECT(M6):INDIRECT(N6)).” In cell U6, type
“=AVERAGE(INDIRECT(O6):INDIRECT(P6)).”
39. Copy the contents of R6, select R7 through R36 and paste. (This will put the averages for your month
for each day of the month into the cell corresponding to the correct year.)
40. Follow step 39 using S6, T6, and U6. (This gives you the data you need to graph daily trends within
your month since 1938).
41. Highlight the data in cells Q5 through Q36 (Year). Hold „Control‟ and highlight the data in cells R5
through R76 (Avg. Max. Temp). With these selected, click on the Chart Wizard icon (see picture above
for details on where to find the Chart Wizard icon).
42. Choose „X-Y Scatter plot,‟ then „Scatter with data points connected by smooth lines,‟ then click Finish.
(Or, walk through the Wizard to customize axis titles, etc. as desired.)
43. Save the graph and be ready to work with it again in the „Trendlines‟ assignment.
44. Follow instructions 41-43 three more times, each time using data from row Q as the first column, and
the other times using columns S, T, and U. (This will give graphs of Average Daily trends WITHIN
your Month for all parameters)
45. Again, save your overall spreadsheet.
Overall Results
Average Daily Results for Your Month since 1938:
What is your month‟s average daily maximum temperature is since 1938? ________
What is your month‟s average daily minimum temperature is since 1938? ________
What is your month‟s average daily rainfall is since 1938? _______
What is your month‟s average daily flow is since 1938? _______
Average Monthly Results for Your Month since 1938:
Attach your four graphs (maximum temperature, minimum temperature, rainfall, and flow):
How does the monthly average for your month change over the years from 1938 to 2007?
_________________________________________________________________________________________
_________________________________________________________________________________________
Why do you think you‟re seeing this pattern? (Relate your answer to your prior knowledge or observations of
the real world)
_________________________________________________________________________________________
_________________________________________________________________________________________
_________________________________________________________________________________________
_________________________________________________________________________________________
_________________________________________________________________________________________
How do the actual data graphs compare with your predictions? (Give specific examples from each graph and
your thoughts as to why they are similar and/or different.)
_________________________________________________________________________________________
_________________________________________________________________________________________
_________________________________________________________________________________________
Average Daily Results WITHIN your month for each day (e.g. Sep. 1, 2, 3, etc.) since 1938:
Attach your four graphs (maximum temperature, minimum temperature, rainfall, and flow):
How does the daily average for your month change over the first to the last day of the month?
_________________________________________________________________________________________
_________________________________________________________________________________________
Why do you think you‟re seeing this pattern? (Relate your answer to your prior knowledge or observations of
the real world)
_________________________________________________________________________________________
_________________________________________________________________________________________
_________________________________________________________________________________________
_________________________________________________________________________________________
_________________________________________________________________________________________
How do the actual data graphs compare with your predictions? (Give specific examples from each graph and
your thoughts as to why they are similar and/or different.)
_________________________________________________________________________________________
_________________________________________________________________________________________
_________________________________________________________________________________________
Adding Trend lines – Dungeness Project
Your job (this assignment) is to add trend lines to your graphs for your month.
As we learned through our „Looking at Trends over Time‟ work, there have been changes over time in the
weather and river flow of our area. In our „Looking at Trends Over Time‟ exercises, we worked with 70 years
of local data for maximum temperature, minimum temperature, rainfall, and flow. In this exercise, we are going
to see how much these factors have actually changed over time. By the way, folks, what you are about to do
has never been done before, and there are many people (including college professors, county employees, and
professional scientists) who are interested in your results.
‘Average Monthly’ Graphs
Look at your four average monthly graphs (for maximum temperature, minimum temperature, rainfall, and
flow). Based on the data shown on your graphs, please answer the following questions:
Average Monthly Maximum Temperature:
Over the last 70 years, do you think average monthly max. temp. has increased or decreased? ________
By much do you think it has changed? _________________________________________________________
Do you think a straight or curved line best shows the way it has changed? _____________________________
Average Monthly Maximum Temperature:
Over the last 70 years, do you think average monthly min. temp. has increased or decreased? ________
By much do you think it has changed? _________________________________________________________
Do you think a straight or curved line best shows the way it has changed? _____________________________
Average Daily Rainfall for your Month:
Over the last 70 years, do you think average rainfall has increased or decreased? ________
By much do you think it has changed? _________________________________________________________
Do you think a straight or curved line best shows the way it has changed? _____________________________
Average Monthly Flow:
Over the last 70 years, do you think flow has increased or decreased? ________
By much do you think it has changed? _________________________________________________________
Do you think a straight or curved line best shows the way it has changed? _____________________________
‘Average Daily Within your Month’ Graphs
Look at your four average within month graphs (for maximum temperature, minimum temperature, and
rainfall). Based on the data shown on your graphs, please answer the following questions:
Average Daily Maximum Temperature within Month:
From the first to last day of your month, do you think avg. max. temp. has increased or decreased? ________
By much do you think it has changed? _________________________________________________________
Do you think a straight or curved line best shows the way it has changed? _____________________________
Average Daily Minimum Temperature within Month:
From the first to last day of your month, do you think avg. min. temp. has increased or decreased? ________
By much do you think it has changed? _________________________________________________________
Do you think a straight or curved line best shows the way it has changed? _____________________________
Average Daily Rainfall within Month:
From the first to last day of your month, do you think avg. daily rainfall has increased or decreased? ________
By much do you think it has changed? _________________________________________________________
Do you think a straight or curved line best shows the way it has changed? _____________________________
Average Daily Flow within Month:
From the first to last day of your month, do you think flow has increased or decreased? ________
By much do you think it has changed? _________________________________________________________
Do you think a straight or curved line best shows the way it has changed? _____________________________
Instructions for Plotting Trend Lines on your Graphs
1. Open your own monthly spreadsheet „Student Share‟/ Lieberman / Dungeness Project / Monthly
Spreadsheets / “YourName+Month”). YOU MUST HAVE ALREADY CREATED YOUR OWN
SPREADSHEET AND GRAPHS FOLLOWING THE „LOOKING AT TRENDS‟ WORKSHEET.
2. Find each of your eight graphs and follow steps 3 through 5 for each one.
3. First look for obvious outliers on your graph. Outliers are data points that are far off from other points.
In science and math, we often “write off” extreme conditions as essential unpredictable get rid of them.
Hovering the mouse over the outliers can tell you the values of each point, so you can find them in your
data and delete them. If there are obvious outliers, delete them.
4. Click on a graph. Under the Chart Menu (which should now be an option at the top of Excel) you can
„Add Trendline.‟ For each graph, if you thought a straight line best describe the change, keep Linear
checked, and tab over to Options and choose “Display equation on chart” and “Display R-squared value
on chart.” If you thought a curved line best describes the change, please see the teacher.
5. In the spaces below, write the equations and R2 values that Excel gives you. Print your 8 graphs.
Write the equation for Avg. Monthly Max. Temp:_______________________________ R2 value: ________
Write the equation for Avg. Monthly Min. Temp:_______________________________ R2 value: ________
Write the equation for Avg. Monthly Rainfall:_______________________________ R2 value: ________
Write the equation for Avg. Monthly Flow:_______________________________ R2 value: ________
Write the equation for Avg. Daily Max. Temp:_______________________________ R2 value: ________
Write the equation for Avg. Daily Min. Temp:_______________________________ R2 value: ________
Write the equation for Avg. Daily Rainfall:_______________________________ R2 value: ________
Write the equation for Avg. Daily Flow:_______________________________ R2 value: ________