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

David Winsemius dwinsemius at comcast.net
Fri Nov 12 23:28:09 CET 2010

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  

This might be the equivalent R operation:

 > x<-1:26
 > y<-exp(x)
 > lm(log(y) ~ x)

lm(formula = log(y) ~ x)

(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

More information about the R-help mailing list