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 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.

"Incoherent" wrote:

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

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



"Incoherent" wrote:

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.

"Incoherent" wrote:

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

Columns B and C are unnecessary, use
LINEST(D1:D5,A1:C5^{1,2,3})

You have resolved your actual question in this case, but there is an
issue with LINEST in 2003, such that coefficients that are exactly zero
are not to be trusted, although nonzero coefficients (other than assumed
intercept) are correct regardless.

http://groups.google.com/group/micro...e29a8e9b4382fa

Jerry

Incoherent wrote:

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

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

"Jerry W. Lewis" wrote:

Columns B and C are unnecessary, use
LINEST(D1:D5,A1:C5^{1,2,3})

You have resolved your actual question in this case, but there is an
issue with LINEST in 2003, such that coefficients that are exactly zero
are not to be trusted, although nonzero coefficients (other than assumed
intercept) are correct regardless.

http://groups.google.com/group/micro...e29a8e9b4382fa

Jerry