Step 6: Interpret Data and Add Trendline

1. At this point, it is a good idea to look at the data and see what is going on with tides. You will begin to see patterns or trends in the data. In fact you may see trends and patterns that we discussed throughout the module.
   1. In the dataset example used here, remember, we are looking at monthly averages, so the data we downloaded is the 30-day average for each month therefore all tides in a given month are averaged to a single value.
   2. Looking at our dataset (Figure 4.51 above), it looks like there are annual highs and lows where tides are high for about 8 months (May-December) every year, and are relatively low for 4 months (January-April). This suggests Panama experiences a persistent seasonal sea level change. This is pretty cool!
   3. You could even calculate the average seasonal range of sea level from your dataset because you now have the data. In this case, the seasonal fluctuation is about 30 cm (300 mm).
   4. Can you think of any reasons why this location in Panama would experience this pattern? What currents/climate factors might change like this to influence water levels annually? If you looked at a longer time increment, would there be any decadal patterns?
   5. Obviously we only plotted 3 years of data, but you should have plotted at least 30 years or more from your datasets so you could look at this issue in your data.
2. Alright, so now we are going to add a trend line to see if there isn't a longer-term signal embedded in the monthly data. So, it is pretty easy. Simply click on the data series line in your graph, and each data point should then be highlighted. Right click and select "Add Trendline..."
   
   
   

   ×

   Figure 4.54: Screenshot showing the "Format Trendline" tool window as discussed.Credit: Microsoft Excel Screenshot
   
   
   
   
   This will open the "Format Trendline" tool window (Figure 4.54 above). Excel will ask you what statistical method you want to employ to calculate a trend. You have several options to choose from.
   1. We recommend you start with the "Linear" option, but you could also repeat the process and use the "Moving Average" option later.
   2. In this case, while the tool window is open, change the averaging period. It automatically starts with "2", which means that it will average 2 month increments to establish a data point, but you can increase the increment. For instance, if you want to do a yearly average, you would select "12" as your period.
3. Before you exit the window, ensure that you:
   1. label the "Trendline Name". Excel will do an "Auto" name, but if you want to change it, now is the chance.
   2. display the equation for the trend line. This option is at the bottom of the window. It will show up in your graph if you select "Display Equation on chart".
   3. display the "R-squared value on chart". You may recall from a high-school statistics/algebra class that an R2 value is actually the "goodness-of-fit" for your data. When your data are tightly distributed around the trend, your R2 value will be close to 1.0. When the data points have a greater spread, the R2 value will be closer to 0. In this case, if the R2 value is 0, the regression equation will not be able to accurately determine the Y value.
   4. Finally take a look at the Forecast option. You can use this function to extend any trend into the future ("Forward" = forecasting) or into the past ("Backward" - which is typically called hindcasting). You won't do this right now, but you will eventually so make note of it.
   5. When you do use this function, remember the periods will be 365 days for one year. So, if you want to forecast one year into the future, you would put an increment of 365 in the "Forward: periods" box.
   6. When you do this, you will need to change the scale on the x-axis accordingly so the forecast trendline will show up on your graph.
      
      
      

      ×

      Figure 4.55: Screenshot showing the trendline (linear regression) for the data in the 3‐year record from Panama (1907 to 1910).The equation for the line is given as is the R2 value. Here the volatility in seasonal water level produces a very low R2 valuethat suggests the line can't be used to predict the accurate water elevation at any given time period. But, over the long‐term,the behavior of the data shows an upward increase in water level for the duration.Credit: Microsoft Excel Screenshot
      
      
      
4. Your screen might look something like what is shown in Figure 4.55 above. Here, you can see the monthly data and a calculated average trend (linear regression) of the data. The equation for the line is shown in the format y=mx+b .
   • You will remember from high school math, that you can solve for any point on that line when you know one or the other of the variable.
   • For instance, you can solve for "y" mean sea level height in mm if you know the x value. This is the equation that you could use to calculate a possible future sea level position.
   • Remember also that your equation was based on the premise that the x-axis origin was January 1, 1900, so future dates would have to be multiplied by 365. So, if you wanted to use this equation to predict the elevation of mean sea level in January 1913, your x value would have to be 365 x 13.
5. Now, using the trendline projected into the future, you could also visually approximate the mean sea level in 1913 as well. That is the difference between quantitative reasoning (true calculation) and qualitative reasoning (approximating the value).
6. In terms of the R2 value, in this example our value is 0.1113. This value is pretty low, because of the seasonal spread of monthly tide values.
   • Clearly sea level change at this location in Panama for these three years was dominated by the seasonal pattern.
   • However, there is a longer temporal trend that explains additional sea level change not explained by the seasonal process.
   • As a scientist, it is your job to think about the "background" signal that might be causing the trend in increasing sea level here. Is it a local process? i.e., subsidence? Is it part of a decadal rhythm? i.e., El Niño? Is it a global process? i.e., eustatic sea level rise?
   • This is what we are trying to figure out. You need to think about the geographic/geologic setting and perhaps patterns that exist at each of your three sites. More on this later.