trendline for part of data
 

We have a XY line chart with X scale by time.

We now would like to have a trendline with specific time period.
How do I make a trendline with only part of data are used?

Answer: trendline for part of data
 

To create a trendline for only a specific part of your data in an Excel XY line chart, you can follow these steps:

1. Select the data points that you want to include in the trendline. You can do this by clicking on the first data point, holding down the Shift key, and then clicking on the last data point.
2. Right-click on one of the selected data points and choose "Add Trendline" from the context menu.
3. In the "Format Trendline" pane that appears on the right side of the screen, choose the type of trendline that you want to use (e.g. linear, exponential, etc.).
4. Under the "Series Options" section, select the "X Value" dropdown and choose "Values in reverse order" to ensure that the trendline is plotted correctly.
5. Under the "Series Options" section, select the "Name" dropdown and give your trendline a descriptive name.
6. Under the "Series Options" section, select the "Plot Series On" dropdown and choose "Secondary Axis".
7. Under the "Axis Options" section, select the "Secondary Vertical Axis" dropdown and choose "Show".
8. Under the "Axis Options" section, select the "Minimum" and "Maximum" dropdowns and set the values to match the time period that you want to include in the trendline.
9. Adjust the formatting of your trendline as desired, such as changing the color or thickness.

Your trendline should now be plotted for only the specific part of your data that you selected.

trendline for part of data
 

Is it an XY chart or a line chart? Best regression results come with XY
charts. To get a trendline for a part of the series, add another series to
the chart using just the points of interest.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Terry" wrote in message
...
We have a XY line chart with X scale by time.

We now would like to have a trendline with specific time period.
How do I make a trendline with only part of data are used?

trendline for part of data
 

Jon has a good answer but an alternative is to use functions like TREND,
SLOPE & INTERCEPT, LINEST , LOGEST (depending on what type of 'curve' you
have) to generate the fitting data for the range of interest in another
column. Now add this to the chart as a second data series.
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Terry" wrote in message
...
We have a XY line chart with X scale by time.

We now would like to have a trendline with specific time period.
How do I make a trendline with only part of data are used?

trendline for part of data
 

"Bernard Liengme" wrote in message
...
Jon has a good answer but an alternative is to use functions
like TREND, SLOPE & INTERCEPT, LINEST , LOGEST (depending on
what type of 'curve' you have) to generate the fitting data
for the range of interest in another column. Now add this to
the chart as a second data series.
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Terry" wrote in message
...
We have a XY line chart with X scale by time.

We now would like to have a trendline with specific time
period.
How do I make a trendline with only part of data are used?

A question arises in my mind after Jon and Bernard's replies. Is
there any particular reason for preferring one alternative over
the other? I'm aware from previous posts that some versions of
Excel get better results with the trendline in some special
cases.



--
James Silverton
Potomac, Maryland

trendline for part of data
 

Pre-XL2003 sometimes gave incorrect results with LINEST with data of a
certain type.
Linest is know to give wrong values when intercept is set to 0.
Articles I have read by statisticians in scientific research labs suggest
that most users would never run into the 'odd data' situation. I generally
compare LINEST and trendline results to ensure I do not have the 'odd' data.
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"James Silverton" wrote in message
...
"Bernard Liengme" wrote in message
...
Jon has a good answer but an alternative is to use functions like TREND,
SLOPE & INTERCEPT, LINEST , LOGEST (depending on what type of 'curve' you
have) to generate the fitting data for the range of interest in another
column. Now add this to the chart as a second data series.
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Terry" wrote in message
...
We have a XY line chart with X scale by time.

We now would like to have a trendline with specific time period.
How do I make a trendline with only part of data are used?

A question arises in my mind after Jon and Bernard's replies. Is there any
particular reason for preferring one alternative over the other? I'm aware
from previous posts that some versions of Excel get better results with
the trendline in some special cases.



--
James Silverton
Potomac, Maryland

trendline for part of data
 

Hi James,

Let's suppose that you are asking which of the two methods will be more
useful rather than which one gives better results. If your question is the
first one then consider that if you create a second series using the formula
approach, then you can make the trend line dynamic, that is you could have a
moving trendline that only displays for the last 30 day, for example. This
extra flexibility might be useful. You could also do that with Jon's method
by creating a dynamic range name using the OFFSET function but that is
slightly more complex.


--
Cheers,
Shane Devenshire


"James Silverton" wrote:

"Bernard Liengme" wrote in message
...
Jon has a good answer but an alternative is to use functions
like TREND, SLOPE & INTERCEPT, LINEST , LOGEST (depending on
what type of 'curve' you have) to generate the fitting data
for the range of interest in another column. Now add this to
the chart as a second data series.
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Terry" wrote in message
...
We have a XY line chart with X scale by time.

We now would like to have a trendline with specific time
period.
How do I make a trendline with only part of data are used?

A question arises in my mind after Jon and Bernard's replies. Is
there any particular reason for preferring one alternative over
the other? I'm aware from previous posts that some versions of
Excel get better results with the trendline in some special
cases.



--
James Silverton
Potomac, Maryland