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.
>
> ______________________________________________
> R-help@r-project.org 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]]


