Trendline - split one into two

[Richard]

All,

I can't find a way to break a three year/12 qtr trendline apart in Excel
Charts. I'd like to have one trendline for the first two years (8 qtrs) and
one for the last year (4 qtrs). Is this possible?

Thanks,
Richard

 

[Roland]

Yes, plot two data sets, with the same data for for the first 8 quarters, and
differing data after that.

 

[Jon Peltier]

You need to split the series into two, since a trendline applies to the
entire series on which it's based..

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

 

[Richard]

How do I split the series in two and still have the chart look continuous?

 

[Richard]

If I try this is there a way to not display the two sets of data on the
chart? I don't want to have two idential columns for of the first eight
quarters.

 

[Jon Peltier]

You can hide plotted data by formatting the series with no border, no fill, no
lines, no markers. You can hide extra legend entries by selecting the legend, then
the text label of the legend entry (two single clicks), then pressing Delete.

This is not clear. What you need to do is plot this data:

Before After
Q1 10
Q2 15
Q3 20
Q4 27
Q5 31
Q6 36
Q7 40
Q8 47
Q9 58
Q10 70
Q11 81
Q12 90

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

 

[Richard]

Jon,
Ah, thanks, now I'm getting somewhere. One more question - right now I have
two lines intersecting between qtrs 8 and 9. Is there any way to make the
line become one with a kink at the intersection (i.e. exclude the "look back"
trendline based on the final four qtrs and exclude the extrapolated trend
based on the first eight qtrs)?

Thanks,
Richard

 

[Jon Peltier]

You might be better at this point making a custom trendline. Get the
slope and intercept using LINEST or SLOPE and INTERCEPT, determine the
point of intersection, and determine the XY pairs for the lowest end of
the line, the point of intersection, and the highest point on the line.
Put this combined series onto the chart as an XY series, so you can
position the X value of the intersection wherever you want. The
categories are treated as whole numbers, with the first one at X=1.

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