How to calculate polynomial regression coefficients

[Joham Shason via OfficeKB.com]

Hi,

I wanted to know a way to calculate the polynomial regression coefficients in
excel as chart does. I have seen many help sites but it has not helped one of
it was JWALK.com which was good but did not work for me. I am using 4th
degree polynomial regression. The y and x values are as below.

X values 0.00 0.03 0.07 0.10 0.13 0.17 0.20 0.23 0.26 0.30 0.33
Y values 0.000 0.000 0.000 0.002 0.005 0.012 0.023 0.041 0.069 0.107 0.159

The coefficients from chart obtained are
c4 9.8202
c3 1.4485
c2 -0.1041
c1 0.0057
b 0.0000

I hope somebody can help

--
Joham


Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-new/200508/1

 

[Dr. Stephan Kassanke]

Hi,

I wanted to know a way to calculate the polynomial regression coefficients
in
excel as chart does. I have seen many help sites but it has not helped one
of
it was JWALK.com which was good but did not work for me. I am using 4th
degree polynomial regression. The y and x values are as below.

X values 0.00 0.03 0.07 0.10 0.13 0.17 0.20 0.23 0.26 0.30 0.33
Y values 0.000 0.000 0.000 0.002 0.005 0.012 0.023 0.041 0.069 0.107 0.159

The coefficients from chart obtained are
c4 9.8202
c3 1.4485
c2 -0.1041
c1 0.0057
b 0.0000

I hope somebody can help

--
Joham


Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-new/200508/1

Hi Joham,

Risi Thomas might have what you are looking for. I have no personal
experience with te Addin Trend2K but it looks promising.

http://rtsoftwaredevelopment.de/Freeware_Trend2k.htm

provides functions and VBA equivalents for computing polynomial trends (and
thus a formal mathematical function for your sample).

It's documented in german but you might give it a try.

just in case Excel does not offer a set of suitable tools, have a look at
SPSS or similar tools.

cheers,
Stephan

 

[Jerry W. Lewis]

http://www.stfx.ca/people/bliengme/ExcelTips/Polynomial.htm

In versions prior to 2003, you are pushing the limits of LINEST
accuracy, which will only agree with 8-11 figures of the chart trendline
coefficients (which uses a much better algorithm). A wider range of
x-values would make this less numerically difficult.

Your posted values are rounded too severely to reproduce the
coefficients you claim from the chart trendline.

Jerry

 

[Joham S via OfficeKB.com]

Hi,

Thanks for all your replies.

Dr. Stephan thx for your effort to get me the site. Can’t understand German
and know nothing about VBA. I wasn’t able to try it due to lack of knowledge
in VBA and German language.

Jerry thx to you too for taking efforts to reply. I have gone through this
site earlier and I wasn’t able to get anything out of it. If you could help
me by creating this example in an excel file and sent me the way LINEST can
be used. It would be really helpful. My email address is joham_shason@yahoo.
com. I have gone through the following sites and the site which you said is
in the second link which I have tried to no avail.
http://www.officekb.com/Uwe/Forum.aspx/excel-chart/3957/logarithmictrendline-equation
http://www.excelforum.com/archive/index.php/t-189077.html
http://www.j-walk.com/ss/excel/tips/tip101.htm

I do not have much expertise on analysis functions. If some could create and
example as shown in the site which jerry proposed and send it as an
attachment to the above address stated, I will be obliged.

Thanks. Thank you once again for all your efforts.

Joham
(e-mail address removed)

 

[Jerry W. Lewis]

Most likely your data is in rows instead of columns, and you overlooked
Bernard's insert showing how you must change the formula for that case.
See also Excel's See Help for "About array formulas and array
constants" subtopic "Using array constants" for details.

Also note that you must array enter (Ctrl+Shift+Enter) the formula. If
you do it correctly, the formula bar will show curly brackets ({} that
you did not type) around the formula.

There may also be an issue with different separator characters due to
regional settings.

Examples for both rows and columns will be privately e-mailed per your
request.

Jerry

 

[Guest]

Reread

http://www.stfx.ca/people/bliengme/ExcelTips/Polynomial.htm

You must select an output range large enough to hold all of the output
values, type in the formula and press Ctrl-Shift-Enter to simultaneously
enter the formula into the entire output range. If you only put the formula
into one cell at a time, each single cell will only contain the first cell of
the array output (i.e. the coefficient of the highest power of x).

Jerry

 

[Joham Shason via OfficeKB.com]

Thank you, Jerry. I understood that. Thanks for you patience in making me
understand the function properly. It was a pleasure learning from you. Jerry
the data which the graph takes and the fuction takes are the same but the
coefficients are different and you have said that "Your values are too
rounded off", I di'nt get that. I am using 2003, i dont think there should be
any problems with the accuracy. Once again thx for all your help.

Thank you
Joham

--
Joham


Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-new/200508/1

 

[Guest]

Thank you, Jerry. I understood that. Thanks for you patience in making me
understand the function properly. It was a pleasure learning from you.

You're welcome, glad it helped.

... Jerry
the data which the graph takes and the fuction takes are the same but the
coefficients are different and you have said that "Your values are too
rounded off", I di'nt get that. I am using 2003, i dont think there should be
any problems with the accuracy. Once again thx for all your help.

With that comment, I was trying to say that your posted X and Y values are
not given to the same precision as the values that produced your posted
polynomial coefficients. Note that Formatting a cell impacts only its
display, not the actual value that will be used in calculations. If you
apply LINEST in Excel 2003 to the same data that produced your posted
coefficients, I would expect it to reproduce the posted coefficients.

Jerry