Correlation-Coefficient

D

daniel chen

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
 
J

Jerry W. Lewis

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
 
D

daniel chen

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

daniel chen

Hi Jerry,
You answered my question. Thanks

Jerry W. Lewis said:
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
Click to expand...
 
Top