Hello, You are using an Excel "feature" documented as bug by Microsoft. See
http://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 https://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: > http://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. > > ______________________________________________ > [email protected] mailing list > https://stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guide http://www.R-project.org/posting-guide.html > and provide commented, minimal, self-contained, reproducible code. [[alternative HTML version deleted]] ______________________________________________ [email protected] mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.
