Polynomial Trendline parameter Extraction
Polynomial Trendline parameter Extraction
(OP)
Does anyone know of a way to extract the paramters from a polynomial regression (trendline) in Excel. I have quickly looked through the object model and don't see it.
RE: Polynomial Trendline parameter Extraction
RE: Polynomial Trendline parameter Extraction
please look at thread: Thread770-44555
determining the coefficients of a polynomial equation is determined by using the "LINEST" function.
i trust u have plotted data and incorporated the trendline capabilities, but how to obtain the coefficients is the question.
a technique is by the linest function. although i've not readily notice this technique widely publisized, here it is. this formula must be entered as an array formula, otherwise it will not work.
the standard linest function is as follows:
=linest(known y's, known x's, const, stats)
to determine a polynomial, array enter:
=linest(known y's, known x's^{1,2,3}, const, stats)
where this equation will determine a 3rd degree polynomial.
by array enter, i mean to select 4 rows x 4 columns, enter the formula, and then "ctrl"+"shift"+"enter".
there are web sites that provide better explanations to use array formulas.
if need be, i will gladly forward an example file to you demonstrating the two techniques.
-pmover
RE: Polynomial Trendline parameter Extraction
Thank you both for taking the time to respond.
RE: Polynomial Trendline parameter Extraction
i understood the question that you needed to determine the coefficients of a polynomial equation (i.e. A0 + A1x + A2x + ...= 0, where the coefficients are A0, A1, A2, An...), based on some x and y data.
i trust that the data was plotted and you've implemented a trendline, which is displayed on the chart.
the technique described does provide the same coefficients that a trendline provides, which is displayed on the chart.
sorry for any confusion!
good luck!
-pmover
RE: Polynomial Trendline parameter Extraction
Under chart options, the equation can be displayed.
However, the equation can be quite badly wrong, and we have not found the reason behind it. We find that using the displayed coefficients to calculate a polynomial on the same x axis data gives a completely different result
when plotted on the same graph!!! No doubt it sometimes works but not i the value series we have been using.
Suggest you try your own matching technique Others on the web have commented on this error also.
RE: Polynomial Trendline parameter Extraction
You may want to check the discussion in the following thread in regards to improving the accuracy of the displayed equation for a trendline. Increasing the number of significant digits that are displayed makes all the difference. Makes you wonder why Excel displays anything less.
Thread770-44555
RE: Polynomial Trendline parameter Extraction
That is not meant to imply that Excel is limited. Indeed, I have found that Excel uses a rather advanced routine that improves upon the result using least square. I guess that doesn't surprise me too much, they have had a while to work on it. What does surprise me, however, is that the parameters are not available...even via the object model using Visual Basic. I suppose it has something to do with the complexity of making all of this available to developers who would probably use a more sophisticated method if they in fact wanted this information in the first place. After all, they'd have to allow for an additional five parameters for up to fifth order regression. Or, perhaps developers over at MS are working on more important matter.
In any event, I am in the process of using linear algebra to solve the resulting matrices stemming from this endeavor. It is not something I have looked at in a while, and something I was hoping to avoid. It is an interesting trip back in time for me. I can almost see myself in class as I write this.
Again, thank you for your help.
RE: Polynomial Trendline parameter Extraction
I ran into the same problem. I think the problem you're having may be due to the formatting of the equation. The equation displayed for the trendline often has too few decimal places for the coefficients. Reformatting the equation to show more decimal places should improve the accuracy of your calculated values.
RGCook,
Set the trendline options to display the equation for the trendline. Start with the = sign and select the entire equation. Copy and paste into the cell you want to calculate and replace the x's in the equation with the appropriate cell reference. Be sure to check your calculated values against your data points to make sure you have enough decimal places in the coefficients. One last point. Be careful with higher order polynomial curve fits if you have to extrapolate from your data. They can head off in odd directions outside your data set.
RE: Polynomial Trendline parameter Extraction
The method explained by PMOVER works exactly as he describes. Don't be too single-minded about the LINEST function. It normally does LINEAR regression but it does POLYNOMINAL regression when invoked as an array function. It's all explained by PMOVER.
As an example, if you need a third degree polynominal (such as y = ax³+bx²+cx+d), then select four cells in a row and enter the formula =linest(known-y's, known-x's^{1,2,3},1,) and hit control shift enter. This puts the coefficients a,b,c&d in the four selected cells. In the above formula, the "known-y's" should be replaced by the cells containing the known y values, same for known-x's.
Another example : If you need a second degree polynomial (such as y = ax²+bx+c), then select three cells in a row and enter the formula =linest(known-y's, known-x's^{1,2},1,) and hit control shift enter. This puts the coefficients a,b&c in the three selected cells.
This method can be modified for the degree of the polynomial by selecting the correct amount of cells and by inserting the correct sequence in the exponent of the linest function. More help available in the Excel Help file on the last two parameters of the function.
Regards.
Wickus
RE: Polynomial Trendline parameter Extraction
Using a linear curve fit (y=Ax+B). Make variables "A" and "B" set them to a value of 1. Then using your y values and A anb B make an estimate of x. Then make a column of x minus estimated x, squared "(x-xest)^2". Make a sum of the new column, and either use the built-in solver, or circular references to adjust A and B so that the Sum of "(x-xest)^2" is minimizied. With a little adjustment this method can be used to create curve fits to any type of equation you can think up.
Regards
Melvin Hakes
RE: Polynomial Trendline parameter Extraction
It is true that the default equation displayed under the trendline options can be badly wrong, however this is usually due to the nuber format display which can be adjusted. If the aim is just to plug in the equation so as to interpolate between values consider the excellent interp and spline functions provided by the xlxtrfun addon (see link below, a must for any engineer using EXCEL).
www.netrax.net/~jdavita/XlXtrFun/XlXtrFun.htm
Regards