https://www.ncdc.noaa.gov/temp-and-precip/national-temperature-index

These pages may be examined on line. In the case of the USCRN page, parameters can be loaded for that application to input and construct a plot. Both pages include a link to data files that can be downloaded. Both are text files and both have data fields delimited by either a comma or a space. It is important to notice that because of the design of both files, an important relationship exists, one that will make possible the construction of an Excel scatter plot ( chart ). Specifically, the data with respect to temperature and CO2 concentrations is in a one to one relationship with respect to each line of both files. Both files have one data value for each month that is archived. Note however that the USCRN data file has not been in existence as long as the NOAA CO2 data file. Care must be taken to extract the same amount of data points, with the same starting date, from each file so as to maintain this one to one relationship. At the time of writing this article the CO2 data file had not been extended to the end of 2018, thus I selected my data end point to be December, 2017. Additional data will be available for use in the coming months and years. The results of this exercise will be an Excel scatter plot ( chart ) that can be used to further explore possible relationships between the two entities.

This chart may be thought of as an X, Y graph, with the X values being CO2 concentrations, in parts per million, and the Y values being Temperature anomaly, in degrees Fahrenheit. My hope is that once readers understand the methods employed, there will be interest in forming and continuing a group that can track this relationship for the coming years. Yes, I understand that :

The USCRN represents data for the 48 contiguous states, Alaska and Hawaii only, it is not global.

The USCRN represents hardware measurement technology AND site selection which, in my opinion, far exceeds ANY of the historical global data sets.

The “ trend analysis “ tool in Excel has it’s limitations, with respect to regression analysis.

Using Temp anomalies versus absolute values is a decision worthy of a separate discussion.

Remember, NOAA can upon occasion, make their websites look different, rearrange the URL’s or add new pages. In the future you may have to hunt to find what is referenced here.

Also, you can:

Check the status of USCRN data sets here :

https://www1.ncdc.noaa.gov/pub/data/uscrn/products/DATASET-STATUS.txt

I am unable to locate a similar file for CO2 data.

As long as there are independent observers that will keep the USCRN data set “ honest “ and report findings as they currently are, for usage, the exercise I outline below should allow for the casual user to both examine the strength of a relationship and further understand the tool and mathematics that are introduced. Please be aware, for this work the order of steps is important, particularly so IF you are not familiar with MS Excel. This article will not make you an Excel expert, rather explain a very specific set of steps and Excel selections to get to an end point of interest. Remember, the goal is an X,Y plot ( chart ) where the X data are “ imported “ into Excel first, the Y data next, and the mean data added last. Upon completion of these steps, Excel has a application that will be introduced to construct a trend line, by user choice of mathematical selections. One Excel function will be introduced and it’s use explained so that the mean of the temperature data can be added to the chart. The work here is not intended to make you an Excel expert and the output of your effort is neither a complete or the “ correct answer “ for this topic, rather it is a start, for the layman. Let’s begin at the beginning.

IF you’re a new user of Excel and you have not examined the specific data files from NOAA in the past, take some time now to familiarize yourself with the mechanics of opening and closing Excel. Also take some time to examine the data files you will be working with, study the layouts of each data line in the files. I have tried to insure that instructions involving a mouse click are both consistent and correct, please incorporate your MS knowledge IF I have errored here. When you are ready :

I – In Excel :

Open MS excel ( 2013 or the latest version you have ). Select or create a blank workbook.

FIRST, How to take ( copy ) and use ( paste ) CO2 data by using MS keyboard shortcuts :

Refer back to the ESRL / GMD FTP data finder page shown previously. At the bottom of the page there are two records shown as available, each on it’s own line. Chose the in situ file and click on the icon ( shown in the data column ) to open the text data file in a new window.

Open the CO2 text datafile.

Notice that the data starts at a much earlier year than the Temp data. I show the first few lines of the file and a further few lines at the start of actual data that will be used.

Scroll down to the start of data for the year 2005. This year is chosen because the Temp data in the second file begins January 1, 2005

# header_lines : 148

#

# ————————————————————->>>>

# DATA SET NAME

