How to get two Trendlines to start and stop at two different intervals on a chart

I

ibddude

New Member
Joined
Aug 20, 2011
Messages
17
All;

I researched and trialed different options on this for awhile, I am not a charting expert but what I'm trying to do is chart two separate trendlines for two separate time intervals but only show (and have the trendline effected by the data of that interval. Below is the data and where I at least have the first trendline correctly shown but the second one which is later in time (x-Axis) projects the trendline back to 0. Any ideas on how I get the second trenline to start at 2017-Q1 would be very appreciated! P.S. The $118K is an anomaly so while I want to show it on the X axis for the time interval, I don't want to include it into the Model A trendline.

Thanks
Mike

[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 69"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Year[/TD]
[/TR]
[TR]
[TD]2013-Q1[/TD]
[/TR]
[TR]
[TD]2013-Q2[/TD]
[/TR]
[TR]
[TD]2013-Q3[/TD]
[/TR]
[TR]
[TD]2013-Q4[/TD]
[/TR]
[TR]
[TD]2014-Q1[/TD]
[/TR]
[TR]
[TD]2014-Q2[/TD]
[/TR]
[TR]
[TD]2014-Q3[/TD]
[/TR]
[TR]
[TD]2014-Q4[/TD]
[/TR]
[TR]
[TD]2015-Q1[/TD]
[/TR]
[TR]
[TD]2015-Q2[/TD]
[/TR]
[TR]
[TD]2015-Q3[/TD]
[/TR]
[TR]
[TD]2015-Q4[/TD]
[/TR]
[TR]
[TD]2016-Q1[/TD]
[/TR]
[TR]
[TD]2016-Q2[/TD]
[/TR]
[TR]
[TD]2016-Q3[/TD]
[/TR]
[TR]
[TD]2016-Q4[/TD]
[/TR]
[TR]
[TD]2017-Q1[/TD]
[/TR]
[TR]
[TD]2017-Q2[/TD]
[/TR]
[TR]
[TD]2017-Q3[/TD]
[/TR]
[TR]
[TD]2017-Q4[/TD]
[/TR]
[TR]
[TD]2018-Q1[/TD]
[/TR]
[TR]
[TD]2018-Q2[/TD]
[/TR]
[TR]
[TD]2018-Q3[/TD]
[/TR]
[TR]
[TD]2018-Q4[/TD]
[/TR]
[TR]
[TD]2019-Q1[/TD]
[/TR]
[TR]
[TD]2019-Q2[/TD]
[/TR]
[TR]
[TD]2019-Q3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 109"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Quarterly Billing[/TD]
[/TR]
[TR]
[TD="align: right"]$118,447[/TD]
[/TR]
[TR]
[TD="align: right"]$409,385[/TD]
[/TR]
[TR]
[TD="align: right"]$422,761[/TD]
[/TR]
[TR]
[TD="align: right"]$440,817[/TD]
[/TR]
[TR]
[TD="align: right"]$488,149[/TD]
[/TR]
[TR]
[TD="align: right"]$557,512[/TD]
[/TR]
[TR]
[TD="align: right"]$558,138[/TD]
[/TR]
[TR]
[TD="align: right"]$664,555[/TD]
[/TR]
[TR]
[TD="align: right"]$752,812[/TD]
[/TR]
[TR]
[TD="align: right"]$761,219[/TD]
[/TR]
[TR]
[TD="align: right"]$600,405[/TD]
[/TR]
[TR]
[TD="align: right"]$732,650[/TD]
[/TR]
[TR]
[TD="align: right"]$751,872[/TD]
[/TR]
[TR]
[TD="align: right"]$838,705[/TD]
[/TR]
[TR]
[TD="align: right"]$806,192[/TD]
[/TR]
[TR]
[TD="align: right"]$820,687[/TD]
[/TR]
[TR]
[TD="align: right"]$614,904[/TD]
[/TR]
[TR]
[TD="align: right"]$712,844[/TD]
[/TR]
[TR]
[TD="align: right"]$708,267[/TD]
[/TR]
[TR]
[TD="align: right"]$697,713[/TD]
[/TR]
[TR]
[TD="align: right"]$665,093[/TD]
[/TR]
[TR]
[TD="align: right"]$617,945[/TD]
[/TR]
[TR]
[TD="align: right"]$616,063[/TD]
[/TR]
[TR]
[TD="align: right"]$696,549[/TD]
[/TR]
[TR]
[TD="align: right"]$678,059[/TD]
[/TR]
[TR]
[TD="align: right"]$614,899[/TD]
[/TR]
[TR]
[TD="align: right"]$627,303[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 71"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Model A[/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]409,385[/TD]
[/TR]
[TR]
[TD="align: right"]422,761[/TD]
[/TR]
[TR]
[TD="align: right"]440,817[/TD]
[/TR]
[TR]
[TD="align: right"]488,149[/TD]
[/TR]
[TR]
[TD="align: right"]557,512[/TD]
[/TR]
[TR]
[TD="align: right"]558,138[/TD]
[/TR]
[TR]
[TD="align: right"]664,555[/TD]
[/TR]
[TR]
[TD="align: right"]752,812[/TD]
[/TR]
[TR]
[TD="align: right"]761,219[/TD]
[/TR]
[TR]
[TD="align: right"]600,405[/TD]
[/TR]
[TR]
[TD="align: right"]732,650[/TD]
[/TR]
[TR]
[TD="align: right"]751,872[/TD]
[/TR]
[TR]
[TD="align: right"]838,705[/TD]
[/TR]
[TR]
[TD="align: right"]806,192[/TD]
[/TR]
[TR]
[TD="align: right"]820,687[/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 71"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Model B[/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]614,904[/TD]
[/TR]
[TR]
[TD="align: right"]712,844[/TD]
[/TR]
[TR]
[TD="align: right"]708,267[/TD]
[/TR]
[TR]
[TD="align: right"]697,713[/TD]
[/TR]
[TR]
[TD="align: right"]665,093[/TD]
[/TR]
[TR]
[TD="align: right"]617,945[/TD]
[/TR]
[TR]
[TD="align: right"]616,063[/TD]
[/TR]
[TR]
[TD="align: right"]696,549[/TD]
[/TR]
[TR]
[TD="align: right"]678,059[/TD]
[/TR]
[TR]
[TD="align: right"]614,899[/TD]
[/TR]
[TR]
[TD="align: right"]627,303[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Sort by date Sort by votes
Option 1
Put =NA() formula in the 2013-Q1 through 2016-Q4 cells for Model B (D2:D17)

Option 2
Perhaps the second trendline could be simulated by calculating its origin and slope then plotting that line on the graph. The line could be redrawn by event trigger whenever a new point was added.
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question Question
Color duplicates rows in two different sets of data and also the rows which are present in both sets of data.
Replies
0
Views
470
Noob_Excel
N
T
  • Question Question
Using a dynamic formula to calculate value of reinvested dividends over changing time periods
Replies
2
Views
3K
Tuckbox
T
B
  • Question Question
Count number of months per year between two dates
Replies
1
Views
1K
DanteAmor
DanteAmor
F
  • Question Question
looking for help with a formula...
Replies
2
Views
590
Vinoth_Shob
V
C
  • Question Question
Finding Percentage Drop
Replies
5
Views
879
DanteAmor
DanteAmor

Forum statistics

Threads
1,224,818
Messages
6,181,152
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top