Trendline Chart Data

[Guest]

Hi,

Besides guessing, can I get the data points from my Trendline on my Chart?
I am looking for amounts from the Trendline.

Thanks,

Stephanie

 

[Tushar Mehta]

Hi,

Besides guessing, can I get the data points from my Trendline on my Chart?
I am looking for amounts from the Trendline.

Thanks,

Stephanie

I am not sure what exactly you want. Do you want to see the equation of the
trendline? If so, double-click it and from the Options tab set the
appropriate checkboxes.

If you want the trendline coefficients in worksheet cells, you can either
use code from Dave Braden that I modified or compute the coefficients w/o
using a trendline. For both see

Trendline Coefficients and Regression Analysis
http://www.tushar-mehta.com/excel/tips/trendline_coefficients.htm

The code is towards the end of the long web page. A link to the post in the
google archives is about 2/3rd of the way through the page.

--
Regards,

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

 

[Mike Middleton]

Stephanie -

Did you look at the LinearTrendSeasonalForecast.xls workbook that I posted
for you at www.mikemiddleton.com ?

The "3 Visual Check Trend" worksheet has a linear trendline, and the "5
Trend and Ratios" worksheet uses the array-entered TREND worksheet function
to obtain the same points in worksheet cells.

- Mike

 

[Guest]

I almost forgot about that. Yes, I went to get the worksheets that you
posted. Thanks so much! This is really helpful.

 

[Guest]

Mike,

I am having trouble with the ^{1,2} part of the Trend equation. I get an
error when I put that in. I know I must be doing something wrong.

Can you help?

Thanks,

Stephanie

 

[Mike Middleton]

Stephanie -

You should use your judgment to decide whether the linear trend or the
quadratic trend is appropriate.

The quadratic trend uses ^{1,2} to obtain both x and x^2 values as
explanatory variables so that the fitted values have some curvature, i.e.,
polynomial of order 2, also called a quadratic.

The TREND function must be array-entered (and a formula that uses ^{1,2}must
be array-entered). To array-enter the function, select the range of cells
that will contain the function results, type the =TREND(...) function with
appropriate arguments but do not press Enter, instead after typing the
closing parentheses hold down Control and Shift keys while you press Enter,
thus "array-entering" the function.

- Mike

 

[Guest]

^{1,2} assumes that the data are in columns. Use ^{1;2} if the data are in
rows.

Jerry