If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
How do I plot a trendline that goes through a specified point?
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 |
#3
|
|||
|
|||
How do I plot a trendline that goes through a specified point?
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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How To Modify One Point In A Graph... | Pierre | Charts and Charting | 3 | November 25th, 2004 10:08 AM |
separate for point and comma | Frank Dulk | Using Forms | 0 | August 3rd, 2004 02:55 AM |
Trendline and its referencing / linking to a cell | MikeG | Charts and Charting | 0 | February 5th, 2004 03:17 PM |
Trendline and its referencing / linking to a cell | Jerry W. Lewis | Charts and Charting | 4 | February 4th, 2004 03:13 AM |
Trendline Not The Same as Equation Displayed | Dan | Charts and Charting | 2 | January 28th, 2004 01:00 AM |