Part 2: Prepare Data

Begin by creating a 'master' file of the precipitation data. The master file will contain all the precipitation data for California and Minnesota:

1. Open the California precipitation file into an Excel spreadsheet using the Open command under the File menu from within the Excel program. All data files are comma delimited text files:











2. Next, select the header information rows and delete them from the file:





3. Label the data using the name of the state. You may find it useful to insert a few rows into the worksheet. Since this master file will have data from both states, we need to identify which data come from which state.

4. Repeat Steps 1-3 for Minnesota. After you've imported and labeled the state file, copy and paste the contents of the Minnesota data file to the bottom of the California file:

5. When all the data have been added to the master file, save the master file. The file name should include the type of data; ie. 'precipitation.xls' to distinguish it from other data files.

6. Repeat Steps 1-5 to create master files for minimum temperature, maximum temperature, and solar radiation.

The data we currently have are in half degree by half degree grid cells for each state (scientists refer to this as the 'spatial resolution of the data'). In order to make state comparisons, we will need to calculate a state average for each state from the gridcell data:

1. If you scan through the data file, you will notice some missing values. These are coded as -9999. These values must be deleted from the worksheet before we can average the gridcell data.
   
   

   The easiest way to do this is to use the Replace command under the Edit menu, replacing all the -9999 numbers with blank cells:

   
   
   
   
2. Place your cursor in the blank cell in the first column of your worksheet below the California data and click on this cell. You may want to insert a couple of rows in this area of the spreadsheet (Select the Rows command under the Insert menu.) Since you will be creating state averages from this gridcell data, label this row with the name of the state and the type of data; ie. California Maximum Temperature:





3. Now place your cursor in the cell below the column of gridcell data that you want to average. This column of data corresponds to the year 2000 and should be the third column in the worksheet. (The first two columns are longitude and latitude). Click on this cell. To calculate a state average for this column of data, we will use the Excel Average formula. Type into this blank cell the text:
   
   =Average(
   
   
   
   The equals sign before the word 'Average' tells Excel that we want to calculate a new value from data in the spreadsheet. Since we want to calculate a state average for each column of data (the years) select the column of data that corresponds to the first column of temperature data. Be careful and only select the data and not the row containing the Year labels:
   
   
   
   Once we've selected the column of data, we need to close the formula by using a close parenthesis )
   
   Your formula should look something like this (Your data range may vary slightly depending on the number of blank rows you've added):
   
   =AVERAGE(C5:C176)
   
   
4. Now move the cursor and click on any cell near the cell that contains the average formula. Excel will compute the average for this column of data.



5. The next step is to copy the formula that we just created to all the cells to the right of the formula that correspond to the years 2001 through 2100. Put your cursor in the cell that contains the formula and then select the Copy command under the Edit menu. Scroll and select the adjacent cells in this row that correspond to the 2001 to 2100 and then choose the Paste command under the Edit menu:





6. Almost done! Convert these formulas to values by selecting the entire row of data and then choosing the Copy command under the Edit menu and then the Paste Special command under the Edit menu. Paste the data as Values.





7. Repeat Steps 1-6 for Minnesota.



8. Now, delete all the rows in the worksheet except the rows of state averages that you have just created and the row containing the year labels. (This will likely be the first row of your spreadsheet).




Your spreadsheet should now look like this:





9. Use the Save As command under the File menu to save this file using a new name. Because we will add similar data for precipitation and solar radiation to this file, you may want to name the file something like 'climate data'.



10. Repeat Step 1 through Step 9 to create state average data for minimum temperature, precipitation, and solar radiation. Once you've computed the state averages, add these data to the master file you created in the previous step.




Your spreadsheet should now look like this:

1. Open the spreadsheet that contains the state averages you calculated in the previous step. Select the Year row and the California and Minnesota data for any of the four variables.
   
   Select the Chart Wizard icon to graph these data:





2. Select the Scatterplot chart type and the chart sub-type data points connected by smoothed lines without markers. Then click the Next button:





3. Select the Legend tab and check the box to display the legend.





4. Select the Title tab and choose a title for the chart. Label the Value(X) axis as 'Year' and the Value (Y) axis as 'Precipitation (mm/yr)'.





5. You can modify the appearance of your graph by double clicking on the element you want to change. For instance, double clicking on one of the Y-axis labels allows you to change the default settings for that axis. The scale settings for precipitation should be set to:




Your final graph should look something like this:


6. Repeat Step 1 through Step 5 and create similar plots for minimum temperature, maximum temperature, and solar radiation.




The Y-axis scale settings should be:


Minimum Temperature:






Maximum Temperature:






Solar Radiation:





7. Your final graphs should look something like this: