Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
how can i obtain polynomial constants when using trendlines.
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. |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
how can i obtain polynomial constants when using trendlines.
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trend Line constants as Excel Cell Values | Excel Discussion (Misc queries) | |||
Polynomial regression - how does Excel do it? (under "Trendline") | Charts and Charting in Excel | |||
VBA code to extract m-coefficient in linear trendlines from ALL charts | Excel Discussion (Misc queries) | |||
More than 2 constants in Index | Excel Worksheet Functions | |||
Summing trendlines in excel charts? | Excel Discussion (Misc queries) |