#

# dataset_name: co2_mlo_surface-insitu_1_ccgg_MonthlyData

#

# ————————————————————->>>>

# DESCRIPTION

#

# dataset_description: Atmospheric Carbon Dioxide Dry Air Mole Fractions from quasi-continuous measurements at Mauna Loa, Hawaii.

#

#

# VARIABLE ORDER

#

site_code year month day hour minute second value value_std_dev nvalue latitude longitude altitude elevation intake_height qcflag

.

.

MLO 2005 1 1 0 0 0 378.46 0.41 31 19.536 -155.576 3437.0 3397.0 40.0 …

MLO 2005 2 1 0 0 0 379.75 0.71 24 19.536 -155.576 3437.0 3397.0 40.0 …

MLO 2005 3 1 0 0 0 380.82 1.12 26 19.536 -155.576 3437.0 3397.0 40.0 …

MLO 2005 4 1 0 0 0 382.29 0.49 26 19.536 -155.576 3437.0 3397.0 40.0 …

Highlight ( select ) all the rows of data ( 156 or more, a function of how the file has been updated ) and simply ” copy ” using ” Ctrl + C ” keys. Remember you must maintain a one to one relationship between the two data sets you will be importing into Excel.

The entire column of CO2 data can be moved, for use in Excel, by pasting, using “ Ctrl + V “ keys, into Excel column A. Select ” Ctrl + V ” to past the data into the A column.  CO2 data will be the independent variable and thus it must be moved into Excel column A.

Below is an illustration of the blank workbook you will be using. The Excel tabs along the top allow access to the sub menus that can be selected to expose further specific tools. Notice that the header for column A is highlighted. This column, by default, is ready to accept data.

When you do the “ paste “ ( CO2 data ) you will see the following warning message in Excel. Continue by clicking on the OK button.

Now you have moved all the data fields for each row, into Excel Column A. The eighth data field from the left, is the only field we need to keep in Excel column A.

Next, use the Text to Columns tool in Excel to “ throw away “ all data fields on each row, except the CO2 ppm data.

Select the Excel DATA tab and then move your mouse cursor over the Text to Columns icon, and this informational window should appear. Using this tool ( Wizard ) by right clicking on the icon you will be at step 1 of 3.

At this step you have a choice to make, you can either leave the radio button selected for fixed width OR you can change to Delimited. Either will enable the desired outcome, however the selection you make will drive how the window for Step 2 of 3 displays. The choice of selection will also impact the subsequent actions you will take to finish the import.

IF you left the default, Fixed width, selected, you should see the following window for Step 2. The particular differences for either choice have to do with the internal Data preview window and instructions or other choices you can make.

Notice below, that in the Data preview window there are two vertical lines with arrow heads to the left of the window, which are delineating the first data column from the second and the second from all remaining. You can right click and hold on the black arrow heads at the top and move these lines as you wish. In our case, you can position them so that we will import the eighth data column ONLY and let the tool discard all other data. It is most important that when you position to select the CO2 data that you snug the vertical lines immediately to the left and right of the first and last Char of the CO2 data ( do not include any SPACE Chars ).

IF however you chose Delimited, you should now be looking at the window shown below for Step 2. Now, each field is separated by a thin vertical black line. When Delimited is chosen, the tool lets you move from left to right and highlight each successive field in black and then decide to import it or to throw it away.

Notice that while in step 2 of this option, you also must set up the Delimiters check boxes, for Space only, check the Treat consecutive delimiters as one and chose (none) for the Text qualifier input box.

To clarify the remaining work, the Step 3 window for Delimited will first be shown, so that you can finish the import work IF you had made this the initial decision in Step 1

This window is explaining several items that will help to guide you. With the General radio button selected, the data field is imported, subject to a following Excel rule: as a numeric value for “ numbers “, a date value for “ dates “ and a text string for all other data types. You can now highlight each data column in the Data preview window ( right click and make the column reverse video ). Then use the Do not import column radio button to skip that column. Notice that when you select this radio button for a column, the header for that column will change from Gener to Skip. To finish at step two and import, you must move to the right, column by column and mark as Skip, all columns of data except column eight ( CO2 ppm values ).

