R nls results different from those of Excel ??

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

R nls results different from those of Excel ??

dgwenzi
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]]

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: R nls results different from those of Excel ??

glsnow
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 <[hidden email]> 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]]
>
> ______________________________________________
> [hidden email] 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.
[hidden email]

        [[alternative HTML version deleted]]

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: R nls results different from those of Excel ??

Rolf Turner-3
In reply to this post by dgwenzi

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]]
>
> ______________________________________________
> [hidden email] 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.
>

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: R nls results different from those of Excel ??

Jeff Newmiller
In reply to this post by glsnow
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:<[hidden email]>        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 <[hidden email]> 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 <[hidden email]>
>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]]
>>
>> ______________________________________________
>> [hidden email] 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.
>>

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: R nls results different from those of Excel ??

Prof J C Nash (U30A)
In reply to this post by dgwenzi
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, [hidden email] wrote:

> Message: 79
> Date: Mon, 18 Feb 2013 22:40:25 -0800
> From: Jeff Newmiller<[hidden email]>
> To: Greg Snow<[hidden email]>, David Gwenzi<[hidden email]>
> Cc: r-help<[hidden email]>
> Subject: Re: [R] R nls results different from those of Excel ??
> Message-ID:<[hidden email]>
> 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:<[hidden email]>         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<[hidden email]>  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<[hidden email]>
>> >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]]
>>> >>
>>> >>______________________________________________
>>> >>[hidden email]  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.
>>> >>

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: R nls results different from those of Excel ??

Hans W Borchers
In reply to this post by Jeff Newmiller
Jeff Newmiller <jdnewmil <at> dcn.davis.ca.us> writes:
>
> 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.

May I be allowed to say that the general comments on MS Excel may be alright,
in this special case they are not.  The Excel Solver -- which is made by an
external company, not MS -- has a good reputation for being fast and accurate.
And it indeed solves least-squares and nonlinear problems better than some of
the solvers available in R.
There is a professional version of this solver, not available from Microsoft,
that could be called excellent. We, and this includes me, should not be too
arrogant towards the outside, non-R world, the 'barbarians' as the ancient
Greeks called it.

Hans Werner

> ---------------------------------------------------------------------------
> 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.

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: R nls results different from those of Excel ??

Jeff Newmiller
I use Excel regularly, and do not consider this a "slam"... it is a fact. I am aware of Solver, but the query was about trend lines generated by Excel. In general it is possible to do arbitrarily complex computations with a four function calculator, but we don't describe that as something the calculator does. Setting up a Solver sheet to obtain trend coefficients is not a typical way to obtain them in Excel... that would be the user's doing, not Excel's.
---------------------------------------------------------------------------
Jeff Newmiller                        The     .....       .....  Go Live...
DCN:<[hidden email]>        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.

Hans W Borchers <[hidden email]> wrote:

>Jeff Newmiller <jdnewmil <at> dcn.davis.ca.us> writes:
>>
>> 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.
>
>May I be allowed to say that the general comments on MS Excel may be
>alright,
>in this special case they are not.  The Excel Solver -- which is made
>by an
>external company, not MS -- has a good reputation for being fast and
>accurate.
>And it indeed solves least-squares and nonlinear problems better than
>some of
>the solvers available in R.
>There is a professional version of this solver, not available from
>Microsoft,
>that could be called excellent. We, and this includes me, should not be
>too
>arrogant towards the outside, non-R world, the 'barbarians' as the
>ancient
>Greeks called it.
>
>Hans Werner
>
>>
>---------------------------------------------------------------------------
>> 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.
>
>______________________________________________
>[hidden email] 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.

______________________________________________
[hidden email] 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.