Correlation-Coefficient

  • Thread starter Thread starter daniel chen
  • Start date Start date
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
 
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
 
You are correct. I should use
Range("G10") = "=G10^0.5": Range("G10") = Range("G10").Value
How do I replace C2:C11 with Rng
 
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...
 
Back
Top