Chapter 16: Using Formulas for Statistical Analysis
16
The OFFSET function is used to return a particular range that’s offset from the starting
point. The arguments to OFFSET are as follows:Reference The cell where the OFFSET function starts.
Rows The number of rows away from the starting cell where the returned range starts.
Negative numbers count up the spreadsheet, while positive numbers count down.
Cols The number of columns from the starting cell. Negative numbers count to the left,
and positive numbers count to the right.
Height How many rows the returned range should have.
Width How many columns the returned range should have.The height and width arguments of OFFSET must be positive numbers.
If we make cell D12 the reference argument, that’s where OFFSET starts counting. The -9
in the rows argument directs OFFSET to count up nine rows to D3. The 0 (zero) in the
cols argument means that OFFSET stays in the same column. After the first two argu-
ments, OFFSET has computed that the start of the returned range will be D3.The height argument is set to 10 , meaning that our range will be ten rows in height,
or D3:D12. The width argument of 1 keeps the range at one column wide. The result of
OFFSET, and what is passed into AVERAGE, is the range D3:D12. As the formula is copied
down, the prior ten scores are averaged.The number of values to include in a moving average varies depending on the data. You may want to show the previ-
ous 12 months, 5 years, or another number that makes sense for your data.
In Figure 16.5, the moving average is added to the chart, and the line for the raw scores is
made lighter so that the average line stands out. Showing the average of the last 10 scores
provides a clearer picture of where this golfer’s game is headed.Applying exponential smoothing to volatile data
A moving average is a great way to smooth out data. One problem with moving averages is
that they give equal weight to each data point in the set. In a six-week moving average,
for example, each week’s value is given 1/6 weight. With some data sets, more current data
points deserve more weight.Figure 16.6 shows the demand for a product over a 26-week period. The Demand column
shows the actual product sold. The Moving Average column attempts to predict the demand
based on a simple six-week moving average. The final column uses exponential smoothing
to give more weight to recent weeks than past weeks.=(C8*$H$2)+(E8*(1-$H$2))