Extracting trendline equation to a worksheet
Lory Chalfoun
polynomial trendline. The equation for the trendline is automatically
presented on the chart. I then manually copy the trendline equation "y=...."
and paste it into a worksheet cell. The spreadsheet then uses that equation
to generate other information in the worksheet. Every time I change the
chart data, a new trenline is automatically generated but then I have to
manually copy and paste again. This manual operation gets tedious because I
have to do this for many sets of data.
Does anyone know how I can get the trenline equation to be automatically
assigned into the worksheet cell without using a macro or VBA?
Okey Laboratory
trendline is just a non-linear regression of x on y so I think you can do
that with worksheet functions instead.
hth,
Tats
"Lory Chalfoun" <lo...@generation.net> wrote in message
news:v5r1ms7...@corp.supernews.com...
Lory Chalfoun
equation of the trendline. The problem is that I am generating a 6th degree
polynomial trendline. I may be wrong, but I don't think there is a worksheet
function that can do this. Is there an add-in that will allow me to do this?
I'm not too familiar with macros, but if their is really no other way, I
guess I will have to spend some time to learn about them. I want to avoid
VBA because I know absolutely nothing about it.
Thanks again.
Okey Laboratory <N/A> wrote in message
news:u24AGSh3...@TK2MSFTNGP11.phx.gbl...
Okey Laboratory
can do for you. The problem you have is called "General Linear Regression"
and I was sure Excel could do that for quadratics and cubics. I could be
wrong, but I think it can be done!
Good luck,
Tats
"Lory Chalfoun" <lo...@generation.net> wrote in message
news:v5r67qd...@corp.supernews.com...
Jerry W. Lewis
be fit by LINEST (e.g.
=LINEST(ydata,xdata^{1,2,3})
for a cubic).
The numerics underlying the XY Chart are far more accurate on
numerically difficult problems than LINEST, so if you see any
discrepancies between the trendline equation and LINEST, then go back to
copying the coefficients from the chart (formatted to scientific with 14
decimal places to avoid rounding)
For a package that is so notoriously bad numerically for probability and
statistics functions the chart trendline is amazingly good. It is much
better numerically than SAS and slightly better numerically than R and
S. Minitab is the only program I have tested that is (barely) better
numerically than Excel's polynomial trendline (Origin 7 may also be, but
I have not been able to get it to display enough figures to tell.
The obvious questions are:
- Why did MS program 2 different solutions for the same problem?
- Why don't they replace the lousy solutions with the better ones that
already exist in the package?
Jerry
David J. Braden
"Jerry W. Lewis" <post_a_reply@no_e-mail.com> wrote:
<<snip>>
> The obvious questions are:
> - Why did MS program 2 different solutions for the same problem?
LOL. Why did MS program *at least 2* solutions to so many problems?!?
Basic uniform(0,1) variate generation is done, poorly, in three
different ways (at least) within Excel.
> - Why don't they replace the lousy solutions with the better ones that
> already exist in the package?
Now you are getting a little "Zen" on us, my friend.
Regards,
Dave Braden
--
ROT13 e-mail: qoe...@ebpurfgre.ee.pbz
David J. Braden
There is indeed a way to do this with worksheet functions, and I suggest
you avoid the ATP for this; it simply uses LINEST, but in batch mode,
leaving you with a static set of numbers that will never be as goos as
what you can get off of the chart's trendline.
Use what Jerry posted to get you started with LINEST, if you choose. But
please consider what he is telling you: the Chart trendline is
top-notch. You can use a VBA routine to extract the trendline info back
into your cells. Why settle for less than the best, especially when you
have folks here that will walk you through it?
FWIW, Jerry has a PhD in statistics. IMHO, no one in these groups knows
the area better than he. You lucked out.
Regards,
Dave Braden
Microsoft MVP
In article <v5r67qd...@corp.supernews.com>,
"Lory Chalfoun" <lo...@generation.net> wrote:
--
ROT13 e-mail: qoe...@ebpurfgre.ee.pbz
Lory Chalfoun
when I have a little time on my hands I will program my own spreadsheet to
solve for the 7 constants in the 6 degree polynomial trendline. I learned
how least square fit polynomials can be solved with simple matrix equations.
The hard part is generating all the values in a linear system of equations
from the data points. You end up with 7 linear equations and 7 unknown.
Thanks again to all
Lory
Lory Chalfoun <lo...@generation.net> wrote in message
news:v5r1ms7...@corp.supernews.com...
John F. Collins
> equation of the trendline. The problem is that I am generating a 6th
degree
> polynomial trendline.
Is that ever a good idea? :-)
Anyway, as others have pointed out, you can get the trendline equation in a
VBA macro. For example you could put a button or menu item in your workbook
that would automatically put the equation coefficients into the worksheet
where you wanted. You could probably even set it up so that whenever the
trendline changed due to addition of data, it would automatically update the
coefficients in your worksheet.
It's a little messy because the equation returned from the chart is a text
string. You need to parse the string to get the coefficients. If you want
VBA solutions, ask and you shall probably receive.
Jerry W. Lewis
simply be reproducing the LINEST calculations with all their numerical
problems.
If you want a numerically stable least squares routine, it cannot
involve computing X'X. You might find Kennedy and Gentle's book
"Statistical Computing" or Lawson and Hanson's book "Solving Least
Squares Problems" helpful.
You could get accuracy comparable to R (not quite as good as Excel's
chart trendline) using Algorithms 75 and 274 from Applied Statistics
(Journal of the Royal Statistical Society, Series C) 23:448-454,1974,
25:323-324,1975, 31:340,1982, 41:458-478,1992, and 43:678,1994. Fortran
code is available on-line at
http://lib.stat.cmu.edu/apstat/75
http://lib.stat.cmu.edu/apstat/274
Alternately, you could download R (free and open source) from
If you are clever, you might be able to pass the data to the R dll from
Excel and return the coefficients back from R to Excel.
Jerry
Jerry W. Lewis
simply be reproducing the LINEST calculations with all their numerical
problems.
If you want a numerically stable least squares routine, it cannot
involve computing X'X. You might find Kennedy and Gentle's book
"Statistical Computing" or Lawson and Hanson's book "Solving Least
Squares Problems" helpful.
You could get accuracy comparable to R (not quite as good as Excel's
chart trendline) using Algorithms 75 and 274 from Applied Statistics
(Journal of the Royal Statistical Society, Series C) 23:448-454,1974,
25:323-324,1975, 31:340,1982, 41:458-478,1992, and 43:678,1994. Fortran
code is available on-line at
http://lib.stat.cmu.edu/apstat/75
http://lib.stat.cmu.edu/apstat/274
Alternately, you could download R (free and open source) from
If you are clever, you might be able to pass the data to the R dll from
Excel and return the coefficients back from R to Excel.
You should consider whether you might be overfitting your data.
Jerry