using linest to generate 3rd order polynomial coefficients

I

Incoherent

In Excel 2000 (and earlier), this example.
A B C D
1 1 1^2 1^3 2.3
2 2 2^2 2^3 4.2
3 3 3^2 3^3 4.7
4 4 4^2 4^3 5.1
5 . . . and so on

=INDEX(LINEST(D1:D5,A1:C5),1) gives the first coefficient for a 3rd order
polynomial fit to the data in column D.
=INDEX(LINEST(D1:D5,A1:C5),2) gives the 2nd,
=INDEX(LINEST(D1:D5,A1:C5),3) the 3rd
=INDEX(LINEST(D1:D5,A1:C5),4) gives the 4th. In the form ax^3+bx^2+cx+d, I
can find the fit for the data. The above formulas give me a,b,c and d.
(This can also be used as a single array formula, dropping the "INDEX" part)
Now in Excel 2003, the above formulas will only return a 2nd order fit. the
results are ax^2+bx+c. The first coefficient is 0.
Any thoughts on how generate the third (or forth, or fifth, or sixth) order
fit. This is driving me crazy because it is one of the things I use
constantly. I will be forced to go back to 2000 if I can't resolve this. (And
the idiotic "fx" in place of "=" in the formula bar)

Cheers

Incoherent
 
I

Incoherent

I should mention that these coefficients I am on about are identical to the
ones generated by the polynomial trend line you can put onto a line or
scatter chart.
 
I

Incoherent

OK I resolved it. It was not Excel 2003 after all. Only my own stupidity. A
missing "$" :)
 
I

Incoherent

Thanks for that suggestion Jerry, thats very useful.
I have been reading about the LINEST issues, fortunately it rarely applies
to how I am using it

Cheers

Incoherent
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top