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

[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.

 

[MrShorty]

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).

 

[Guest]

Thats wonderful, thank you so much. I can't believe I didn't think of that
before!
Thanks again

Jessica