How do I plot a trendline that goes through a specified point?

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

Guest

I have a scatter graph and want to plot a trendline that goes through a
specified point first, then to adjust according to the rest of the data. I
also want it to pass the y-axis (unknown point) to be read off the graph. The
trendline must be linear.
 
Probably easier to do using worksheet functions than chart trendlines.
basic approach:

Start with the equation for a straight line y=mx+b.
We want to constrain the equation so that the point (x0,y0) are on the
line. Substitute into the equation: y0=m*x0+b
solve for b: b=y0-m*x0
substitute b into original equation: y=mx+y0-m*x0
Associate like terms: y-y0=m*(x-x0)+0 which, if we define new variables
Y=y-y0 and X=x-x0 looks like Y=m*X.
We can now obtain m using the SLOPE function on columns containing y-y0
and x-x0. b is obtained from the above expression for b (b=y0-m*x0).
 
Thats wonderful, thank you so much. I can't believe I didn't think of that
before!
Thanks again

Jessica
 
Back
Top