Correlation-Coefficient

The following macro is to find the Linear Correlation-Coefficient
of a selected range. I'd like to use a Selection "Rng" instead of
a fixed range "C2:C11"
Can it be done?

Sub RSQ()
Dim Rng As Variant
Rng = Selection
Range("G10") = "= RSQ(C2:C11,ROW(C2:C11))": _
Range("G10") = Range("G10").Value
' I want to replace C2:C11 with Rng in the line above
End Sub

Sub RSQ()
Dim Rng As Variant
Rng = Selection
Range("G10").FormulaArray = "= RSQ(" & Selection.Address & ",ROW(" &
Selection.Address & "))": _
Range("G10") = Range("G10").Value
' I want to replace C2:C11 with Rng in the line above
End Sub

Your terminology is not exactly correct. RSQ is the square of the
correlation coefficient. Also, in Excel versions prior to 2003, CORREL()^2
is numerically better
than RSQ().

Jerry

"daniel chen" wrote:

> The following macro is to find the Linear Correlation-Coefficient
> of a selected range. I'd like to use a Selection "Rng" instead of
> a fixed range "C2:C11"
> Can it be done?
>
> Sub RSQ()
> Dim Rng As Variant
> Rng = Selection
> Range("G10") = "= RSQ(C2:C11,ROW(C2:C11))": _
> Range("G10") = Range("G10").Value
> ' I want to replace C2:C11 with Rng in the line above
> End Sub
>
>
>

You are correct. I should use
Range("G10") = "=G10^0.5": Range("G10") = Range("G10").Value
How do I replace C2:C11 with Rng

"Jerry W. Lewis" <post_a_reply@no_e-mail.com> wrote in message
news:39D4E6F7-9050-4E7F-A4A1-DB33B13F763A@microsoft.com...
> Sub RSQ()
> Dim Rng As Variant
> Rng = Selection
> Range("G10").FormulaArray = "= RSQ(" & Selection.Address & ",ROW(" &
> Selection.Address & "))": _
> Range("G10") = Range("G10").Value
> ' I want to replace C2:C11 with Rng in the line above
> End Sub
>
> Your terminology is not exactly correct. RSQ is the square of the
> correlation coefficient. Also, in Excel versions prior to 2003,
> CORREL()^2
> is numerically better
> than RSQ().
>
> Jerry
>
> "daniel chen" wrote:
>
>> The following macro is to find the Linear Correlation-Coefficient
>> of a selected range. I'd like to use a Selection "Rng" instead of
>> a fixed range "C2:C11"
>> Can it be done?
>>
>> Sub RSQ()
>> Dim Rng As Variant
>> Rng = Selection
>> Range("G10") = "= RSQ(C2:C11,ROW(C2:C11))": _
>> Range("G10") = Range("G10").Value
>> ' I want to replace C2:C11 with Rng in the line above
>> End Sub
>>
>>
>>

Hi Jerry,
You answered my question. Thanks

"Jerry W. Lewis" <post_a_reply@no_e-mail.com> wrote in message
news:39D4E6F7-9050-4E7F-A4A1-DB33B13F763A@microsoft.com...
> Sub RSQ()
> Dim Rng As Variant
> Rng = Selection
> Range("G10").FormulaArray = "= RSQ(" & Selection.Address & ",ROW(" &
> Selection.Address & "))": _
> Range("G10") = Range("G10").Value
> ' I want to replace C2:C11 with Rng in the line above
> End Sub
>
> Your terminology is not exactly correct. RSQ is the square of the
> correlation coefficient. Also, in Excel versions prior to 2003,
> CORREL()^2
> is numerically better
> than RSQ().
>
> Jerry
>
> "daniel chen" wrote:
>
>> The following macro is to find the Linear Correlation-Coefficient
>> of a selected range. I'd like to use a Selection "Rng" instead of
>> a fixed range "C2:C11"
>> Can it be done?
>>
>> Sub RSQ()
>> Dim Rng As Variant
>> Rng = Selection
>> Range("G10") = "= RSQ(C2:C11,ROW(C2:C11))": _
>> Range("G10") = Range("G10").Value
>> ' I want to replace C2:C11 with Rng in the line above
>> End Sub
>>
>>
>>