Function for Logarithmic Trendline

I have been using the trendline - logarithmic type - on some charts. Although
the equation in the form
y = a*ln(x) + b
can be made visible on the chart, I need a function that will return the a
and b coefficients.

I can't find such a function. I don't think logest is the same.

Art

To use the LINEST function for the coefficients of a log trendline see
Trendline coefficients
http://www.tushar-mehta.com/excel/ti...efficients.htm

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <[email protected]>,
[email protected] says...
> I have been using the trendline - logarithmic type - on some charts. Although
> the equation in the form
> y = a*ln(x) + b
> can be made visible on the chart, I need a function that will return the a
> and b coefficients.
>
> I can't find such a function. I don't think logest is the same.
>
> Art
>

Thank you Tushar. I did look at your reference materials, and learned a lot.
However, I still do not see how to use LINEST to derive the coefficients of a
logarithmic trendline. LINEST is for linear trends only, correct?

Let me clarify my problem. On my chart I have a log trendline that displays
as:
y = 88.28Ln(x) - 38.613. It has a very good fit (r sqared of .97).

I am looking for a function (or some method) of returning the coefficient
values of 88.28 and -38.613 so that I can use them in subsequent calculations.

Thanks,
Art



"Tushar Mehta" wrote:

> To use the LINEST function for the coefficients of a log trendline see
> Trendline coefficients
> http://www.tushar-mehta.com/excel/ti...efficients.htm
>
> --
> Regards,
>
> Tushar Mehta
> www.tushar-mehta.com
> Excel, PowerPoint, and VBA add-ins, tutorials
> Custom MS Office productivity solutions
>
> In article <[email protected]>,
> [email protected] says...
> > I have been using the trendline - logarithmic type - on some charts. Although
> > the equation in the form
> > y = a*ln(x) + b
> > can be made visible on the chart, I need a function that will return the a
> > and b coefficients.
> >
> > I can't find such a function. I don't think logest is the same.
> >
> > Art
> >
>

Art -

Using Excel's notation, the log trendline uses the equation Y = c*Ln(X)+b.

c = SLOPE(Yrange,LN(Xrange))
b = INTERCEPT(Yrange,LN(Xrange))


Tushar's web site shows how to get the same results using =LINEST(y-range,
LN(x-range)).

The LINEST function (multiple X variables) and the Slope & Intercept
functions (single X variable) may be used to fit linear combinations of
variables, in this case, linear combinations of transformed variables. So
they are not really limited to only linear trends.

- Mike
www.mikemiddleton.com

"ArthurJ" <[email protected]> wrote in message
news:[email protected]...
> Thank you Tushar. I did look at your reference materials, and learned a
> lot.
> However, I still do not see how to use LINEST to derive the coefficients
> of a
> logarithmic trendline. LINEST is for linear trends only, correct?
>
> Let me clarify my problem. On my chart I have a log trendline that
> displays
> as:
> y = 88.28Ln(x) - 38.613. It has a very good fit (r sqared of .97).
>
> I am looking for a function (or some method) of returning the coefficient
> values of 88.28 and -38.613 so that I can use them in subsequent
> calculations.
>
> Thanks,
> Art
>
> "Tushar Mehta" wrote:
>
>> To use the LINEST function for the coefficients of a log trendline see
>> Trendline coefficients
>> http://www.tushar-mehta.com/excel/ti...efficients.htm
>>
>> --
>> Regards,
>>
>> Tushar Mehta
>> www.tushar-mehta.com
>> Excel, PowerPoint, and VBA add-ins, tutorials
>> Custom MS Office productivity solutions
>>
>> In article <[email protected]>,
>> [email protected] says...
>> > I have been using the trendline - logarithmic type - on some charts.
>> > Although
>> > the equation in the form
>> > y = a*ln(x) + b
>> > can be made visible on the chart, I need a function that will return
>> > the a
>> > and b coefficients.
>> >
>> > I can't find such a function. I don't think logest is the same.
>> >
>> > Art
>> >
>>

Instead of LINEST, you could also use
=SLOPE(ydata,LN(xdata))
=INTERCEPT(ydata,LN(xdata))
for a and b. These formulas must also be array entered (Ctrl-Shift-Enter).

