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
|
|||
|
|||
trendline equations
I am having a dual problem:
1. On a scatter graph I can't get the trendline equation to update when the trendline itself moves (ie I am using the worksheet as a template for a complex calculation so each time I do it, the data the trendline is based on has been changed, the trendline moves but the equations stays the same - I have to delete it and reapply it to get it to update). 2. I am using the trendline equation to calculate values for other points that lie on the trendline. To do this I am using the formula for the trendline and want a way to get it off the chart and into a cell without having to type it in manually - I want the template essentially totally automated to reduce human error. Anyone got any ides? Thanks heaps, Kathy |
#2
|
|||
|
|||
trendline equations
Hi Kathy,
The behaviour you describe in 1 is very odd. The equation is linked to the trendline and both are dynamic - change the data and the trendline and its equation both alter. Does this happen even if you do not save the workbook as a template? I cannot reproduce this in Excel 2002 or Excel 2003 Beta. The answer to 2 is not so hard. Is it a linear trendline? If so you the SLOPE and INTERCEPT functions. If it is polynomial (or linear with fixed intercept) use LINEST. See www.stfx.ca/people/bliengme/Excelips on using LINEST to fit a polynomial. There is also LOGEST for exponential fitting. Tell us what type you are using. Best wishes Bernard "kathy" wrote in message ... I am having a dual problem: 1. On a scatter graph I can't get the trendline equation to update when the trendline itself moves (ie I am using the worksheet as a template for a complex calculation so each time I do it, the data the trendline is based on has been changed, the trendline moves but the equations stays the same - I have to delete it and reapply it to get it to update). 2. I am using the trendline equation to calculate values for other points that lie on the trendline. To do this I am using the formula for the trendline and want a way to get it off the chart and into a cell without having to type it in manually - I want the template essentially totally automated to reduce human error. Anyone got any ides? Thanks heaps, Kathy |
#3
|
|||
|
|||
trendline equations
Here's a list of formulas that will generate trendlines:
http://j-walk.com/ss/excel/tips/tip101.htm John Walkenbach For Excel tips, macros, & downloads... http://j-walk.com/ss "kathy" wrote in message ... I am having a dual problem: 1. On a scatter graph I can't get the trendline equation to update when the trendline itself moves (ie I am using the worksheet as a template for a complex calculation so each time I do it, the data the trendline is based on has been changed, the trendline moves but the equations stays the same - I have to delete it and reapply it to get it to update). 2. I am using the trendline equation to calculate values for other points that lie on the trendline. To do this I am using the formula for the trendline and want a way to get it off the chart and into a cell without having to type it in manually - I want the template essentially totally automated to reduce human error. Anyone got any ides? Thanks heaps, Kathy |
#4
|
|||
|
|||
trendline equations
Others have already given formulas based on the Excel functions SLOPE,
LINEST, etc. These functions can run into severe numerical difficulties with some data sets. http://groups.google.com/groups?selm...40mediaone.net lists numerically better formulas for linear regression. avid Braden has posted a function that will extract the coefficients from the chart trendline for use in workbooks, which is numerically better than LINEST for polynomials, etc. http://groups.google.com/groups?selm....microsoft.com Jerry kathy wrote: I am having a dual problem: 1. On a scatter graph I can't get the trendline equation to update when the trendline itself moves (ie I am using the worksheet as a template for a complex calculation so each time I do it, the data the trendline is based on has been changed, the trendline moves but the equations stays the same - I have to delete it and reapply it to get it to update). 2. I am using the trendline equation to calculate values for other points that lie on the trendline. To do this I am using the formula for the trendline and want a way to get it off the chart and into a cell without having to type it in manually - I want the template essentially totally automated to reduce human error. Anyone got any ides? Thanks heaps, Kathy |
#5
|
|||
|
|||
trendline equations
That's a handy page.
- Jon John Walkenbach wrote: Here's a list of formulas that will generate trendlines: http://j-walk.com/ss/excel/tips/tip101.htm John Walkenbach For Excel tips, macros, & downloads... http://j-walk.com/ss "kathy" wrote in message ... I am having a dual problem: 1. On a scatter graph I can't get the trendline equation to update when the trendline itself moves (ie I am using the worksheet as a template for a complex calculation so each time I do it, the data the trendline is based on has been changed, the trendline moves but the equations stays the same - I have to delete it and reapply it to get it to update). 2. I am using the trendline equation to calculate values for other points that lie on the trendline. To do this I am using the formula for the trendline and want a way to get it off the chart and into a cell without having to type it in manually - I want the template essentially totally automated to reduce human error. Anyone got any ides? Thanks heaps, Kathy |
#6
|
|||
|
|||
trendline equations
Thanks heaps for your help. It ended up that the equation
wasn't changing because I was an idiot and changed the font making the chart think that it was now a textbox and so wasn't updating the data. Great! Thanks again! Kathy |
Thread Tools | |
Display Modes | |
|
|