Hi all I have a set of data whose scatter plot shows a very nice power relationship. My problem is when I fit a Power Trend Line in an Excel spreadsheet, I get the model y= 44.23x^2.06 with an R square value of 0.72. Now, if I input the same data into R and use model< -nls(y~ a*x^b , trace=TRUE, data= my_data, start = c(a=40, b=2)) I get a solution with a = 246.29 and b = 1.51. I have tried several starting values and this what I always get. I was expecting to get a value of a close to 44 and that of b close to 2. Why are these values of a and b so different from those Excel gave me. Also the R square value for the nls model is as low as 0.41. What have I done wrong here? Please help. Thanks in advance David [[alternative HTML version deleted]]
Have you plotted the data and the lines to see how they compare? (see fortune(193)). Is there error around the line in the data? The nls function is known to not work well when there is no error around the line. Also check and make sure that the 2 methods are fitting the same model. You might consider taking the log of both sides of the function to turn it into a linear function and using lm to fit the logs. On Mon, Feb 18, 2013 at 9:49 PM, David Gwenzi <dgwenzi@gmail.com> wrote:> Hi all > > I have a set of data whose scatter plot shows a very nice power > relationship. My problem is when I fit a Power Trend Line in an Excel > spreadsheet, I get the model y= 44.23x^2.06 with an R square value of 0.72. > Now, if I input the same data into R and use > model< -nls(y~ a*x^b , trace=TRUE, data= my_data, start = c(a=40, b=2)) I > get a solution with a = 246.29 and b = 1.51. I have tried several starting > values and this what I always get. I was expecting to get a value of a > close to 44 and that of b close to 2. Why are these values of a and b > so different from those Excel gave me. Also the R square value for the nls > model is as low as 0.41. What have I done wrong here? Please help. Thanks > in advance > > David > > [[alternative HTML version deleted]] > > ______________________________________________ > 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. >-- Gregory (Greg) L. Snow Ph.D. 538280@gmail.com [[alternative HTML version deleted]]
To paraphrase Bill Venables (see fortune(217)):
Simple. Excel must be broken. Have you reported it to them?
(The difference in this case is that it is probable that Excel *is* broken.
It usually is.)
cheers,
Rolf Turner
On 02/19/2013 05:49 PM, David Gwenzi wrote:> Hi all
>
> I have a set of data whose scatter plot shows a very nice power
> relationship. My problem is when I fit a Power Trend Line in an Excel
> spreadsheet, I get the model y= 44.23x^2.06 with an R square value of 0.72.
> Now, if I input the same data into R and use
> model< -nls(y~ a*x^b , trace=TRUE, data= my_data, start = c(a=40, b=2))
I
> get a solution with a = 246.29 and b = 1.51. I have tried several starting
> values and this what I always get. I was expecting to get a value of a
> close to 44 and that of b close to 2. Why are these values of a and b
> so different from those Excel gave me. Also the R square value for the nls
> model is as low as 0.41. What have I done wrong here? Please help. Thanks
> in advance
>
> David
>
> [[alternative HTML version deleted]]
>
> ______________________________________________
> R-help at 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.
>
Prof J C Nash (U30A)
2013-Feb-19 13:10 UTC
[R] R nls results different from those of Excel ??
This thread unfortunately pushes a number of buttons:
- Excel computing a model by linearization which fits to
residual = log(data) - log(model)
rather than
wanted_residual = data - model
The COBB.RES example in my (freely available but rather dated) book
at http://macnash.telfer.uottawa.ca/nlpe/ shows an example where
comparing the results shows how extreme the differences can be.
- nls not doing well when the fit is near perfect. Package nlmrt is
happy to compute such models, which have a role in approximation. The
builders of nls() are rather (too?) insistent that nls() is a
statistical function rather than simply nonlinear least squares. I can
agree with their view in its context, but not for a general scientific
computing package that R has become. It is one of the gotchas of R.
- Rolf's suggestion to inform Microsoft is, I'm sure, made with the sure
knowledge that M$ will ignore such suggestions. They did, for example,
fix one financial function temporarily (I don't know which). However,
one of Excel's maintainers told me he would disavow admitting that
"Bill" called to tell them to put the bug back in because the
president
of a large American bank called to complain his 1998 profit and loss
spreadsheet had changed in the "new" version of Excel. Appearances are
more important than getting things right. At the same conference where
this "I won't admit I told you" conversation took place, a
presentation
was made estimating that 95% of major investment decisions were made
based on Excel spreadsheets. The conference took place before the 2008
crash. One is tempted to make non-statistical inferences.
JN
On 13-02-19 06:00 AM, r-help-request at r-project.org
wrote:> Message: 79
> Date: Mon, 18 Feb 2013 22:40:25 -0800
> From: Jeff Newmiller<jdnewmil at dcn.davis.CA.us>
> To: Greg Snow<538280 at gmail.com>, David Gwenzi<dgwenzi at
gmail.com>
> Cc: r-help<r-help at r-project.org>
> Subject: Re: [R] R nls results different from those of Excel ??
> Message-ID:<50c09528-7917-4a20-ad0e-5f4ebf9d0d6d at
email.android.com>
> Content-Type: text/plain; charset=UTF-8
>
> Excel definitely does not use nonlinear least squares fitting for power
curve fitting. It uses linear LS fitting of the logs of x and y. There should be
no surprise in the OP's observation.
> ---------------------------------------------------------------------------
> Jeff Newmiller The ..... ..... Go Live...
> DCN:<jdnewmil at dcn.davis.ca.us> Basics: ##.#. ##.#.
Live Go...
> Live: OO#.. Dead: OO#.. Playing
> Research Engineer (Solar/Batteries O.O#. #.O#. with
> /Software/Embedded Controllers) .OO#. .OO#. rocks...1k
> ---------------------------------------------------------------------------
> Sent from my phone. Please excuse my brevity.
>
> Greg Snow<538280 at gmail.com> wrote:
>
>> >Have you plotted the data and the lines to see how they compare?
(see
>> >fortune(193)).
>> >
>> >Is there error around the line in the data? The nls function is
known
>> >to
>> >not work well when there is no error around the line. Also check
and
>> >make
>> >sure that the 2 methods are fitting the same model.
>> >
>> >You might consider taking the log of both sides of the function to
turn
>> >it
>> >into a linear function and using lm to fit the logs.
>> >
>> >
>> >On Mon, Feb 18, 2013 at 9:49 PM, David Gwenzi<dgwenzi at
gmail.com>
>> >wrote:
>> >
>>> >>Hi all
>>> >>
>>> >>I have a set of data whose scatter plot shows a very nice
power
>>> >>relationship. My problem is when I fit a Power Trend Line
in an Excel
>>> >>spreadsheet, I get the model y= 44.23x^2.06 with an R
square value of
>> >0.72.
>>> >>Now, if I input the same data into R and use
>>> >>model< -nls(y~ a*x^b , trace=TRUE, data= my_data, start
= c(a=40,
>> >b=2)) I
>>> >>get a solution with a = 246.29 and b = 1.51. I have tried
several
>> >starting
>>> >>values and this what I always get. I was expecting to get a
value of
>> >a
>>> >>close to 44 and that of b close to 2. Why are these values
of a and b
>>> >>so different from those Excel gave me. Also the R square
value for
>> >the nls
>>> >>model is as low as 0.41. What have I done wrong here?
Please help.
>> >Thanks
>>> >>in advance
>>> >>
>>> >>David
>>> >>
>>> >> [[alternative HTML version deleted]]
>>> >>
>>> >>______________________________________________
>>> >>R-help at 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.
>>> >>
Reasonably Related Threads
- NLS results different from Excel -- Tricky fortunes nomination
- NLS results different from Excel
- Non-linear curve fitting (nls): starting point and quality of fit
- non linear regression with nls
- 'singular gradient matrix’ when using nls() and how to make the program skip nls( ) and run on