# Part 3—Prepare and Graph Averaged Data for One Station

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

Average temperatures are used when looking for warming or cooling trends. In Part 2, you calculated the average annual temperature for just two years in a time-intensive process. The Global Historical Climatology Network (GHCN) provides monthly average temperature data. However, GHCN temperatures are given in degrees Celsius rather than Fahrenheit. Making comparisons between GHCN and GSOD datasets requires converting measurements to the same units, which is easily done using Excel formulas.

- 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:

- Insert a new blank worksheet and rename its tab "Graphs", as in Part 2, step 2.
- In the original data sheet, select and
**Copy**columns B through I for 1949 through 2011. - Switch to the Graphs sheet and
**Paste**the data into cell A1. - Select columns C,D,E,F, and G and choose
**Edit > Clear Contents**. You will use these columns later for calculated values. - Select and Drag the MNTM values into column F.
- Enter new headings for columns A through F: Year, Month, Average, Mean_Temp_F, and Mean_Temp_C. Select Row 2 and delete it.
- 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. - 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.

- 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**. - 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. - 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
- Compare this graph to the graph of the GSOD data for Barrow for 1946. What are the differences and similarities?

If you had trouble loading the data or creating the worksheet with the 1949-2011 GHCN data, or with converting the temperatures to degrees F, right-click (PC) or control-click (Mac) the BarrowGSN_part3_step1.xls (Excel 182kB Nov7 12) link and download the file.

## 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.

- 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. - Create a new sheet and label it "Annual_Temp".
- Select columns A through C on the "Graphs" sheet and copy them. Switch to the "Annual_Temp" sheet and choose
**Edit > Paste Special**. - 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. - 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).

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

- 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.
- 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

- 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.
- This is similar to the resulting graph. Note the years are slightly different.

- 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?