>Here’s An Odd Effect Created By EXCEL

>I use gif animations of graphs to show changes in data and trends. I had hoped to use one or more in my post “OHC Trends Presented by Levitus et al (2009) Versus Trends Presented by the OHC Data,” but if I had used them, there was an unusual effect that I would have needed to explain and that would have detracted from the content of the post.

In that post, I prepared OHC trend comparison graphs, using three different time periods. Levitus et al (2009) used 1969 to 2003, though the common data extended back to 1955, so I also created a comparison graph to show the trends over that longer period. The third trend comparison graph incorporated all of the data that was available through 2008. As opposed to having OHC data appear and disappear, I used dashed lines to show the data that was excluded in the trends. Figure 1 is a gif animation of three trend graphs of the data I created for that post. The trend lines shift as anticipated, but notice the slight shifts in the OHC data itself. There are no differences in the data between 1969 and 2003 in all three graphs, yet they shift. The data does not shift significantly, but they do change enough that the effect would need to be explained.
http://i27.tinypic.com/2qb6txz.gif
Figure 1

To create the trend comparison graph of all of the data over their terms, I obviously used the first five columns of the spreadsheet. Refer to Table 1. It shows the last 9 years of data. (The source of the data is discussed in OHC Trends Presented by Levitus et al (2009) Versus Trends Presented by the OHC Data.)
http://i25.tinypic.com/2d7u8u0.png
Table 1

Then to create the shorter term trend comparisons, I shifted the data that was not to be used in the trend analyses to columns “F” through “I”, and used dashed lines to differentiate them in the graphs. Refer to Table 2. Note: to prevent a sizable gap between the datasets I duplicated the 2003 data in the “unused” data columns.
http://i27.tinypic.com/b9azvl.png
Table 2

Without the trend lines, Figure 2, the data shifts in the comparison graphs. It would, therefore, appear the shifts depend on the start and stop dates. They happen even during the period of 1969 to 2003, a period when the data in the spreadsheet does not change in any way. Curious.
http://i29.tinypic.com/345lcma.gif
Figure 2

To determine if the effect was a function of the dashed lines, I deleted them for yet another gif animation. Refer to Figure 3. Based on this animation, it is the amount of data included in the graph that causes the anomalous shifts.
http://i26.tinypic.com/1zp3992.gif
Figure 3

And for those wondering if the trends lines shift the data, I’ve created Figure 4, which shows one comparison with and without the trend lines. The addition of the trends does not shift the basic data.
http://i29.tinypic.com/11spbf8.gif
Figure 4

About Bob Tisdale

Research interest: the long-term aftereffects of El Niño and La Nina events on global sea surface temperature and ocean heat content. Author of the ebook Who Turned on the Heat? and regular contributor at WattsUpWithThat.
This entry was posted in Uncategorized. Bookmark the permalink.

4 Responses to >Here’s An Odd Effect Created By EXCEL

  1. Anonymous says:

    >you just need to select 0.5 for " Contour interval for var1: " and 0 for " white: " to get a reasonable map.

  2. Bob Tisdale says:

    >Anonymous: You wrote, "you just need to select 0.5 for ' Contour interval for var1: ' and 0 for ' white: ' to get a reasonable map.I believe this comment belonged on the Monthly update thread.Additionally, those sound like the adjustments for making a map from the portion of NOMADS that used to allow access to ERSST.v2 data. There aren't any such adjustments for the OI.v2 data here:http://nomad3.ncep.noaa.gov/cgi-bin/pdisp_sst.sh?ctlfile=monoiv2.ctl&varlist=on&new_window=on&lite=&ptype=map&dir=

  3. Bob Tisdale says:

    >Anonymous: THANK YOU!!!!!!!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s