Trendlines in charts with missing data

[Whoop92]

Hi Everbody,
I need to create a chart that shows two data series and trendlines for those series. The two series don’t overlap. One series starts in January and ends in June, while the other starts in July and ends in October. When I add trendlines to the chart the trendline extends beyond the end (or the beginning) of the data

Does anybody have any ideas on how to get the trendlines to start and end with the start and end of each series? In other words, can the trendline for the series that starts in January and ends in June also start in January and end in June?

Thanks in advance!
Whoop92

(A example spreadsheet is hopefully attached.)

[Kaper]

While it is pretty easy to extend trendlines beyond data scope, it's not that easy to "shrink" them.

My proposition is to calculate the trendlines and plot them on a graph as additional series.
So to make life easy:
- list numbers 1... number of your months in one row (say row 5). Always start with 1 even if your data starts in March or September
- calculate slope and intercept for your data so in say M6 and N6:

Please Login or Register  to view this content.

and copy down
in B6 calculated point on trendline:

Please Login or Register  to view this content.

copy right and down (to B6:K7)
add these two series to your graph, format to taste, and ... Enjoy!

[Andy Pope]

You would need to use xy-scatter instead of line chart.