Step 7: Repeat Steps 2 to 6 for Other Site

1. So now, what about the other site? You will follow the same steps that you have above and produce scatter plots for each dataset. In the end, you should have 3 scatterplots, each with "linear" regression and "moving average" averaged over 12 periods.
   1. Note the moving average will not produce an equation or an R2 value, so this cannot be used for forecasting.
   2. Anyway, think about each dataset individually in the same fashion that we just did.
   3. What do the data show? Why do they show that? Use inference/deduction skills to draw logical and thoughtful conclusions supported by the data. Identify any questions or concerns you might have? What additional ideas/hypotheses do you have?
   4. Write down all observations and interpretations in your course notebook, and export each of your graphs to a PowerPoint for later use. Save your Excel data chart often so it isn't lost!!!
   5. Make sure you answer the questions above, at the minimum. If you get stuck in your graphing, remember, that you have already downloaded example plots from the PSMSL website. You should be able to verify if you are on the right track. If your graph looks absolutely nothing like the PSMSL plot, you should go back through and see if you made any errors. It is best to go through this step-by-step the first time.
2. Now, to make and draw comparisons between your sites. In order to begin to look at differences and similarities, you need to produce a composite graph. This means you need to put all 3 datasets on the same graph. If you have been methodical and careful in your labeling, this step should be relatively easy as well. So, what do you do now?
   1. If you have each data on a different sheet in your Excel. Go ahead and copy each data set to a fourth sheet. Label that sheet "composite data".
   2. Next, make sure your data are aligned by date and that each dataset has the same date format. In the example that follows (Figure 4.56), I have 5 yearly datasets (not monthly as in the previous example) that I have aligned accordingly. Note in the example below, I have plotted data since 1937 for Cuba, New Zealand, Virginia, Canada and Uruguay. Note that Cuba is the oldest record and should be plotted first (column B). The data record for New Zealand doesn't start until 1944, so it is aligned with the Cuba dataset. The same goes for the other sites.
   3. Note here that I am using the RLR datum data, rather than the corrected to mean sea level (MSL) data. It is easier to demonstrate the graphing process here. You would want to use the MSL data, which would require that you align the axes again as you did previously.
      
      
      

      ×

      Figure 4.56: Plotting data from all of your sites on one composite graph. Here, sea level elevations from several sites are shown plotted by date (year).Credit: Microsoft Excel Screenshot
      
      
3. Once the data are aligned, you can easily create the combined scatter plot. In the end, you will likely produce a diagram that looks like the one below (i.e., Figure 4.57). Your graph will have MSL as the y-axis rather than sea level relative to RLR.
   
   
   

   ×

   Figure 4.57: An example of a combined sea level plot for 5 different localities. Although each site shows annual variability, the long‐term trend is clearly toward higher sea levels.Credit: Microsoft Excel Screenshot
   
   
   
4. You will note the plot above doesn't include regression lines for the datasets, which yours will include so you can compare long-term trends between each of the three sites you evaluated. We have already discussed how to include a regression line so make sure you do that.
5. But. wait! Once you have a single data set plotted, how do you add the other datasets to make a combined plot?
   1. It's actually pretty simple... To help demonstrate the process, we have plotted data for Cuba in Figure 4.58. Note the omitted data in the 1940s. Wonder why data is missing? So, as you did previously when you changed the title of the legend, you will use the "Select Data Source" function as shown below.
   2. You can then click "Add" in the Legend Entries (Series) box - shown below. An "Edit Series" window will open (Figure 4.59).
   3. In the box - type the name of the data series in the "Series name:" box. In this case, it will be New Zealand.
   4. Then click in the "Series values:" box, click on the spreadsheet icon to the right of the blank box. The window will minimize, and you can navigate to the y value data in your spreadsheet and click and drag until all data points are included. In this case, the white Series values: box will be populated by the appropriate variables you clicked on. Here, we selected column C beginning with row C9 through C76. Once you have done this for your second data set, hit OK.
      
      
      

      ×

      Figure 4.58: In order to insert additional datasets into an existing scatter plot, you will use the "Add" data button in the "Select Data Source" window as shown above.Credit: Microsoft Excel Screenshot
      
      
      
      
      

      ×

      Figure 4.59: The "Edit Series" dialogue box will appear after clicking "Add" in Figure 4.58. You will name the series, and include the values that are to be plotted along with the previous dataset.Credit: Microsoft Excel Screenshot
      
      
6. You can go back to your "Select Data Source" window and hit "Add" again to add your third data set. Here "Virginia, USA" and variable range from D16 to D76 will be added (Figure 4.60).
   
   
   

   ×

   Figure 4.60: Edit Series Box for the third and final dataset to be added to the composite scatter plot.Credit: Microsoft Excel Screenshot
   
   
   
   1. Once you have added all three, you can click "OK" all the way out of the tool.
   2. Your chart might look something like what is shown below in Figure 4.61 below.
      
      
      

      ×

      Figure 4.61: Combined scatterplot showing all three datasets and their linear regression lines. Each linear regression line has been color coded to the appropriate dataset. All three datasets show long‐term sea level rise trends, however each has a slightly different rate of rise. The slowest rate of rise was located in Cuba, whereas the highest rate of rise was in Virginia.Credit: Microsoft Excel Screenshot
      
      
      
   3. Once you have added all of the datasets to the scatterplot, you will want to clean up the axes, and change any chart labels to make the chart look professional again. Notice in Figure 4.61, because we used Cuba's dataset first (which ended in 1971), we had to fix the x-axis to show data through 2004. In the Excel document we provided, we have provided a composite hydrograph for Victoria and one additional site. You will use this as a model to build your own composite graph. Start with Victoria and add the other two sites you dowloaded.
   4. Also, notice the legend was placed inside the graph. Placement is up to you, but locate the legend so you can maximize visualization of your composite dataset.
   5. To aid visualization, we have also color-coded the regression lines, the equations and R2 values, to the original color of the data line. Thus, the blue regression line corresponds with the blue data points for New Zealand. This practice helps with visual acuity and improves the ability of readers to interpret the data. We also took time to change the orientation of the labels on the X axis so they are rotated. You can do this in the Format Axis, Alignment tool box.