For the sake of finishing the import here, the Fixed width option, from step 1 will be continued to completion, so you can see the complete import into Column A in Excel having been taken thru the steps for each option.

In this Step 2 you will set two break lines, used so as to teach the tool which fields you wish to throw away and which field you want to import. Use the MOVE option to achieve the break line positions as shown below. You will in step three, throw away everything to the left of column eight, in the preview window, keep column 8 for import and throw away all columns to the right of the second break line. Make sure you set the break lines tight against the digits of the data field. You do not want to import any unnecessary spaces into Excel column A. Next continue to step 3, by right clicking on the Next button.

In this last step, you will do the work to finish the import.

As it says, this screen lets you select each column that you want to import and set the data format for that imported column ( data field ).

Notice that column 1 is highlighted, in black. This shows in the Data preview window. Notice that the “ General “ radio button has been changed to “ Do not import “. Thus, the column highlighted in black has had it’s header description changed to “ Skip Column “. You can highlight the second column by right clicking on it and importing it as General, which the tool will do and convert to a numeric value. And for the 3^rd column, again select “ Do not import “.

At this point, your window should look as follows :

The import will take place when you click on the finish button. The results of the import should look as follows, with respect to the Excel workbook home screen.

II – How to take ( copy ) and use ( paste ) Temperature data by using MS keyboard shortcuts:

Refer back to the USCRN time series page shown previously. Use the link to arrive at the default page setup. You will not be using the default view. Here are the text entries used to create the maximum size data file that will source the data imported into Excel :

under Time Series, select  USCRN

under Parameter, select  Average Temperature Anomaly

under Time Scale, select Previous 12 Months

under Start Year, select  2005

under End Year, select most current year  2017

under Month, select  December

Click on the ” Plot ” button, the chart will appear.  Immediately below the chart Y axis vertical you will see a ” Download ” text string with 3 small symbols to it’s right.  Right click on the middle symbol, the Xcel icon.

A text file will open in a new window.

Here is an example of the first few lines you should be seeing :

Contiguous U.S. Average Temperature Anomaly (degrees F)

Date,USCRN

200501,1.75

200502,2.50

200503,-0.88

200504,0.41

Remember : This data set starts at the year 2005, month 01 and thus will limit the CO2 dataset usage, to begin use at the same year and month !

Highlight ( select ) all the rows of data ( 156 now, more as time proceeds ) and simply ” copy ” using ” Ctrl + C ” keys.

The entire column of Temp data can then be moved from the data text file. Highlight the B Column tab in Excel. Select ” Ctrl + V ” to past the data into the B column.  Again, you will see a text box warning that the data to paste is not the same size as the B column you are pasting.  Click ” OK “.

The import of Temp data follows the same flow and steps as you used to import the CO2 data. Simply review previous steps and repeat now for column B in your Excel workbook.

When all import work is finished, your Excel workbook should look as follows, you can scroll down to see that you have 156 ( or more, in the future ) lines of data pairs, as a matched one to one data set.

III – EXCEL TREND ANALYSIS

To do any sort of regression analysis in Excel we must first create a chart.  Making the chart ( 2 dimensional graph, X,Y ) is as follows. Excel calls this X,Y graph a scatter chart.

At the top of the Excel tool bar, select the INSERT tab.  Look to the right along the top and see the charts grouping section.  You can highlight ( move the mouse cursor over ) the symbol for a scatter chart ( it’s the small symbols with a tiny pull down arrow that is just above and slightly to the right of the word “ Charts “ ) and from the pull down menu that appears, select the scatter chart icon, without lines.

You will create a chart that looks like this :

Notice that the CHART TOOLS tab has appeared, with the option to either DESIGN or FORMAT this new chart. Also notice that there are about eight variations of the chart design that you could click on to change the “ look “ and that the default is the first style, left most. Although this illustration shows the horizontal axis having grid line labels going from zero to 180 the chart you have created will show labels representing the CO2 values that were imported.

