Polynomial least squares fit
 

Hi,
I have a graph of two values and I need to know, what the
formula is that connects all to points. The trend function
in the graph gives

y = 0.0557x4 - 51.355x3 + 18559x2 - 3E+06x + 2E+08

I need the last two terms too more places.

Is there a spread sheet function for this, a macro or
anything.

MarkS

Polynomial least squares fit
 

On Mon, 18 Aug 2003 00:32:03 -0700, "Mark Scholes"
wrote:

Hi,
I have a graph of two values and I need to know, what the
formula is that connects all to points. The trend function
in the graph gives

y = 0.0557x4 - 51.355x3 + 18559x2 - 3E+06x + 2E+08

I need the last two terms too more places.

Is there a spread sheet function for this, a macro or
anything.

MarkS

Select the equation on the chart. Then right-click and select Format Data
Labels. The Number tab allows formatting the numbers in the equation to more
decimal places (although Excel's precision limits makes formatting to more than
15 places meaningless).

You can also use the LINEST worksheet function if the above is not
satisfactory.

The LINEST function does not always return the right answer, although it works
pretty well most of the time. The algorithms used in the chart trendline
generator is more robust. For my limited uses, LINEST has been satisfactory.

If you search the archives using GOOGLE you will find techniques for not only
using LINEST, but for also extracting the equation from the chart.


--ron

Polynomial least squares fit
 

David Braden's has VBA code to extract the coefficients directly from
the chart into cells is given at

http://groups.google.com/groups?selm....microsoft.com

LINEST does work well with non-challenging data, the problem is that it
gives no warning when it fails completely.

Jery

Ron Rosenfeld wrote:

On Mon, 18 Aug 2003 00:32:03 -0700, "Mark Scholes"
wrote:


Hi,
I have a graph of two values and I need to know, what the
formula is that connects all to points. The trend function
in the graph gives

y = 0.0557x4 - 51.355x3 + 18559x2 - 3E+06x + 2E+08

I need the last two terms too more places.

Is there a spread sheet function for this, a macro or
anything.

MarkS


Select the equation on the chart. Then right-click and select Format Data
Labels. The Number tab allows formatting the numbers in the equation to more
decimal places (although Excel's precision limits makes formatting to more than
15 places meaningless).

You can also use the LINEST worksheet function if the above is not
satisfactory.

The LINEST function does not always return the right answer, although it works
pretty well most of the time. The algorithms used in the chart trendline
generator is more robust. For my limited uses, LINEST has been satisfactory.

If you search the archives using GOOGLE you will find techniques for not only
using LINEST, but for also extracting the equation from the chart.


--ron