Multiple Trend Lines for Different Segments of the Same Series in Excel

2

So I have a set of data in Excel that follows a periodic (roughly sawtooth shaped) pattern over time. That is, it increases gradually over a long period of time, and suddenly drops back down to a minimum before beginning to gradually increase again.

Image 1

For each of the up-sloping segments of this data, I would like to plot a second trend line. I have tried splitting the data into multiple series, but because I'm plotting it as a line graph I end up with two sets of data both starting from my "zero" time, and I don't seem to be able to change this by editing the horizontal axis, because it changes for the entire graph, not the individual series.

Image2

I have also tried switching to a scatter plot, but this leaves me with a discontinuity between the data sets (where the vertical section would be on a sawtooth function). I could fix this by duplicating a data point between the segments, but the table, in this case, is also going to be presented and needs to look good.

Image3

If nobody knows of a better way, I'll have to keep two tables (one that's presentable, and one that makes the graph work) and switch to scatter plot, but it would be nice to not have to do all of this to make it work.

realityChemist

Posted 2015-09-02T13:54:19.277

Reputation: 272

Without an example to look at (if you have one, post it to a filesharing site and someone will probably edit into your question for you), I'm guessing that you'll need to use an XY scatter plot chart type, and use Excel's functions to calculate your trend lines (as opposed to the chart shortcut option). This will allow you to determine which areas need to be represented by each trendline. FWIW, it sounds like you have some seasonality issue that may need to be identified/documented to help explain your data. – dav – 2015-09-02T15:26:23.110

I know why the data has these sharp discontinuities. It's a tank of water that is slowly rising in pH over time as the level drops. The sudden drop is when the tank is refilled with freshly distilled water, setting the pH back to it's original low level. I haven't established cause yet, but that's beside the point. If I need to use X-Y scatter, I'm going to have to maintain two copies of the table, to fix a slight issue that arises when switching to X-Y scatter. I'll attach example images of what I'm talking about. – realityChemist – 2015-09-02T15:35:27.453

Ahh, I see, my rep isn't high enough to add images. Okay, here are the links generated by uploading the images. Image 1 is what my current graph looks like, with the green line being a known constant; image 2 is what happens if you try the naive approach of just making two series (X-axis is not per-series on line plot); image 3 shows the discontinuity that would require a second table to cure when using X-Y scatter. [1]: http://i.stack.imgur.com/Mt1zT.png [2]: http://i.stack.imgur.com/N08B8.png [3]: http://i.stack.imgur.com/fiGr9.png

– realityChemist – 2015-09-02T15:46:07.443

I added the images-you may want to adjust their location or text around them. I'll also take a look at your problem and see if I can offer any suggestions. – dav – 2015-09-02T16:43:51.967

Answers

2

To get the different lines with different dates onto the same line chart, put all the dates for both lines into one column, the data for the first line into the second column, and the data for the second line into the third column. Plot using these three columns.

enter image description here

It's generally a good idea to use different ranges for data to be presented in tabular form and for data to be plotted (and also for data to be used for various additional calculations), because formatting or layout required for one purpose is guaranteed to interfere with another. Rather than bust your head trying to use a poorly arranged set of data for the wrong purpose, it's better to have multiple data ranges. Link them to an original source range, of course, so they all show the same numbers.

So you could present the data next to the chart, and keep the chart data source off to the side somewhere. It can be further than column K, even on another sheet.

enter image description here

Jon Peltier

Posted 2015-09-02T13:54:19.277

Reputation: 3 470

Asked: 2015-09-02T13:54:19.277

Viewed: 21 286 times

Active: 2016-12-08T11:41:43.987