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

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

"MrShorty" wrote:

>
> 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).
>
>
> --
> MrShorty
> ------------------------------------------------------------------------
> MrShorty's Profile: http://www.excelforum.com/member.php...o&userid=22181
> View this thread: http://www.excelforum.com/showthread...hreadid=509472
>
>