Part 3—Prepare and Graph Averaged Data for One Station

Step 1 – Load Global Historical Climatology Network (GHCN) data for Barrow

1. Launch Excel and open Barrow_GHCN_data.xls. The value of interest is MNTM, the mean (average) temperature for each month. The imported GHCN data looks like this in Excel:
   
   
   
   
   
2. Insert a new blank worksheet and rename its tab "Graphs", as in Part 2, step 2.
3. In the original data sheet, select and Copy columns B through I for 1949 through 2011.
4. Switch to the Graphs sheet and Paste the data into cell A1.
5. Select columns C,D,E,F, and G and choose Edit > Clear Contents. You will use these columns later for calculated values.
6. Select and Drag the MNTM values into column F.
7. Enter new headings for columns A through F: Year, Month, Average, Mean_Temp_F, and Mean_Temp_C. Select Row 2 and delete it.
8. The MNTM data from the GHCN is in tenths of degrees Celsius, so you need to divide it by 10. Enter this formula into cell E2 =F2/10 click return. You should now have a decimal in the data and it is ready to convert to Fahrenheit.
9. To copy this formula down the column, select cell E2, position the mouse over the lower right corner of the cell until the + cursor appears, then drag down the column to the last row of data. This will copy the formula down the column and display the converted data.
   
   
   
   
10. The formula used to convert temperatures from Celsius to Fahrenheit is F=(9/5)*C + 32. Enter this formula in cell D2 as =(9/5)*E2+32.
11. To copy this formula down the column, select cell D2, position the mouse over the lower right corner of the cell until the + cursor appears, then drag down the column to the last row of data. This will copy the formula down the column and display the Fahrenheit temperatures.
Show me
Hide
The worksheet should now look like this:





12. Select the mean temperatures in column D for 1949 through 1952. Use the chart wizard to create a line graph of the average monthly temperatures for Barrow, Alaska for 1949–1952
13. Compare this graph to the graph of the GSOD data for Barrow for 1946. What are the differences and similarities?
Show me
Hide
The GHCN graph shows five full annual temperature cycles, while the 1946 GSOD graph shows only one. The GHCN graph is also smoother than the 1946 GSOD daily average temperature graph. The mean temperatures for both graphs rise going from winter to summer and drop back down again in fall and winter.

Step 2 – Create Annual Average Temperature at Barrow with GHCN data.

Global Surface Network data consists of monthly average values. To get an average yearly value, calculate the average of twelve monthly values.

1. Enter the formula =AVERAGE(D2:D13) in cell C2 and copy this formula down to the bottom of column C. The values in column C now represent a running 12-month average temperature.




2. Create a new sheet and label it "Annual_Temp".
3. Select columns A through C on the "Graphs" sheet and copy them. Switch to the "Annual_Temp" sheet and choose Edit > Paste Special.
4. In the Paste Special dialog box, click the Values option. In the pasted cells, the formulas are replaced by the calculated values. It is now possible to reorganize the data without scrambling the monthly average temperature values.
5. The important values are the ones calculated for January of each year. To sort the data by month, select all three columns and choose Data > Sort. Sort first by Month (ascending), then by Year (ascending).






Show me the results of the sort process
Hide

After sorting, the top of the sheet should look like this, with the January 12-month average values grouped together:






If you have trouble downloading data or creating the "annual_temp" worksheet, then download and use the BarrowGSN_Part3_step2.xls (Excel 255kB Nov7 12) file to do the following graphing steps.

Step 3 – Graph Annual Average Temperature at Barrow with GHCN Data

1. On the Annual_Temp sheet, select the Running_Average values for January from 1949 through 2011 and use the chart wizard to create a line graph of the annual average temperatures.
2. In Step 2 of the chart wizard, click the Series button at the top of the window. Click in the Category (X) axis labels: field, then select the range of cells in the Year column of the Annual_Temp sheet from 1949 to 2011. (Or enter the cell range =Annual_Temp!$A$2:$A$60.) This step will label the x-axis of your line graph with the correct year for each value.

Step 4 – Add a Trendline to the Temperature Graph

1. Think back to the question of Part 2, "Has the average annual temperature been increasing from 1949 to 2011?". Use Excel to calculate and add a straight "trendline" to the graph.
Show me how to add the trendline
Hide
1. Right-click (PC) or control-click (Mac) the line graph and choose Add Trendline from the shortcut menu.




2. Choose the "Linear" Trend/Regression type.



3. Click the Options button and choose the "Display equation on chart" option.



4. Click the OK button to add the trendline to your graph. If the trendline equation is hard to read, drag it to a more readable location.
5. Modify the axis labels and titles to produce a pleasing, well-labeled graph.




2. This is similar to the resulting graph. Note the years are slightly different.
   
   
   
   
3. The equation of a line is y = mx + b where m is the slope and b is the y-intercept. In this case, the slope represents the average rise in the temperature each year, in units of degrees F per year. To determine the total temperature rise over this time period, multiply the slope (0.0608 degrees per year) times the number of years (59 years). What is the temperature trend over this 59 year period?
Show me the answer
Hide
59 years * 0.0608 degrees/year = an increase of about 3.6 degrees F. This agrees well with the overall change shown on the graph.