easy correlation computations with lags

M

misterhanky

I'm trying to get a sense for how certain macroeconomic variables drive
each other-- nothing too rigorous, and I know there are all sorts of
problems with multicollinearity, spurious correlation, etc.-- and I
have a number of datapoints for which I am trying to run correlations.
Let's focus on cells v3:v175, u3:u175. (I actually have 9 columns of
data, but I'm running two-variable correlations)

I know that I can get a correlation with the formula =CORREL(v3:v175,
u3:u175). But there seems to be no keystroke-minimizing way to lag one
of the variables. I tried using
=CORREL(INDIRECT("V5:V175"),INDIRECT("X5:X174")), thinking I could just
insert/delete a cell above the data I wanted to lag/unlag and keep my
references pure, but Excel didn't like that.

So, two questions:
1. Can my first approach be somehow made to work?
2. If you were doing what I was trying to do, how would you save
headaches?

Thanks.
 
M

misterhanky

Oh, dear. I feel stupid. Substitute:
=CORREL(INDIRECT("V5:V175"),INDIRECT("X5:X175")),
for
=CORREL(INDIRECT("V5:V175"),INDIRECT("X5:X174")),

and everything works.

/kicks self
 
Top