[R] HELP! Excel and R give me totally different regression results using the exact same data
David Winsemius
dwinsemius at comcast.net
Wed Nov 7 01:57:23 CET 2012
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
More information about the R-help
mailing list