HELP! Excel and R give me totally different regression results using the exact same data
On Nov 6, 2012, at 4:20 PM, frauke wrote:
Hallo,
I am totally confused why Excel and R give me totally different regression
results for the data below. If you know the solution, please enlighten me.
In Excel I used LINEST() and Data>Data Analysis>Regression and both
(fortunately) gave the same result.
The coefficients were:
/b0=1.16, b1=0.957, b2=0.024, R2=0.0027, adjusted R2=-0.017/
In R I used model<-lm(as.numeric(test[,1]) /(wave sign here)/
as.numeric(test[,2]) + as.numeric(test[,3]))
The result is:
/Coefficients:
Estimate Std. Error t value Pr(>|t|)
(Intercept) 3.514 28.913 0.122 0.904
as.numeric(test[, 2]) 0.423 1.027 0.412 0.681
as.numeric(test[, 3]) 6.629 4.194 1.581 0.117
Residual standard error: 29.19 on 98 degrees of freedom
Multiple R-squared: 0.02706, Adjusted R-squared: 0.007204
F-statistic: 1.363 on 2 and 98 DF, p-value: 0.2607
I was unable to reproduce this result using the data you offered. Code is below. I'm guessing you failed to include a header argument in read table abd as a result were actually doing your regression on the integers underneath the factor representation. (Who knows what you did wrong in Excel.)
Does anybody know what is going on?
Have you looked at the data? Looks to me that assumptions of constant variance and normality of errors are violated.
Thank you for your time! Frauke
test<- read.table(text='
obs fcst48 rate24.last
[1,] "1.93" "6.9" "-0.13"
[2,] "5.23" "6.6" "6.52"
[3,] "5.25" "6.8" "5.85"
[4,] "5.26" "6.6" "1.27"
[5,] "5.26" "6.8" "-0.0700000000000003"
[6,] "5.34" "6.6" "-0.95"
[7,] "5.37" "6.9" "0.78"
[8,] "5.56" "6.7" "3.05"
[9,] "5.60" "7" "1.64"
[10,] "5.61" "6.9" "2.6"
[11,] "5.74" "6.7" "4.09"
[12,] "5.92" "6.8" "1.95"
[13,] "5.95" "6.8" "-5.87"
[14,] "6.08" "6.6" "0.1"
[15,] "6.13" "6.6" "2.6"
[16,] "6.16" "6.6" "-6.15"
[17,] "6.26" "6.5" "1.4"
[18,] "6.32" "7" "9.78"
[19,] "6.33" "6.5" "0.28"
[20,] "6.35" "6.6" "-0.0899999999999999"
[21,] "6.36" "6.6" "-0.13"
[22,] "6.37" "6.9" "-0.04"
[23,] "6.39" "6.5" "-2.14"
[24,] "6.39" "6.6" "0.15"
[25,] "6.39" "6.7" "2.29"
[26,] "6.40" "6.6" "-1.84"
[27,] "6.42" "6.7" "-2.25"
[28,] "6.43" "6.6" "-0.0300000000000002"
[29,] "6.45" "6.8" "0.220000000000001"
[30,] "6.47" "6.5" "-0.18"
[31,] "6.51" "6.9" "-1.8"
[32,] "6.55" "6.7" "-1.38"
[33,] "6.56" "6.6" "1.33"
[34,] "6.58" "6.7" "7.86"
[35,] "6.58" "6.8" "-0.220000000000001"
[36,] "6.61" "6.8" "-0.59"
[37,] "6.62" "6.7" "-0.24"
[38,] "6.62" "6.7" "-0.0999999999999996"
[39,] "6.64" "6.6" "-0.220000000000001"
[40,] "6.65" "6.7" "-9.63"
[41,] "6.69" "6.8" "-0.18"
[42,] "6.72" "6.9" "-0.739999999999999"
[43,] "6.73" "6.8" "0.83"
[44,] "6.76" "6.6" "-1.56"
[45,] "6.76" "6.7" "-1.64"
[46,] "6.79" "6.8" "-0.140000000000001"
[47,] "6.80" "6.9" "0.38"
[48,] "6.81" "6.8" "6.46"
[49,] "6.81" "6.9" "-0.0199999999999996"
[50,] "6.82" "6.9" "0.54"
[51,] "6.84" "6.9" "-0.19"
[52,] "6.84" "7" "-2.11"
[53,] "6.85" "6.7" "-0.170000000000001"
[54,] "6.88" "6.9" "0.0600000000000005"
[55,] "6.90" "6.7" "-0.0800000000000001"
[56,] "6.90" "7" "-0.18"
[57,] "6.91" "6.6" "-4.65"
[58,] "6.91" "6.6" "3"
[59,] "6.92" "6.6" "-0.219999999999999"
[60,] "6.94" "6.8" "1.4"
[61,] "6.99" "6.7" "-0.44"
[62,] "6.99" "6.9" "1.16"
[63,] "7.02" "6.8" "-1.72"
[64,] "7.03" "6.8" "-0.46"
[65,] "7.03" "6.9" "-0.24"
[66,] "7.04" "7" "-0.11"
[67,] "7.06" "6.9" "-0.13"
[68,] "7.07" "6.7" "-0.0199999999999996"
[69,] "7.08" "6.8" "0.23"
[70,] "7.09" "6.7" "0.66"
[71,] "7.09" "7" "-0.55"
[72,] "7.12" "7" "-0.17"
[73,] "7.15" "6.7" "-3.05"
[74,] "7.17" "6.7" "-0.140000000000001"
[75,] "7.26" "6.9" "-0.170000000000001"
[76,] "7.31" "7" "-1.57"
[77,] "7.36" "6.8" "-0.2"
[78,] "7.41" "6.6" "-0.29"
[79,] "7.42" "6.6" "-3.39"
[80,] "7.42" "6.8" "2.11"
[81,] "7.53" "6.9" "0.18"
[82,] "7.66" "6.6" "0"
[83,] "7.74" "6.7" "10.31"
[84,] "7.87" "6.9" "-0.0700000000000003"
[85,] "7.89" "6.6" "0.24"
[86,] "8.07" "6.8" "-0.25"
[87,] "8.17" "6.6" "0.97"
[88,] "8.38" "6.8" "-0.15"
[89,] "8.38" "6.9" "0.7"
[90,] "8.43" "6.8" "0.0999999999999979"
[91,] "8.92" "6.8" "0"
[92,] "9.20" "7" "0.18"
[93,] "9.79" "6.6" "0.33"
[94,] "9.87" "6.8" "0"
[95,] "10.72" "6.8" "0.13"
[96,] "11.61" "6.8" "1.88"
[97,] "16.44" "6.8" "-0.00999999999999801"
[98,] "16.55" "6.6" "2.96"
[99,] "16.88" "6.7" "1.16"
[100,] "17.72" "6.7" "-0.0699999999999994"
[101,] "17.91" "6.7" "-0.24"
[102,] "19.03" "6.8" "-0.430000000000001" ', header=TRUE)
test[] <- lapply( test, as.numeric)
plot(x=test[[2]], y=test[[1]])
plot(x=test[[3]], y=test[[1]])
plot(resid(lm(obs ~ ., data=test)))
plot(x=test[[2]], resid(lm(obs ~ ., data=test)))
plot(x=test[[3]], resid(lm(obs ~ ., data=test)))
# This show heteroscedasticity and non-normality of residuals around the predictions.
plot(predict(lm(obs ~ ., data=test)), resid(lm(obs ~ ., data=test)))
lm(obs ~ ., data=test) Call: lm(formula = obs ~ ., data = test) Coefficients: (Intercept) fcst48 rate24.last 10.9546962 -0.5097615 -0.0004139
summary(lm(obs ~ ., data=test))
Call:
lm(formula = obs ~ ., data = test)
Residuals:
Min 1Q Median 3Q Max
-5.5074 -1.1413 -0.6548 -0.1731 11.5415
Coefficients:
Estimate Std. Error t value Pr(>|t|)
(Intercept) 10.9546962 13.7494829 0.797 0.428
fcst48 -0.5097615 2.0350479 -0.250 0.803
rate24.last -0.0004139 0.1045771 -0.004 0.997
Residual standard error: 2.779 on 99 degrees of freedom
Multiple R-squared: 0.0006371, Adjusted R-squared: -0.01955
F-statistic: 0.03156 on 2 and 99 DF, p-value: 0.9689
David Winsemius, MD Alameda, CA, USA