Suppressing part of a graphical trendline

DavidB

Active Member
Joined
Oct 21, 2002
Messages
253
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
Good morning

We have a 13 month time series of data and want to show the linear trend of the most recent 6 months only on the graph.

How do we suppress the trend line going over the first 7 months data?"

Thanks DavidB
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

jc113883

Board Regular
Joined
May 29, 2006
Messages
165
I don't think the trendline can be supressed for sections of data.

Easiest way would be to plot another series on the chart, of only the last 6 months worth of data and put a trendline on that. You can then hide the data points so they can't be seen. You can delete the entry in the legend if required.
 

DavidB

Active Member
Joined
Oct 21, 2002
Messages
253
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
Thanks jc113883, you comments set me down a path to a solution. I learnt a few interesting things along the way.

1) Trendlines can be extrapolated beyond the left and right end of the data but can not be shrunk shorter than the data series. (We wanted the trendline to represent and show only the last 6 month's data.)

2) The NA() function can be used to suppress values from displaying on the chart.

Essentially, what I did was:

1) Used the COUNTA() function to determine the total number of datapoints and hence the location of the last data points.

2) Used the LINEST() and OFFSET() functions to determine the slope and intercept parameters for the trendline describing the last 6 datapoints.

3) From the LINEST() function I generated best fit data points for the six last data values and plotted these values. Plotting outside the range of the last 6 data points was suppressed using the IF() and NA() functions. These datapoints I charted.

Regards David
 

Watch MrExcel Video

Forum statistics

Threads
1,122,564
Messages
5,596,878
Members
414,106
Latest member
Tigretto

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
Top