Linked Questions

Popular Questions

Can I create an array with indirect?

Asked by At

I have some weekly stock return data that looks like this: enter image description here

I've hidden a lot of rows there, obviously. But basically there are 200ish different stocks and their weekly returns over the course of 10 years. They are paired with a return in column E for the same period for the S&P 500. I want to calculate betas with these Covariance(Stock, S&P 500)/Variance(S&P 500). I'm struggling just to create the Covariance portion right now:

Column A in my new sheet is a unique list of all the ticker symbols and my formula is as follows:

=COVARIANCE.P(INDIRECT("'Weekly Data'!D$"&MATCH(A2,'Weekly Data'!F:F,0)&":'Weekly Data'!D$"&MATCH(A2,'Weekly Data'!G:G,0)),INDIRECT("'Weekly Data'!E$"&MATCH(A2,'Weekly Data'!F:F,0)&":'Weekly Data'!E$"&MATCH(A2,'Weekly Data'!G:G,0)))

Getting a #REF error.

Related Questions