This chart can be resized by dragging a corner to make it larger for the following work. The work will consist of adding a trend line ( regression ) for Temp versus CO2, adding a Mean column of data for all 156 existing data points ( using Column D ) and then adding mean trend plot points for all existing Temp data points on the chart.

Last, you can do some formatting work to add beginning and ending data point flags and format the horizontal and vertical axis labels and chart title field.

The chart you have created should look like this. Notice that CO2 values are shown on the horizontal axis and Temp data ( degrees F ) on the vertical axis. From this point on you will work with either the + button ( upper right corner of the chart ) or by enabling a new window menu by right clicking with your mouse cursor anywhere on the chart itself.

IV – Adding a regression line & MEAN data points

A right click on the + brings up the CHART ELEMENTS window, followed by another right click on the small grey arrow head to the right of the Trendline check box brings up the secondary window. Chose the More Options option and right click.

A Format Trendline pane opens up to the right of the Excel workbook area, this is the gateway window to allow for many choices you can explore with respect to displaying regression types. The default is Linear, and IF you move to the bottom of this pane and check the Display options for the equation and the R squared values, they will show up on your chart. Also note that you have a dashed linear regression line, same color as the CO2 data points.

Along with the dotted line, the linear equation is displayed and the R squared value. If you chose, you can move your mouse cursor onto the equation text and drag both text items to a location on the chart that promotes easy reading. You can also format the text, make it larger or BOLD simply by highlighting either line of text and using the Excel Font area OR left clicking on the highlighted text to open a options window for the texts.

Place the mouse cursor on the cart in the area shown below and right click.

This new window will allow you to Select Data, which is the starting point to adding a second plot line on your chart – the plot line for the MEAN value of the temperature data. The plot line is constructed by using an Excel math function and a unused data column. Let’s use the D column. The high level steps are to put a MEAN value into each row of the D column, thus allowing for a one to one relationship for MEAN with all the CO2 data points you have incorporated in the chart.

At this point the chart doesn’t directly display the Temp data’s singular MEAN, however. To add that MEAN to a scatter plot, create a separate data series that plots the MEAN against your data’s X-axis ( CO2 ) values.

Step 1

Click a cell ( row 1 ) in a new column ( use column D ), to act as a temporary data holder.

Step 2

Type the following formula into the Excel formula text bar which is directly above the column identifier tabs :

=AVERAGE($B:$B)

Type the formula exactly as shown, use upper case and no spaces between chars in the string enclosed in the parenthesis OR anywhere else. The dollar sign locks values when you transfer the formula output to other cells. The B:B is, I believe, is Excel for “ take the formula input from column B ( Temp data ) and use all rows in column B.

Notice as your typing the formula, the formula is also appearing in the first cell in column D. Also notice that when you have finished typing the $B:$B string turns blue in color.

Step 3

Now, move your cursor down to the header label “ tab “ for column D and left click. Notice that a numeric value appears in the row one cell and all the cells in column D are highlighted. This numeric value is in fact the MEAN ( average ) for all rows of Temp data. At this point however it is only in the first row.

Step 4

To create a MEAN line on your existing chart, you will have to replicate this numeric value into the same number of rows in column D as there are rows of data values in Column B ( the Temp data ).

Step 5

Move the cursor into the row 1, column D cell. The cursor will be displayed as a “ fat cross “. Left click once and all the rows in Column D will no longer be highlighted. Also notice that in the lower right corner of the cell outline there is a very small black square. Move your cursor over this black square and see that the cursor itself changes from the “ fat white cross “ to a black “ plus “ sign.

Step 6

Left click and hold then drag this “ plus sign “ down, inside column D as far as the number of rows of Temp data that you have imported previously. When you release the left click, you should see the value of cell row 1, replicate itself into all the rows you have just highlighted.

This is the first significant way point into creating a MEAN plot line on the chart. You now have as many points of mean data as you have Temp data. A one to one relationship has been created. To finish, use Excel to create a MEAN series of points on the existing chart.

Step 7

Put mouse cursor back on the scatter plot.  Right click on the chart. Click “Select Data” from the pop up menu that opens. This opens the “Select Data Source” dialog box.