Jerry

ArthurJ wrote:

> I have been using the trendline - logarithmic type - on some charts. Although
> the equation in the form
> y = a*ln(x) + b
> can be made visible on the chart, I need a function that will return the a
> and b coefficients.
>
> I can't find such a function. I don't think logest is the same.
>
> Art

Jerry (or Mike),

Is there an error in SLOPE or INTERCEPT contained in your response? They are
identical ... ?

I have only one independent x-variable.

I need a step by step on this. Here is what I tried: I calculated ln(xdata)
for each record. I then entered the SLOPE formula into one cell below the
table exactly as written below. I then selected that cell and the adjoining
cell to the right, pressed F2, then Ctrl-Shift-Enter. Both cells evaluate to
the same number.

Art

"Jerry W. Lewis" wrote:

> Instead of LINEST, you could also use
> =SLOPE(ydata,LN(xdata))
> =INTERCEPT(ydata,LN(xdata))
> for a and b. These formulas must also be array entered (Ctrl-Shift-Enter).
>
> Jerry
>
> ArthurJ wrote:
>
> > I have been using the trendline - logarithmic type - on some charts. Although
> > the equation in the form
> > y = a*ln(x) + b
> > can be made visible on the chart, I need a function that will return the a
> > and b coefficients.
> >
> > I can't find such a function. I don't think logest is the same.
> >
> > Art
>
>

Mike,
I tried charting y-range and LN(x-range). It is not linear, but is still log
shaped (concave downward), though not as much as the original chart of
x-range,y-range. It doesn't make sense to me to calculate LINEST on this data
which is not linear. I'm just not getting this yet.
Art

"Mike Middleton" wrote:

> "Tushar's web site shows how to get the same results using =LINEST(y-range,
> LN(x-range))".

> - Mike
> www.mikemiddleton.com
>
> "ArthurJ" <[email protected]> wrote in message
> news:[email protected]...
> > Thank you Tushar. I did look at your reference materials, and learned a
> > lot.
> > However, I still do not see how to use LINEST to derive the coefficients
> > of a
> > logarithmic trendline. LINEST is for linear trends only, correct?
> >
> > Let me clarify my problem. On my chart I have a log trendline that
> > displays
> > as:
> > y = 88.28Ln(x) - 38.613. It has a very good fit (r sqared of .97).
> >
> > I am looking for a function (or some method) of returning the coefficient
> > values of 88.28 and -38.613 so that I can use them in subsequent
> > calculations.
> >
> > Thanks,
> > Art
> >
> > "Tushar Mehta" wrote:
> >
> >> To use the LINEST function for the coefficients of a log trendline see
> >> Trendline coefficients
> >> http://www.tushar-mehta.com/excel/ti...efficients.htm
> >>
> >> --
> >> Regards,
> >>
> >> Tushar Mehta
> >> www.tushar-mehta.com
> >> Excel, PowerPoint, and VBA add-ins, tutorials
> >> Custom MS Office productivity solutions
> >>
> >> In article <[email protected]>,
> >> [email protected] says...
> >> > I have been using the trendline - logarithmic type - on some charts.
> >> > Although
> >> > the equation in the form
> >> > y = a*ln(x) + b
> >> > can be made visible on the chart, I need a function that will return
> >> > the a
> >> > and b coefficients.
> >> >
> >> > I can't find such a function. I don't think logest is the same.
> >> >
> >> > Art
> >> >
> >>
>
>
>

Art -

There seem to be at least two things to consider. First, look at your data
(e.g., an XY Scatter plot) and, based partly on what you plan to do with the
results of your analysis, choose an appropriate functional form for a best
fit. Second, use Excel to determine the parameters for the best-fit
function.

