Polynomial Trendline parameter Extraction 1

[RGCook]

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.

 

[ivanlocke]

I had tried to find how to extract the trendline equation to no avail. I believe we even called microsoft and they said it was all internal with nothing accessible. We ended up having to write a function that calculated the trendline, or you are left with displaying the equation and copying into cells manually.

 

[pmover]

all,

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

 

[RGCook]

Thank you ivanlok for the reply. I suspected as much. As for pmover, I respectfully submit that my question was with regards to polynomial, not linear regression. The linest function is applicable to linear regression only.

Thank you both for taking the time to respond.

 

[pmover]

RGCook,

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

 

[hazelwoodr]

Please be warned that teh polynomial tendline as displayed on the graph looks lovely (appears to fit the data).
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.

 

[EGT01]

hazelwoodr,

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

 

[RGCook]

Thanks for the warning and additional information. I have decided to write my own function that performs polynomial regression using least squares method. A benefit of this is that it does not have the limitation in terms of only going to the fifth order (as Excel limits).

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.

 

[enirwin]

Hazelwoodr,

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.

 

[Wickus]

Dear RGCook

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

 

[elvis932]

It's been a few years, but I used to curve fit 3d surfaces with this method. The method can be applied to any type of equation. I will start with a general description of the method, and if anyone is intrested they can e-mail for a detailed explanation.
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

 

[barryodonnell]

Hi,
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).

http://www.netrax.net/~jdavita/XlXtrFun/XlXtrFun.htm

Regards