I’ve moved to WordPress: http://bobtisdale.wordpress.com/

Tuesday, July 7, 2009

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.
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.)
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.
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.
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.
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.
Figure 4


Anonymous said...

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

Bob Tisdale said...

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:

Anonymous said...

of course wrong thread....but it is for oiv2, below "Draw" and before "plot size" :

Bob Tisdale said...

Anonymous: THANK YOU!!!!!!!


Tips are now being accepted.

Comment Policy, SST Posts, and Notes

Comments that are political in nature or that have nothing to do with the post will be deleted.
The Smith and Reynolds SST Posts DOES NOT LIST ALL SST POSTS. I stopped using ERSST.v2 data for SST when NOAA deleted it from NOMADS early in 2009.

Please use the search feature in the upper left-hand corner of the page for posts on specific subjects.
NOTE: I’ve discovered that some of the links to older posts provide blank pages. While it’s possible to access that post by scrolling through the history, that’s time consuming. There’s a quick fix for the problem, so if you run into an absent post, please advise me. Thanks.
If you use the graphs, please cite or link to the address of the blog post or this website.