Solved: Incorporating dynamic ranges into CORREL function using VBA

[Visual Newby]

Hi, I'm stuck with a problem on calculating the correlation coefficient of 2 ranges when the ranges are continually expanding on a monthly basis. I have solved this problem previously for functions such as STDEV by selecting the whole column but unfortunately this does not work for the CORREL function.

Is anyone able to give me an example of how to incorporate a dynamic range into the CORREL function?

Thanks

[Bob Phillips]

Use a dynamic named range, using a formula such as

=OFFSET($A$1,0,0,COUNTA($A:$A),1)

[Bob Phillips]

BTW, I have Excel 20010, and I can use full columns in CORREL. Do you have an older version that restricts this?

[Visual Newby]

Yes I'm using 2003. I haven't used dynamic ranges before, would you be able to give me an example of how I can incorporate this into the correl function?

Say for example i need to calculate the correlation between a 2 dynamic ranges in column A and B

[Bob Phillips]

Something like this

=CORREL(OFFSET($A$1,0,0,COUNTA($A:$A),1),OFFSET($A$1,0,1,COUNTA($A:$A),1))

[GTO]

BTW, I have Excel 20010, ...

[Bob Phillips]

Mark,

I have suggested before that you get on-board, you can't keep these old dinosaur versions indefinitely

[GTO]

Agreed, but 20,010? VBA537 just seems like such a big jump for my poor head .

Off to bed for real; a great day to you and yours

[Visual Newby]

Thanks for this. Based on the above I am applying the formula to columns BA and BB using the formula below:

ActiveCell.FormulaR1C1 = "=CORREL(OFFSET($BA$1,0,0,COUNTA($BA:$BA),1),OFFSET($BA$1,0,1,COUNTA($BA:$B A),1))"

However, for some reason I'm getting an error:

"Run time error 1004. Application defined or object defined error"

Can you see where my formula is going wrong

[Bob Phillips]

The formula isn't in R1C1 so use

[VBA]ActiveCell.Formula = "=CORREL(OFFSET($BA$1,0,0,COUNTA($BA:$BA),1),OFFSET($BA$1,0,1,COUNTA($B A:$B A),1))"
[/VBA]

[Visual Newby]

Perfect! Thank you for such a quick resonse!

[Aussiebear]

Agreed, but 20,010? VBA537 just seems like such a big jump for my poor head .

Off to bed for real; a great day to you and yours

Hey Mark its not the first time Bob's indicated he is using a future version.

[GTO]

I know, I am starting to believe it!