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