RSQ (Correlation)

Hi,

I have 6 known X's for which I would like to find the correlation coefficient with a single known Y. The problem is that the sample size (n) needs to be adjusted down whenever a X doesn't have data.

For example, in column F I don't begin to have data until row49 and so i only want to correlate row49 through the last row of data in column F (row375) with the same rows in column I.

How would I dynamically find all the values in a given column and then correlate those with the same rows in column I? The data will get larger and larger every month and so I need a formula to offset to the first non-blank row in a X column and then correlate with the same offset data range in column I.

Thanks,
John

Any help would be very very very appreciated

Originally Posted by John Bates

How would I dynamically find all the values in a given column and then correlate those with the same rows in column I?

Don't quite understand this part of it. What exactly are you looking to do? As for the formula to get dynamic ranges, for column C, it would be =C11:INDEX(C:C,MATCH(9.99999999999999E+307,C:C)), for example. You should be able to either used this as a named range or plug it in anywhere you already have a range for C11:C1000 or C11:C375.

Hi, here is a possible solution. Seems to work, as far as I can see. It can without doubt be made much more simple and efficient, though...

The method is to identify where each X-series starts and ends,
return the addresses and use these to create a range to use with INDIRECT in your RSQ-formula.


First frame contains the code for column C, (C1-C9).
These codes can be copied across to col H.
Row 1 (letter C to H) must however be entered manually

Please Login or Register  to view this content.

Here's the result from the code (A1:A9)

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Hi, you may have to replace all the ";" with "," to make the code work.
estige