Here’s An Odd Effect Created By EXCEL

I’ve moved to WordPress.  This post can now be found at 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