How do I anchor one end of a linear trend line?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to display a linear trend line to project an end point at a future
time. I have a fixed starting point and want a linear extrapolation from
that point. The standard linear trend line moves the start point to give a
straight line fit to the data, but that's not what I am trying to do.
 
Utf-8?B?V2lsbGlhbSBTcHVybG9jaw==?=" <William
(e-mail address removed)> says...
I want to display a linear trend line to project an end point at a future
time. I have a fixed starting point and want a linear extrapolation from
that point. The standard linear trend line moves the start point to give a
straight line fit to the data, but that's not what I am trying to do.
Click to expand...
To specify a particular intercept, double-click the trendline, then
select the Options tab. The rest should be straightforward.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
If you don't want to constrain the intercept (the Y value at X=0) but
instead want to extend the trendline beyond the first or last X values, on
the trendline Options tab, change the Forward and Backward values in the
Forecast box.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Hi,

If you want to the force the trendline ALWAYS pass through a fixed starting
point (I assume that this is not the y-intercept but the first data point),
you could try the following approach.

Supposing that your X-range is in A2:A11 and Y-range is in B2:B11, create a
helper column C with the following formula in C2 and autofill to C11.

=$B$2+LINEST($B$2:$B$11-$B$2,$A$2:$A$11-$A$2,0)*(A2-$A$2)

Add a second series to your XY-scatter plot with the A2:A11 as the X-range
and C2:C11 as the Y-range, format the series for "Automatic" for Line and
"None" for Marker. Now get the trendline equation for Series 2 (not your
original series). The trendline will overlap with the second series, and
hence Series 2 will be indistinguishable.

Regards,
B. R. Ramachandran
 
Back
Top