View Single Post
  #1   Report Post  
byundt
 
Posts: n/a
Default LINEST bug with cubic polynomials in Excel 2003

LINEST in Excel 2003 SP1 finds incorrect values for regression coefficients
of cubic polynomials when the values of x are determined by a formula. Excel
97, 2000 and 2002 perform the calculation correctly.

To reproduce this problem:
1) Put the following labels and polynomial coefficients in cells A1:B4
a3 19
a2 23
a1 2
a0 81

2) Put the values of x in cells A9:C29
A9=-1.0
A10=A9+0.1 (copy this formula down)
B9=A9^2 (copy this formula down)
C9=A9^3 (copy this formula down)

3) Put the values of y in cells D9:D29
D9=B$4+B$3*A9+B$2*A9^2+B$1*A9^3 (copy this formula down)

4) Select cells F9:I13 and array enter the following formula:
=LINEST(D9:D29,A9:C29,TRUE,TRUE)

Note that LINEST in Excel 2003 returns incorrect values for a0 and a2. This
error is consistent even if I change polynomial coefficients in B1:B4. Excel
97, 2000 and 2002 all return the correct values, however.

If the formulas in A9:A29 are replaced by their values, then LINEST works
correctly. Also, if one of the rows of data is deleted (or repeated) then
LINEST works correctly.

Bottom line: I have an acceptable workaround for the time being--but
Microsoft has a bug that needs to be fixed.