MrExcel Publishing
Your One Stop for Excel Tips & Solutions

trendline equation coefficients


Posted by Greg Walton on January 28, 2002 10:55 AM

Excel will display chart trendline equations on its charts. I type the coefficients into cells and use the formaulas in the spreadsheet. However, the coefficients change each time the chart data changes and I have to retype the coefficients into the cells each time this happens. Where does excel store the trendline coefficents? I want to refer to these storage locations in the cells and have excel update the coefficents for me rather than retype them each time by hand. Thanks for the help.


Posted by Bariloche on January 28, 2002 12:14 PM

Greg,

See if the code written by David Braden, available here) helps. His function "Regress" is probably what you're looking for.


enjoy

Posted by Bariloche on January 28, 2002 12:27 PM

an additional resource

Greg,

Here's a MSFT KB article that also may be helpful. Link


have fun

Posted by Mark W. on January 28, 2002 12:50 PM

Are you using a linear trendline? If so...

The LINEST worksheet function will return a
horizontal array containing the slope(s) and
y-intercept. See the Help topic for LINEST.

Posted by Bariloche on January 28, 2002 12:59 PM

more to the point

Greg,

Here's a response to this question that was posted by Jerry Lewis on the excel.misc NG:


"Use LINEST()
For example, suppose y is in A1:A10 and x is in B1:B10 and you want to
fit a cubic equation. Highlight 4 cells in a row (for the four
coefficients) and input the formula =LINEST(A1:A10,B1:B10^{1,2,3}) as an
array formula (ctrl+shft+enter). If you do it right, the formula will
appear as {=LINEST(A1:A10,B1:B10^{1,2,3})} even though you did not type
the curly brackets.

Jerry W. Lewis StatsMan@erols.com"

enjoy

Greg, Here's a MSFT KB article that also may be helpful. Link

: Greg, : See if the code written by David Braden, available here) helps. His function "Regress" is probably what you're looking for. : : enjoy