From your first post, I thought you were at the second step. Now, it seems
you're back on the first step. So, you could post the data in the body of a
message (if it's not too much) if you want us the "look at the data." And,
describe the context of your analysis (what is it for?).

- Mike

"ArthurJ" <[email protected]> wrote in message
news:[email protected]...
> Mike,
> I tried charting y-range and LN(x-range). It is not linear, but is still
> log
> shaped (concave downward), though not as much as the original chart of
> x-range,y-range. It doesn't make sense to me to calculate LINEST on this
> data
> which is not linear. I'm just not getting this yet.
> Art
>
> "Mike Middleton" wrote:
>
>> "Tushar's web site shows how to get the same results using
>> =LINEST(y-range,
>> LN(x-range))".
>
>> - Mike
>> www.mikemiddleton.com
>>
>> "ArthurJ" <[email protected]> wrote in message
>> news:[email protected]...
>> > Thank you Tushar. I did look at your reference materials, and learned a
>> > lot.
>> > However, I still do not see how to use LINEST to derive the
>> > coefficients
>> > of a
>> > logarithmic trendline. LINEST is for linear trends only, correct?
>> >
>> > Let me clarify my problem. On my chart I have a log trendline that
>> > displays
>> > as:
>> > y = 88.28Ln(x) - 38.613. It has a very good fit (r sqared of .97).
>> >
>> > I am looking for a function (or some method) of returning the
>> > coefficient
>> > values of 88.28 and -38.613 so that I can use them in subsequent
>> > calculations.
>> >
>> > Thanks,
>> > Art
>> >
>> > "Tushar Mehta" wrote:
>> >
>> >> To use the LINEST function for the coefficients of a log trendline see
>> >> Trendline coefficients
>> >> http://www.tushar-mehta.com/excel/ti...efficients.htm
>> >>
>> >> --
>> >> Regards,
>> >>
>> >> Tushar Mehta
>> >> www.tushar-mehta.com
>> >> Excel, PowerPoint, and VBA add-ins, tutorials
>> >> Custom MS Office productivity solutions
>> >>
>> >> In article <[email protected]>,
>> >> [email protected] says...
>> >> > I have been using the trendline - logarithmic type - on some charts.
>> >> > Although
>> >> > the equation in the form
>> >> > y = a*ln(x) + b
>> >> > can be made visible on the chart, I need a function that will return
>> >> > the a
>> >> > and b coefficients.
>> >> >
>> >> > I can't find such a function. I don't think logest is the same.
>> >> >
>> >> > Art
>> >> >
>> >>
>>
>>
>>

Art -

If your xdata is in A2:A8 and your ydata is in B2:B8, in some other cell
type =SLOPE(B2:B8,LN(A2:A8)) and press Enter.

In another cell, type =INTERCEPT(B2:B8,LN(A2:A8)) and press Enter.

I don't think these functions need to be array-entered.

- Mike
www.mikemiddleton.com

"ArthurJ" <[email protected]> wrote in message
news:[email protected]...
> Jerry (or Mike),
>
> Is there an error in SLOPE or INTERCEPT contained in your response? They
> are
> identical ... ?
>
> I have only one independent x-variable.
>
> I need a step by step on this. Here is what I tried: I calculated
> ln(xdata)
> for each record. I then entered the SLOPE formula into one cell below the
> table exactly as written below. I then selected that cell and the
> adjoining
> cell to the right, pressed F2, then Ctrl-Shift-Enter. Both cells evaluate
> to
> the same number.
>
> Art
>
> "Jerry W. Lewis" wrote:
>
>> Instead of LINEST, you could also use
>> =SLOPE(ydata,LN(xdata))
>> =INTERCEPT(ydata,LN(xdata))
>> for a and b. These formulas must also be array entered
>> (Ctrl-Shift-Enter).
>>
>> Jerry
>>
>> ArthurJ wrote:
>>
>> > I have been using the trendline - logarithmic type - on some charts.
>> > Although
>> > the equation in the form
>> > y = a*ln(x) + b
>> > can be made visible on the chart, I need a function that will return
>> > the a
>> > and b coefficients.
>> >
>> > I can't find such a function. I don't think logest is the same.
>> >
>> > Art
>>
>>

Thanks for everyone who stuck with me on these questions. I now have
successfully implemented the suggested procedures contained in these replies,
which included using linest, or slope and intercept to directly calculate the
a and b coefficients of the natural log form.

Art