frauke
2012-Nov-07 00:20 UTC
[R] HELP! Excel and R give me totally different regression results using the exact same data
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 / Does anybody know what is going on? Thank you for your time! Frauke test<- 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" -- View this message in context: r.789695.n4.nabble.com/HELP-Excel-and-R-give-me-totally-different-regression-results-using-the-exact-same-data-tp4648648.html Sent from the R help mailing list archive at Nabble.com.
David Winsemius
2012-Nov-07 00:57 UTC
[R] 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.2607I 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! Frauketest<- 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
Rui Barradas
2012-Nov-07 12:05 UTC
[R] HELP! Excel and R give me totally different regression results using the exact same data
Hello, You are using an Excel "feature" documented as bug by Microsoft. See support.microsoft.com/kb/214230 It warns that both function LINEST() and the regression tool in Analysis ToolPack may give incorrect results. Search the help pages to get long discussions on this. Among others you'll find the link above in stat.ethz.ch/pipermail/r-help/2012-July/318348.html Looking at your results, don't you find a negative R^2 suspect? Hope this helps, Rui Barradas Em 07-11-2012 00:20, frauke escreveu:> 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 > / > > Does anybody know what is going on? > > Thank you for your time! Frauke > > > > test<- > 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" > > > > -- > View this message in context: r.789695.n4.nabble.com/HELP-Excel-and-R-give-me-totally-different-regression-results-using-the-exact-same-data-tp4648648.html > Sent from the R help mailing list archive at Nabble.com. > > ______________________________________________ > R-help@r-project.org mailing list > stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guide R-project.org/posting-guide.html > and provide commented, minimal, self-contained, reproducible code.[[alternative HTML version deleted]]
frauke
2012-Nov-07 16:53 UTC
[R] HELP! Excel and R give me totally different regression results using the exact same data
Hi David, hi Rui, thanks for your quick replies. I have replicated David's R results and confirmed them with Minitab. Though I'm not sure what you are trying to tell me with the code you wrote, David. Do you mean, I should use a dataframe rather than a matrix, or use the "data=" part of the lm() function? Rui seems to be right, too. Excel's regression function doesn't work; I cannot replicate the Minitab and R results with it. According to the Microsoft website this is probably because the x- and y-values overlap. I am truly astonished that such a major bug doesn't at least have a major red flag to it. Thank you! Frauke -- View this message in context: r.789695.n4.nabble.com/HELP-Excel-and-R-give-me-totally-different-regression-results-using-the-exact-same-data-tp4648648p4648723.html Sent from the R help mailing list archive at Nabble.com.