[R] Replicate Excel's LOGEST worksheet function in R

[Jeff Newmiller]

Most software for curve fitting uses linear fits in conjunction with some combination of logarithms of your original in order to obtain logarithmic, power or exponential curve fits. The nls approach is arguably more correct, but it will yield different results than "normal", and may be finicky with some data sets.

Anyway, I recommend you learn from David before criticizing his assistance.

cran.30.miller_2555 at spamgourmet.com wrote:

>On Fri, Nov 12, 2010 at 5:28 PM, David Winsemius -
>dwinsemius at comcast.net
><+cran+miller_2555+c0e7477398.dwinsemius#comcast.net at spamgourmet.com>
>wrote:
>
>>
>> On Nov 12, 2010, at 5:07 PM, David Winsemius wrote:
>>
>>
>>> On Nov 12, 2010, at 4:22 PM, cran.30.miller_2555 at spamgourmet.com
>wrote:
>>>
>>>  Hi -
>>>>
>>>>   I have a dataframe of (x,y) values. I'd like to fit an
>exponential
>>>> curve to the data for further statistical analysis (pretty much the
>same
>>>> functionality provided by Excel's LOGEST worksheet array function).
>Can
>>>> someone point me to the (set of) functions/ package that is best
>suited
>>>> to
>>>> provide this functionality? Admittedly, I am a novice in the use of
>R
>>>> statistical functions, so a brief example of how to compute a
>correlation
>>>> coefficient off a fitted exponential curve would be greatly
>appreciated
>>>> (though I could probably work through it over time if I knew the
>proper R
>>>> tools).
>>>>
>>>>
>>> Probably (not seeing a clear description of the LOGEST function):
>>>
>>> ?exp
>>> ?log
>>> ?lm
>>> ?cor
>>>
>>>
>> I set up a OO.org Calc spreadsheet which has a lot of Excel
>work-alike
>> functions and does have a LOGEST. Giving an argument of x=1:26 and
>y=exp(x)
>> to the first two arguments of LOGEST, I get 1 and e. The OO.org help
>page
>> says
>> "FunctionType (optional). If Function_Type = 0, functions in the form
>y =
>> m^x will be calculated. Otherwise, y = b*m^x functions will be
>calculated."
>>
>> This might be the equivalent R operation:
>>
>> > x<-1:26
>> > y<-exp(x)
>> > lm(log(y) ~ x)
>>
>> Call:
>> lm(formula = log(y) ~ x)
>>
>> Coefficients:
>> (Intercept)            x
>>          0            1
>>
>> > exp(coef(lm(log(y) ~ x)))
>> (Intercept)           x
>>   1.000000    2.718282
>>
>> Note this is not a correlation coefficient but rather an
>(exponentiated)
>> regression coefficient.
>>
>> --
>> David Winsemius, MD
>> West Hartford, CT
>>
>>
>>
>Thanks, but I'm looking to fit an exponential curve (not a linear
>model).
>However, I was able to identify the `nls()` function that works well
>(adapted from John Fox's contribution "Nonlinear Regression and
>Nonlinear
>Least
>Squares<http://cran.r-project.org/doc/contrib/Fox-Companion/appendix-nonlinear-regression.pdf>"
>[Jan 2002] ref:
>http://cran.r-project.org/doc/contrib/Fox-Companion/appendix-nonlinear-regression.pdf).
>For those interested, the following short script highlights my simple
>test
>case (though a little sloppy):
>
>mydf <- as.data.frame(cbind(1:6,rev(c(48.0000, 24.0000, 12.0000,
>6.0000,
>3.0000, 1.5000)),rev(c( 51.4943, 12.4048, 12.9587, 3.7707, 2.4253,
>2.0400))));
>colnames(mydf) <- c("X","Y","Y2");
>
>my.mod <- nls(Y2 ~ a*exp(b*X), data=mydf, start=list(a=3.00,b=2.00),
>trace=T)
>
>plot(mydf[,"X"],residuals(my.mod))
>plot(mydf[,"X"],mydf[,"Y2"], lwd=1)
>lines(mydf[,"X"],fitted.values(my.mod), lwd=2)
>
>	[[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.

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