A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Charts and Charting
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

trendline equations



 
 
Thread Tools Display Modes
  #1  
Old September 17th, 2003, 06:21 PM
kathy
external usenet poster
 
Posts: n/a
Default 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  
Old September 17th, 2003, 07:55 PM
Bernard V Liengme
external usenet poster
 
Posts: n/a
Default 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  
Old September 17th, 2003, 09:33 PM
John Walkenbach
external usenet poster
 
Posts: n/a
Default 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  
Old September 17th, 2003, 10:11 PM
Jerry W. Lewis
external usenet poster
 
Posts: n/a
Default 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  
Old September 18th, 2003, 01:13 PM
Jon Peltier
external usenet poster
 
Posts: n/a
Default 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  
Old October 2nd, 2003, 10:12 PM
Kathy
external usenet poster
 
Posts: n/a
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 04:39 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.