CODEWITHSUNDEEP
excelregressioncorrelation
MM Cui
MM Cui

Reputation: 51

Excel Trend line (SLOPE() ) and CORREL() yields different coefficients

I'm trying to use excel to get the coefficient for two financial market spreads using two methods on data series Sprd1 against data series Sprd2:

1) I used scatter plot and simply added a trend line, showing R^2 (0.4052) and Coefficient (0.614). Trend line should be using SLOPE() to get the coefficient...

2) I used =CORREL(Sprd1, Sprd2), showing 0.637; =RSQ(Sprd1, Sprd2), yielding 0.4052.

I understand that the R-sq values should be pretty close. But why would the coefficents differ? I'm trying to look for any difference in terms of excel's embedded methods or assumptions on the trendline and the CORREL.

Thank you very much!

Upvotes: 0

Views: 1653

Answers (1)

SeanC
SeanC

Reputation: 15923

While both RSQ and CORREL work from the same equation

pearson equation

the value returned by RSQ is the square of that result.
i.e. RSQ()=CORREL()^2

SLOPE, on the other hand, does not use (y-MEAN(y))^2, nor does it take a square root of the denominator:
SLOPE function

so will give slightly different results, depending on the mean of y

Upvotes: 1

Related Questions