Problem
You need to center a data series before further analysis.
Discussion
Centering in this context refers to the process of removing a bias or offset in a series of data so that the resulting series values fluctuate approximately about 0. Centering is often required before processing data using forecasting techniques, and can be accomplished quite easily in Excel.
There are a number of ways to center a data series, and here we’ll use the standard approach of subtracting the average of the data in the series from each data item in the series.
Fig. 6-17
The first column contains the month label, while the second column contains the original data series before centering. This series represents average monthly temperatures.
To center the data, I first computed the average of all the temperature values in the original data series. Cell D27 contains the formula =AVERAGE(D3:D26) that computes the average of all temperatures.
The column adjacent to the original data series contains the centered data (column E). The formulas in this column are of the form =D3-$D$27. All this does is subtract the average temperature contained in cell D27 from every temperature value in the original series. The result is a centered data series with the offset removed.
Fig. 6-18 shows the original data series and Fig. 6- 19 shows the centered data series.
Fig. 6-18
Fig. 6-19
The original data series oscillates about the average temperature up in the 60s, while the centered data series oscillates about the 0 axis.
This isn’t the only way to center data. Some techniques involve computing a weighted average of the data and using the result to center the series.
© 2la.it - Riproduzione riservata.
