Trendline Coefficients

[Guest]

Does anyone know if there are functions available in or for Excel that return
the coefficents of logarithmic and polynomial trendlines, analogous to
Slope(...) and Intercept(...) for linear trendlines?

Thank you.

Sprinks

 

[Tushar Mehta]

Not only is LINEST a misleading name but the documentation is also
equally misleading. For more on how to get the coeffients for various
types of trendlines see
Trendline coefficients
http://www.tushar-mehta.com/excel/tips/trendline_coefficients.htm

--
Regards,

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

 

[Guest]

Thank you for your response, Tushar.

I read the material on your link, and appreciated the discussion regarding
values not significantly different from zero, and in overspecifying
regression. In your discussion of obtaining the two coefficients for an
exponenential trendline, you indicated that

=LINEST(y-range, LN(x-range)) will give the required a (slope) and b
(intercept) values.

I am missing how to enter this as an array value, such that it returns both
the slope AND intercept. If I enter the line as a normal formula, it indeed
does return the same slope value as the trendline. If you could provide a
step-by-step instruction on how to do this correctly, I'd very much
appreciate it.

Also, your discussion of how to return the three coefficients of a quadratic
trendline (the data with which I work never requires higher order
polynomials) was over my head. Could you provide a detailed discussion of
how to enter the needed formula(e) to return these coefficients?

Thank you very much for your assistance.

Sprinks

 

[Tushar Mehta]

One correction to your post. The array formula
=LINEST(y-range, LN(x-range))
applies to the logarithmic regression, not the exponential version.
The correct formula for the natural exponential version (i.e., of the
type y=a*exp(b*x) is =LINEST(LN(y-range), x) and the results for a and
be are given by a=EXP(intercept) and b=slope

That said, in the document itself instead of providing written step-by-
step instructions, I let the pictures do most of the talking. So, if
you look at the natural exponential regression, Figure 11 shows how to
carry it out. The =LINEST(LN(y-range), x) becomes the array formula
=LINEST(LN(E2:E16),A2:A16,TRUE,TRUE). Entered in the range A41:B45 it
gives both the slope and the intercept of the regression. In D41 and
E41 I have the results for the orignal 'a' and 'b' values.

The same approach applies to the polynomial regression. Figure 12
illustrates how to implement it in XL.

--
Regards,

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