Excel has some little-known but powerful functions for operating on arrays. Although they are clunky, they work; and knowing how to use them reduces the chance of making errors (and likely improves computational efficiency). In particular, using these functions helps one to avoid copying data. (Having copies of data in various places creates opportunities for corruption and inconsistency, as well as resulting in a confusing document that is more difficult to check.)
A lagged covariance requires functions to shift (that is, "lag") a vector and to compute covariances. The shift function is called OFFSET
and the covariance function is COVAR
.
I recommend naming arrays rather than using direct references to them. In this example, let's suppose the range of four cells in which the "Value" data are contained has been named Values
. Then an expression to lag this (vertical) array by a number of rows $j$ is
OFFSET(Values,j,0,4-j,1)
The first occurrence of $j$ shifts Values
down by $j$ rows. The second occurrence, in the form $4-j$, cuts off the shifted array at the end so it can match the first $4-j$ entries in Values
. Those, too, should be cut using OFFSET
. Thence the full formula is
=COVAR(OFFSET(Values,0,0,4-j,1), OFFSET(Values,j,0,4-j,1))
For the lag-one covariance, just replace "$j$" by "$1$" all three places it occurs.
An easy flexible way to study the serial covariances more generally is to create a column of values of $j$, to which this formula may refer. Type it in once and copy it down its column. That leads to the following solution in which values $j=0,1,2,3$ (the maximum possible lag is one less than the length of the array) are placed into column D
and the corresponding lagged covariances are computed with this formula in neighboring column E
:
The lag-1 covariance is $0.363,$ shown in cell E3
. Here is the full spreadsheet:
Please note that in time series analysis one usually computes an autocorrelation function (ACF) using a slightly different formula: refer to Understanding this acf output for details.