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.6. Detrending a Time Series

Problem

You're working with a data series that exhibits a clear trend and before processing the data further you need to remove the trend from the data. This is called detrending .

Solution

Construct a trendline in Excel using one of the techniques discussed in Chapter 8 (see Recipe 6.2 for an introduction to trendlines in Excel). Use the resulting trendline to detrend the original data as discussed in this recipe.

Discussion

Time series data is often thought of as being comprised of several components: a long-term trend, seasonal variation, and irregular variations. (Some models assume a fourth, cyclic, component.) When analyzing time series data (e.g., when making forecasts based on historical data), it's often desirable to decompose the time series into its various components. To this end, additive or multiplicative models are often used.

Let Y represent the ordinates of a time series such that Y = f(t), where f is some function of time. Then, we can decompose Y as follows:

or

In these equations, T represents the long-term trend component, S the seasonal component, and I the irregular variation component of the total time series, Y. The first equation is a multiplicative model, while the second is an additive model. Which model you decide to use largely ...

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