CODEWITHSUNDEEP
excelexcel-formula
extensionhelp
extensionhelp

Reputation: 574

Can I create an array with indirect?

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.

Upvotes: 0

Views: 264

Answers (1)

Axel Richter
Axel Richter

Reputation: 61860

Your concatenation

"'Weekly Data'!D$"&MATCH(A2,'Weekly Data'!F:F,0)&":'Weekly Data'!D$"&MATCH(A2,'Weekly Data'!G:G,0)

leads to the wrong string representation of a cell range address.

It leads to 'Weekly Data'!D$2:'Weekly Data'!D$528 in case of ORCL. But in Excel it would must be 'Weekly Data'!D$2:D$528. Note, the sheet name only once.

So correct formula would be 

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

But as often INDIRECT could be replaced by INDEX. This is the better approach because of the volatile behavior of INDIRECT, which gets recalculated on each change in sheet and not only on change of the cells in function parameters.

The INDEX formula would be:

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

Upvotes: 1

Related Questions