how can i obtain polynomial constants when using trendlines.

how can i obtain polynomial constants when using trendlines. i sometimes
need to used these constants in other calculations but must manually enter
them in another context.

Hi,

Let's suppose that the x- and y- ranges are in A2:A51 and B2:B51
respectively, and you are fitting a 6-order polynomial to your data. Select
a 7 column x 1 row area, enter the following formula, and confirm
with CTRL-SHIFT-ENTER.

=LINEST(B2:B51,A2:A51^{0,1,2,3,4,5,6},0,)

The formula will return the 7 coefficients, starting from the zeroth order
and ending with the 6th order.

If you want to have the coefficients shown starting from the 6th order and
ending in the zeroth order, modify the formula as,

=LINEST(B2:B51,A2:A51^{6,5,4,3,2,1,0},0,)

In general, for a polynomial fit of order n (where n=2 to 6), select a (n+1)
column x 1 row area, and use one of the following formulas:

=LINEST(B2:B51,A2:A51^{0,1,2,....,n},0,)
=LINEST(B2:B51,A2:A51^{n,n-1,....,1,0},0,)

Regards,
B. R. Ramachandran


"senrats" wrote:

how can i obtain polynomial constants when using trendlines. i sometimes
need to used these constants in other calculations but must manually enter
them in another context.

It would be wise to compare the values returned by LINEST to the chart
trendline coefficients (assuming that the chart is an "X-Y (Scatter)" chart
and not a "Line" chart).

Prior to Excel 2003, LINEST used a mathematically correct but numerically
poor algorithm that could result in serious cancellation issues with higher
order polynomials. Excel 2003 fixed that problem, but introduced a new one;
so that in 2003, LINEST coefficients that are exactly zero are not to be
trusted without independent confirmation.

Alternately, Tushar Mehta has enhanced VBA code by David Braden to extract
coefficients from the chart trendline.
http://groups.google.com/group/micro...da30f29434786d

Jerry

"B. R.Ramachandran" wrote:

Hi,

Let's suppose that the x- and y- ranges are in A2:A51 and B2:B51
respectively, and you are fitting a 6-order polynomial to your data. Select
a 7 column x 1 row area, enter the following formula, and confirm
with CTRL-SHIFT-ENTER.

=LINEST(B2:B51,A2:A51^{0,1,2,3,4,5,6},0,)

The formula will return the 7 coefficients, starting from the zeroth order
and ending with the 6th order.

If you want to have the coefficients shown starting from the 6th order and
ending in the zeroth order, modify the formula as,

=LINEST(B2:B51,A2:A51^{6,5,4,3,2,1,0},0,)

In general, for a polynomial fit of order n (where n=2 to 6), select a (n+1)
column x 1 row area, and use one of the following formulas:

=LINEST(B2:B51,A2:A51^{0,1,2,....,n},0,)
=LINEST(B2:B51,A2:A51^{n,n-1,....,1,0},0,)

Regards,
B. R. Ramachandran


"senrats" wrote:

how can i obtain polynomial constants when using trendlines. i sometimes
need to used these constants in other calculations but must manually enter
them in another context.