software

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

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

Jessica

quot;MrShortyquot; wrote:

gt;
gt; Probably easier to do using worksheet functions than chart trendlines.
gt; basic approach:
gt;
gt; Start with the equation for a straight line y=mx b.
gt; We want to constrain the equation so that the point (x0,y0) are on the
gt; line. Substitute into the equation: y0=m*x0 b
gt; solve for b: b=y0-m*x0
gt; substitute b into original equation: y=mx y0-m*x0
gt; Associate like terms: y-y0=m*(x-x0) 0 which, if we define new variables
gt; Y=y-y0 and X=x-x0 looks like Y=m*X.
gt; We can now obtain m using the SLOPE function on columns containing y-y0
gt; and x-x0. b is obtained from the above expression for b (b=y0-m*x0).
gt;
gt;
gt; --
gt; MrShorty
gt; ------------------------------------------------------------------------
gt; MrShorty's Profile: www.excelforum.com/member.php...oamp;userid=22181
gt; View this thread: www.excelforum.com/showthread...hreadid=509472
gt;
gt;