I need to process a large amount of data. I would like to automatically
insert the equations for third order polynominal trendlines based on my data
into a cell so I can further use the data. I know I can fit the line, copy
the equation, paste into a cell, and then modify so it is a working equation,
but for the amount of data I have this is absurd.

I am slightly familiar with macros, but I have never written my own.

There must be an easier way

Thanks!

Hi Suzy,
I was working on a similar problem, but it did not have to do with a volume
problem, as much as it had to do with a 6th degree polynomial. It appeared
that the precision of 15 decimals places would not allow finding the "data"
points well enough. So I would like to see how this goes for you.

Assuming your data has not exceeded the 65000 row limit inherant in Excel,
there sould not be a reason a formula can not be copied either up or down,
depending your data layout. I believe the formula will have to be
"anchoraged" at either the top or bottom of your data range. I am not certain
that the formula yields specific "new" data points, which allow you to graph
the trend line, with out using the built in trend line in Excel, which is
what I was attempting to accomplish.

I had attemted to go to several math web sites to solve the 15 decimal place
precision problem, but failed to solve the problem. That had more to do with
the 6th degree. But I assume you are trying to accomplish something similar,
but for a 3rd degree poly.

How much data are you actually dealing with? Does it exceed 65000?
--
David


"Suzy" wrote:

I need to process a large amount of data. I would like to automatically
insert the equations for third order polynominal trendlines based on my data
into a cell so I can further use the data. I know I can fit the line, copy
the equation, paste into a cell, and then modify so it is a working equation,
but for the amount of data I have this is absurd.

I am slightly familiar with macros, but I have never written my own.

There must be an easier way

Thanks!

Have you looked into the LINEST worksheet function?


--
MrShorty
------------------------------------------------------------------------
MrShorty's Profile: http://www.excelforum.com/member.php...o&userid=22181
View this thread: http://www.excelforum.com/showthread...hreadid=487317

I'm not sure I explained my situation clearly enough. My data doesn't exceed
the excel limit, but I have about 50 worksheets of similar data with a matrix
of about 45 by 550, so its more of a time issue. Heres how I would solve my
problem if I had lots of time . .

I would plot each column, fit a 3rd degree polynominal and display the
resulting equation, copy the trendline equation from the plot, paste into a
cell, modify the equation (replace cell names for "x", erase spaces, etc) and
then use this result.

I am interested in if there is a way to skip the steps where I make a plot
for each column, fit a polynominal, and then copy that equation. For now I
have used the TREND function and just fit two separate linear lines (for each
column), but a 3rd degree polynominal would suit me better.

Thanks,
Suzy

"David" wrote:

Hi Suzy,
I was working on a similar problem, but it did not have to do with a volume
problem, as much as it had to do with a 6th degree polynomial. It appeared
that the precision of 15 decimals places would not allow finding the "data"
points well enough. So I would like to see how this goes for you.

Assuming your data has not exceeded the 65000 row limit inherant in Excel,
there sould not be a reason a formula can not be copied either up or down,
depending your data layout. I believe the formula will have to be
"anchoraged" at either the top or bottom of your data range. I am not certain
that the formula yields specific "new" data points, which allow you to graph
the trend line, with out using the built in trend line in Excel, which is
what I was attempting to accomplish.

I had attemted to go to several math web sites to solve the 15 decimal place
precision problem, but failed to solve the problem. That had more to do with
the 6th degree. But I assume you are trying to accomplish something similar,
but for a 3rd degree poly.

How much data are you actually dealing with? Does it exceed 65000?
--
David


"Suzy" wrote:

I need to process a large amount of data. I would like to automatically
insert the equations for third order polynominal trendlines based on my data
into a cell so I can further use the data. I know I can fit the line, copy
the equation, paste into a cell, and then modify so it is a working equation,
but for the amount of data I have this is absurd.

I am slightly familiar with macros, but I have never written my own.

There must be an easier way

Thanks!

LINEST and TREND can handle 3rd degree polynomials. Something like
LINEST(knownys,knownxs^{1,2,3},true). Or is there something else about
these functions that makes them unsuitable for your use?


--
MrShorty
------------------------------------------------------------------------
MrShorty's Profile: http://www.excelforum.com/member.php...o&userid=22181
View this thread: http://www.excelforum.com/showthread...hreadid=487317

If it helps, review
http://www.tushar-mehta.com/excel/ti...efficients.htm


--
MrShorty
------------------------------------------------------------------------
MrShorty's Profile: http://www.excelforum.com/member.php...o&userid=22181
View this thread: http://www.excelforum.com/showthread...hreadid=487317

Hi Suzy,
It is a little difficult to generalize to your data, but this may help. The
data I used was for a stock closing for specific dates (Column A - Data Label
"Date" - Range A1 to A358) and (Column B - Data Label "Adj. Close" - Range B1
to B358)

The trend line is added and the RSquared formula is added to the chart. The
formula is copied and put on the worksheet in cell C2. I am not sure how to
edit the formula, so there is only the formula directly off the chart, it
does not calculate.

Thanks
--
David


"Suzy" wrote:

I'm not sure I explained my situation clearly enough. My data doesn't exceed
the excel limit, but I have about 50 worksheets of similar data with a matrix
of about 45 by 550, so its more of a time issue. Heres how I would solve my
problem if I had lots of time . .

I would plot each column, fit a 3rd degree polynominal and display the
resulting equation, copy the trendline equation from the plot, paste into a
cell, modify the equation (replace cell names for "x", erase spaces, etc) and
then use this result.

I am interested in if there is a way to skip the steps where I make a plot
for each column, fit a polynominal, and then copy that equation. For now I
have used the TREND function and just fit two separate linear lines (for each
column), but a 3rd degree polynominal would suit me better.

Thanks,
Suzy

"David" wrote:

Hi Suzy,
I was working on a similar problem, but it did not have to do with a volume
problem, as much as it had to do with a 6th degree polynomial. It appeared
that the precision of 15 decimals places would not allow finding the "data"
points well enough. So I would like to see how this goes for you.

Assuming your data has not exceeded the 65000 row limit inherant in Excel,
there sould not be a reason a formula can not be copied either up or down,
depending your data layout. I believe the formula will have to be
"anchoraged" at either the top or bottom of your data range. I am not certain
that the formula yields specific "new" data points, which allow you to graph
the trend line, with out using the built in trend line in Excel, which is
what I was attempting to accomplish.

I had attemted to go to several math web sites to solve the 15 decimal place
precision problem, but failed to solve the problem. That had more to do with
the 6th degree. But I assume you are trying to accomplish something similar,
but for a 3rd degree poly.

How much data are you actually dealing with? Does it exceed 65000?
--
David


"Suzy" wrote:

I need to process a large amount of data. I would like to automatically
insert the equations for third order polynominal trendlines based on my data
into a cell so I can further use the data. I know I can fit the line, copy
the equation, paste into a cell, and then modify so it is a working equation,
but for the amount of data I have this is absurd.

I am slightly familiar with macros, but I have never written my own.

There must be an easier way

Thanks!