Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 193
Default 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?
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up 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.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 6,582
Default 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?



  #4   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 4,393
Default 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?



  #5   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 182
Default 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



  #6   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 4,393
Default 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



  #7   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 2,344
Default 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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Manual control of link updating for downloaded quotes? dk_ Excel Discussion (Misc queries) 9 November 15th 06 02:04 PM
formula to identify exact point where trendline intersects data Jane Excel Worksheet Functions 5 November 15th 06 12:09 AM
macro unouwanme Excel Discussion (Misc queries) 9 August 31st 06 09:38 PM
Macro question Chris Excel Worksheet Functions 12 July 7th 06 01:23 AM
Printing data validation scenarios SJC Excel Worksheet Functions 14 July 24th 05 12:43 AM


All times are GMT +1. The time now is 09:00 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"