# [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
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

```