추세선의 결정계수에는 오류가 있다

단순히 회귀분석 모형 선택시 절편이 있는 경우와 절편이 없는 경우를 선택하는
옵션 정도로만 지레짐작 하고 그냥 넘겨 버렸다.

그러나 왠걸?
엑셀의 추세선 기능에서 절편이 없는 경우 결정계수 R²가 잘못 계산되는 것 아닌가!!!
(엑셀2010 베타판이라서 양넘 글이다.)

간단한 예제를 갖고 뭐가 문제인지 살펴보자.

이를 추세선 기능을 활용해 하나는 절편이 있는 경우, 다른 하나는 절편이 없는 경우를 선택해
차트에 함께 나타내면...

절편이 없는 경우 결정계수 R²에 붉은 칠을 했다.
먼가 이유가 있을 것이다.

위 예제를 갖고 직접 계산하면 결과는 아래 처럼 나온다.
그런데 절편이 없는 경우는 결정계수가 두 종류로 계산해 놨네?

결정계수1과 결정계수2가 있는데...
절편이 없는 경우 결정계수2는 앞서 구한 추세선에 붉은 칠한 값과 같다.

그런데 잘못된 값이다.
LinEst 함수를 갖고 절편이 있는 경우, 절편이 없는 경우 각각을 구해 결과값을 비교하면...
앞서 절편이 없는 경우 결정계수2에 나온 값은 그 어디에도 없다.

그렇다면 절편이 없는 경우 결정계수2는 어떻게 구한 값일까?

f5 셀 (절편이 없는 경우 - 결정계수2)
=1-SUMXMY2(A2:A6,MMULT(B2:B6^{1,0},F2:F3))/SUMPRODUCT((A2:A6-AVERAGE(A2:A6))^2)

그런데 이는 절편이 있는 경우에 사용하는 결정계수 계산 방식이다.
절편이 없는 경우엔 결정계수를 아래 처럼 계산한다.

f4 셀 (절편이 없는 경우 - 결정계수1)
=SUMPRODUCT((B2:B6*F2)^2)/(SUMSQ(A2:A6))

그리고 f4 셀 값이 절편이 없는 경우 LinEst 함수에서 얻은 값과 같다는 것을 알 수 있다.

즉 추세선에서 보여주는 절편이 없는 경우 결정계수 R²는 잘못된 결과값이다.