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.5. Centering Data

Problem

You need to center a data series before further analysis.

Solution

Use basic spreadsheet techniques and Excel's built-in formulas to center the data.

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.

Figure 6-17 shows an example spreadsheet I set up.

Centering example

Figure 6-17. Centering example

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 ...

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