Using range names in functions

[JDC]

I need to measure the correlation between a large number of discrete series.
To do this quick, I did the following:

(1) Created a unique range name for each number series
(2) Created a alphabetical list of every range name in column A
(3) Transposed the the alphabetical list of range names from column A so
that each range name had its own column accross the page (starting with
column B, range names listed accross the page in row 1). I now had the same
list of range names listed vertically in column A and horizontly in the first
row.

Now, my question. I was hoping to create a forumula in one cell that I
could copy throughout the X-Y grid. The thought was that I would use the
CORREL function and pull the text labels into the formula so the formula
recognizes them as range names rather than as simple text? In one quick
copy, I'd have correlations measured for every paired combination.

The problem is that I can pull the text and have it be recognized as a
series. While I can create a single-cell formula that has CORREL (range name
1, range name 2). But, when I try to copy it accross, it acts as a absolute
reference and every new cell has the exact same formula.

Bottom line: how can I pulll a label from a single cell into a formula and
have that formula interpret that label as a range name, not as text.

I hope this make sense becuase I'm going crazy tring to figure this out.

 

[Glenn]

I need to measure the correlation between a large number of discrete series.
To do this quick, I did the following:

(1) Created a unique range name for each number series
(2) Created a alphabetical list of every range name in column A
(3) Transposed the the alphabetical list of range names from column A so
that each range name had its own column accross the page (starting with
column B, range names listed accross the page in row 1). I now had the same
list of range names listed vertically in column A and horizontly in the first
row.

Now, my question. I was hoping to create a forumula in one cell that I
could copy throughout the X-Y grid. The thought was that I would use the
CORREL function and pull the text labels into the formula so the formula
recognizes them as range names rather than as simple text? In one quick
copy, I'd have correlations measured for every paired combination.

The problem is that I can pull the text and have it be recognized as a
series. While I can create a single-cell formula that has CORREL (range name
1, range name 2). But, when I try to copy it accross, it acts as a absolute
reference and every new cell has the exact same formula.

Bottom line: how can I pulll a label from a single cell into a formula and
have that formula interpret that label as a range name, not as text.

I hope this make sense becuase I'm going crazy tring to figure this out.

Something like this:

=CORREL(INDIRECT($A2),INDIRECT(B$1))

 

[Glenn]

I need to measure the correlation between a large number of discrete series.
To do this quick, I did the following:

(1) Created a unique range name for each number series
(2) Created a alphabetical list of every range name in column A
(3) Transposed the the alphabetical list of range names from column A so
that each range name had its own column accross the page (starting with
column B, range names listed accross the page in row 1). I now had the same
list of range names listed vertically in column A and horizontly in the first
row.

Now, my question. I was hoping to create a forumula in one cell that I
could copy throughout the X-Y grid. The thought was that I would use the
CORREL function and pull the text labels into the formula so the formula
recognizes them as range names rather than as simple text? In one quick
copy, I'd have correlations measured for every paired combination.

The problem is that I can pull the text and have it be recognized as a
series. While I can create a single-cell formula that has CORREL (range name
1, range name 2). But, when I try to copy it accross, it acts as a absolute
reference and every new cell has the exact same formula.

Bottom line: how can I pulll a label from a single cell into a formula and
have that formula interpret that label as a range name, not as text.

I hope this make sense becuase I'm going crazy tring to figure this out.

Something like this:

=CORREL(INDIRECT($A2),INDIRECT(B$1))