O'Reilly logo

Excel Scientific and Engineering Cookbook by David M Bourg

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

6.8. Deseasonalization of a Time Series

Problem

You're working with a time series with clear seasonal components, which you'd like to isolate and remove from the original data.

Solution

Deseasonalize your data in Excel using standard spreadsheet techniques.

Discussion

In the previous recipe, I showed you how to compute seasonal indices for a time series using the average-percentage method. That recipe required nothing more than standard spreadsheet techniques to compute the indices. Further, the earlier Recipe 6.6 showed you how to use standard spreadsheet techniques to isolate and remove the long-term trend in a time series. The model used there was the multiplicative model in which the original time series, Y, is assumed to be composed of a long-term trend, seasonal variation, and irregular variation, which is represented in the formula Y = TSI. Deseasonalizing a time series amounts to estimating the S contribution and removing it by dividing Y by S (that is, by computing Y/S). You can extend these ideas further to isolate the irregular variation as well by computing Y/(TS) to yield I.

Let's reconsider the average monthly temperature series shown earlier in Figure 6-23. That data series has a slight upward trend and a very clear seasonal variation over each year. Using the seasonal indices computed in the previous recipe for this time series, we can easily decompose and deseasonalize the series. Figure 6-26 shows a spreadsheet I set up to decompose the time series.

Figure 6-26. Average ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required