trend for polynomial curve fitting by regressing

[Guest]

Hi,
I use the trend function which returns values along a linear trend.

I read in the help aht we can use trend for polynomial curve fitting by
regressing against the same variable raised to different powers. it s given
in the example that, if column A contains y-values and columne b cotains
x-values, and we can enter x^2 in column C, x^3 in column D..and so on and
then regress columns B through D against Column A.

I am just wondering how does this do.. say for this example:

X Y
1 10
2 22
3 30
4 25
5 69
6 50
7 90
8 88
9 97
10 99

How can i regress this data using polynomial curve fitting using trend
formula in excel

Thanks

 

[Robert_Steel]

The function is LINEST
the ms help is a bit difficult to follow but have a look at this link
http://www.stfx.ca/people/bliengme/ExcelTips/Polynomial.htm

post back if you need more

cheers RES

 

[Guest]

the easiest way I know is to plot the values on an XY chart then add a
trendline - right click on the series, click Add Tendline then select
Polynomial and go to the Options and click Display equation on chart.

Or, you could use the Solver to calculate the coefficients of each power of
X that would minimize the sum of the squares between calculated and actual
values of Y for each known X, but that is a little harder. However, see

http://archives.math.utk.edu/ICTCM/EP-13/C13/html/paper.html

 

[Guest]

I have a spreadsheet that uses Solver to calculate the constant and
coefficients for each power of X, using your data (currently up to the fifth
power, but expandable. Unfortunately, Savefile server is full so I can;t
send it that way. But if you would like me to send it, please let me know.

 

[DOR]

Well, that is certainly very useful to know. Beats the heck out of the
graph and solver methods! (although the results are not quite the same
- very slight difference from the Graph and solver whch both prodiuce
virtuall the same result). To say the the MS help for LINEST is a bit
difficult to follow is quite an understatement ...

Thank you very much for this information.

Declan O'R

 

[Robert_Steel]

Declan
prior to Excel 2003 the Linest algorithm was not as good as the one used
by trend line on the graph.
So if you are, like me, using an older option you may wish to use the
graph route.

There has been code posted to extract the coefficients but I have not used
it.
http://groups.google.com/group/microsoft.public.excel.misc/msg/4d440d271303e0d6

A google search for
excel coefficient trendline linest extract
or direct link
http://groups.google.co.uk/groups?hl=en&q=excel+coefficient+trendline+linest+extract&qt_s=Search

will take you into a world of discussion on the matter if needed.

hth RES

 

[DOR]

Robert

Thank you again. I rarely have a need to find the coefficients, but I
had found the MS article on the errors in LINEST prior to 2003, which
explained the differences I saw, and of course, I am using an older
version. Nevertheless, thanks for the links. They may come in useful
sometime.

The solver approach is surprisingly easy and does provide the
coefficients directly in cells, so I don't really know why I would use
all that code.

Regards

Declan O'R

 

[DOR]

OOOPS!

I take back my comments about Solver's ability. It seems to work ok
for some problems but not for others; it gives up when it believes the
values do not converge, when in fact they should. Maybe it is not so
good after all, or maybe I'm missing something in the set-up. Oh well,
back to the drawing board and to the Solver vendor's site. Maybe the
more powerful Solver versions are required for some problems.

Declan O'R