PDA

View Full Version : Solved: Incorporating dynamic ranges into CORREL function using VBA



Visual Newby
06-19-2012, 02:43 AM
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
06-19-2012, 03:01 AM
Use a dynamic named range, using a formula such as

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

Bob Phillips
06-19-2012, 03:03 AM
BTW, I have Excel 20010, and I can use full columns in CORREL. Do you have an older version that restricts this?

Visual Newby
06-19-2012, 04:09 AM
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
06-19-2012, 05:01 AM
Something like this

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

GTO
06-19-2012, 05:14 AM
BTW, I have Excel 20010, ...

:bow:

Bob Phillips
06-19-2012, 05:45 AM
:bow:

Mark,

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

GTO
06-19-2012, 05:53 AM
Agreed, but 20,010? VBA537 just seems like such a big jump for my poor head:p .

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

Visual Newby
06-19-2012, 08:41 AM
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:$BA ),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
06-19-2012, 08:55 AM
The formula isn't in R1C1 so use

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

Visual Newby
06-19-2012, 09:24 AM
Perfect! Thank you for such a quick resonse!

Aussiebear
06-19-2012, 11:21 PM
Agreed, but 20,010? VBA537 just seems like such a big jump for my poor head:p .

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

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

GTO
06-20-2012, 12:15 AM
I know, I am starting to believe it! :ack: