LINEST bug with cubic polynomials in Excel 2003

[byundt]

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 D929
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(D929,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.

 

[Jerry W. Lewis]

Old news. There are previously reported anomalies, suggesting that
LINEST/LOGEST coefficient estimates that are exactly zero in 2003 should
be looked on with some suspicion.
http://groups.google.com/groups?threadm=412980D4.5040305@no_e-mail.com
Apparently this is an example of MS being too helpful, as in the
difference between =A29-1 and =(A29-1) with your data.

As previously noted, you can identify the issue by an estimated
coefficient that is exactly zero. In the referenced thread I suggested
some ways to slightly perturb the model in order to avoid this
helpfulness gone wrong (also why you get different results from slightly
different formulations of the problem). In your case, you could also
simply reduce to
=LINEST(D928,A9:C28,,TRUE)

"If the formulas in A9:A29 are replaced by their values" is not entirely
clear. Do you mean Edit|Copy followed by Edit|Paste Special|Values, or
hand entering the values? They will not be the same, your increment
(0.1) has no exact binary representation, and hence must be
approximated. As a result, A19 is -1.38777878078145E-16 instead of
zero, and A29 is slightly less than 1 as shown by (A29-1). You can
reduce the accumulation of approximations by using
=(ROW()-19)/10
in A9:A29

Also, you are working harder than you need to by directly calculating
B9:C29 when you could use
=LINEST(D928,A9:A28^{1,2,3},,TRUE)

Jerry

As for your specific example, your instructions for reproducing are
incomplete.
1) specifies 8 values to fill 16 cells in an unspecified way.

 

[Jerry W. Lewis]

ld news. There are previously reported anomalies, suggesting that
LINEST/LOGEST coefficient estimates that are exactly zero in 2003 should
be looked on with some suspicion.
http://groups.google.com/groups?threadm=412980D4.5040305@no_e-mail.com
Apparently this is an example of MS being too helpful, as in the
difference between =A29-1 and =(A29-1) with your data.

As previously noted, you can identify the issue by an estimated
coefficient that is exactly zero. In the referenced thread I suggested
some ways to slightly perturb the model in order to avoid this
helpfulness gone wrong (also why you get different results from slightly
different formulations of the problem). In your case, you could also
simply reduce to
=LINEST(D928,A9:C28,,TRUE)

"If the formulas in A9:A29 are replaced by their values" is not entirely
clear. Do you mean Edit|Copy followed by Edit|Paste Special|Values, or
hand entering the values? They will not be the same, your increment
(0.1) has no exact binary representation, and hence must be
approximated. As a result, A19 is -1.38777878078145E-16 instead of
zero, and A29 is slightly less than 1 as shown by (A29-1). You can
reduce the accumulation of approximations by using
=(ROW()-19)/10
in A9:A29

Also, you are working harder than you need to by directly calculating
B9:C29 when you could use
=LINEST(D928,A9:A28^{1,2,3},,TRUE)

Jerry

 

[Jerry W. Lewis]

Old news. There are previously reported anomalies, suggesting that
LINEST/LOGEST coefficient estimates that are exactly zero in 2003 should
be looked on with some suspicion.
http://groups.google.com/groups?threadm=412980D4.5040305@no_e-mail.com
Apparently this is an example of MS being too helpful, as in the
difference between =A29-1 and =(A29-1) with your data.

As previously noted, you can identify the issue by an estimated
coefficient that is exactly zero. In the referenced thread I suggested
some ways to slightly perturb the model in order to avoid this
helpfulness gone wrong (also why you get different results from slightly
different formulations of the problem). In your case, you could also
simply reduce to
=LINEST(D928,A9:C28,,TRUE)

"If the formulas in A9:A29 are replaced by their values" is not entirely
clear. Do you mean Edit|Copy followed by Edit|Paste Special|Values, or
hand entering the values? They will not be the same, your increment
(0.1) has no exact binary representation, and hence must be
approximated. As a result, A19 is -1.38777878078145E-16 instead of
zero, and A29 is slightly less than 1 as shown by (A29-1). You can
reduce the accumulation of approximations by using
=(ROW()-19)/10
in A9:A29

Also, you are working harder than you need to by directly calculating
B9:C29 when you could use
=LINEST(D928,A9:A28^{1,2,3},,TRUE)

Jerry