Excel 2010 - Auto-Adjusting Trendline As Slope Changes

[MeGustaXL]

Hi

I plot trend data, either on log-log scales or linear graphs, and apply trendlines to the plot(s).

Quite often, the plots have sharp doglegs and up- or down-ticks. When I see these, I split the data at that point, and make the chart into two (or more) series, each with a separate trendline. Not a problem, and even therapeutic (for me) :-D

I wondered if there is some built-in or programmatic way to do this automatically? For example, if the slope goes up or down by 50%, start a new trendline.

Chris

Someday I'll know what I'm donig...damn!

 

[Deniall]

I doubt there will be any built-in way to do this.

[In the "none of my business but… …" department.[ ] It worries me that you seem to be making arbitrary, cosmetically determined, decisions about your data stream.[ ] Shouldn't you leave it alone unless you have a priori reasons to suspect a discontinuity at that particular point?]

 

[MeGustaXL]

Hi Deniall,
I think you're right, there's no built in way to do it.
I'm not actually making arbitrary decisions. I use what Larry Crow calls the "Intra-Ocular Impact Test". Look at the plot; any significant change in direction should hit you between the eyes, and you should investigate. "What happened in July?" you might say, or "Why did the failure rate shoot up when we moved to Montana?" Spotting those elbows and making a second plot from the first shows the Senior Management that your suggested improvement has saved the Corporation £15Million, or the weather in Witchita plays havoc with the Doohicky Seals.

Chris

Someday I'll know what I'm donig...damn!

 

[Deniall]

All is fine then.

"Intra-Ocular Impact Test".[ ] ¡Lo me gusta![ ] A wonderful expression, which I'll add to my repertoire.[ ] Thanks.

 

[SkipVought]

I wondered if there is some built-in or programmatic way to do this automatically? For example, if the slope goes up or down by 50%, start a new trendline.

Delta 50%: for how many points? 2?

Delta 50%: compared to how many points? 2? Or current trend line?

You have to define the logic in the IOIT.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[combo]

Concerning trend analysis, when you add trendline to excel, you can display its R[sup]2[/sup], a measure of fitting quality. Instead of manually adjusting data range for trendline you can try (if it's linear) calculate slope of recent n points and draw it as another series.

combo

 

[MeGustaXL]

Hi Guys, and thanks for everyone's input

Combo, that's another thing I've tried, sort of. When I see a dogleg in the plot, I calculate the slope and intercept from the cusp to the end of the data, or to the next cusp if there is one, and use them to calculate a new series, which I plot as a straight (trend)line. I might extrapolate this line out a couple of periods for a forecast, but only if it fits well, ie with an R-squared of 0.95 or better. This lets me have several 'trendlines' on a particularly zigzaggy chart. Management like this view, especially if the plot markers change shape and/or colour with each 'new' trend.

That's the bit that needs another series/snapshot of the data. So if I do that, I might as well stick a trendline on it.

Chris

Someday I'll know what I'm donig...damn!