Trendline - split one into two

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

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

"Richard" wrote:

> 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

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 wrote:

> 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

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

"Jon Peltier" wrote:

> 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 wrote:
>
> > 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
>

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.

"Roland" wrote:

> Yes, plot two data sets, with the same data for for the first 8 quarters, and
> differing data after that.
>
> "Richard" wrote:
>
> > 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

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.

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

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 wrote:

> 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.
>
> "Roland" wrote:
>
>
>>Yes, plot two data sets, with the same data for for the first 8 quarters, and
>>differing data after that.
>>
>>"Richard" wrote:
>>
>>
>>>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

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" wrote:

> 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.
>
> >>Yes, plot two data sets, with the same data for for the first 8 quarters, and
> >>differing data after that.
>
> 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 wrote:
>
> > 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.
> >
> > "Roland" wrote:
> >
> >
> >>Yes, plot two data sets, with the same data for for the first 8 quarters, and
> >>differing data after that.
> >>
> >>"Richard" wrote:
> >>
> >>
> >>>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
>
>

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/
_______


Richard wrote:
> 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" wrote:
>
>
>>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.
>>
>> >>Yes, plot two data sets, with the same data for for the first 8 quarters, and
>> >>differing data after that.
>>
>>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 wrote:
>>
>>
>>>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.
>>>
>>>"Roland" wrote:
>>>
>>>
>>>
>>>>Yes, plot two data sets, with the same data for for the first 8 quarters, and
>>>>differing data after that.
>>>>
>>>>"Richard" wrote:
>>>>
>>>>
>>>>
>>>>>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
>>
>>